top of page
Writer's pictureAmandeep Kaur

Working with JSON Data in PostgreSQL


PostgreSQL is a powerful open-source relational database management system (RDBMS). JSON stands for JavaScript Object Notation. With PostgreSQL's JSON capabilities, we can store, query, and manipulate JSON data effectively.


JSON data is written in key-value pairs surrounded by quotes. JSON supports 2 widely used data structures objects and arrays.


  1. An Object is an unordered collection of key-value pair enclosed in curly brackets '{' and '}'. String and value is separated by a ':' and if there are more than one string value pairs, they are separated by comma ','.


SYNTAX: {string: value,......}

EXAMPLE: Below is an example from Film table .The film object has four keys title, description, length and rating with associated value is separated by a ':' and a comma ','.

	 {
 	 "title": "Academy Dinosaur",
	  "description": " A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian 	  Rockies",
	  "length": 86,
  	 "rating":"PG"

}


2. An Array is an ordered list of values, starts and ends with '[' and ']'. Between them, a number of values can reside. If there are more than one values , they are separated by ','.


SYNTAX:[value,.............]

EXAMPLE: [100, 200, 300, 400] ,[filmid, description, duration, rating]


Both objects and arrays can be nested. Arrays can also be nested within an object. Let us take an example of entering a phone number of a person to understand this in detail. Here a person has 2 phone numbers (personal and work) so we nested both phone numbers as an Array  starting with ' [' and ending with ']' .


--Create a new table Actor Detail
 CREATE TABLE Actor_Detail(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phonenumber JSONB
);
--Add data to the table 
INSERT INTO Actor_Detail(name, phonenumber)
VALUES('Tom Cruz','[
	   {"personal":"123456789"},
	   {"work":"0848654278"}]'
	  ),	   
	   ('Santa Cruz','[
	   {"personal":"987416789"},
	   {"work":"8415685428"}]'
	   )



Additionally, an array may contain values of any valid JSON data type . There four basic built-in data types in JSON. They are strings, numbers, boolean (i.e true and false) and null.


SYNTAX:

String || Number || Object || Array || TRUE || FALSE || NULL

PostgreSQL offers two data types for storing JSON. Both types aim to accommodate JSON data, differing mainly in their storage and performance optimization.


  • JSON – is a data type in PostgreSQL that allows to store an exact copy of the JSON input text .This makes it simpler to use but can result in slower query performance.


  • JSONB – is a data type in PostgreSQL that allows us to store the JSON data in binary format and performance wise it is generally faster.


JSON Operators and Functions


Functions and operators allow you to store, manipulate, and query data in JSON format in PostgreSQL.

Here are some of the commonly used PostgreSQL Functions and operators :


  • ->: This operator allows you to extract a specific value from a JSON object with the quote around it.

For example:

--Create a new table Actor Detail
 CREATE TABLE Actor_PersonalDetail(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phonenumber JSONB,
	Tshirt JSONB
);
--Add data to the table 
INSERT INTO Actor_PersonalDetail(name, phonenumber,Tshirt)
VALUES('Tom Cruz','[
	   {"personal":"123456789"},
	   {"work":"0848654278"},
	   {"phone":"Android"}]',
	   '{"color": "white", "size": ["S","M","L","XL"]}'
	  ),	   
	   ('Santa Cruz','[
	   {"personal":"987416789"},
	   {"work":"8415685428"},
		{"phone":"Apple"}]',
		'{"color": "black", "size": ["S","M","L","XL"]}'
	   )
--To retrieve a specific value from a JSON object use the -> operator, in a SELECT statement as seen below:
SELECT name, phonenumber, Tshirt -> 'color' color 
FROM Actor_PersonalDetail;

JSON value extracted using "->" in above example have quotes around it , if you want to extract values without quotes use ->> operator.


  • "->>" : This operator allows you to extract a JSON object field as text without the quotes around it from a JSON object.

For example:


SELECT name, phonenumber,
  Tshirt ->> 'color' color 
FROM 
  Actor_PersonalDetail;

Aggregating JSON data


We can use JSON aggregate functions that collect data from multiple rows and put them into a JSON array or object.


The jsonb_agg() function can be used to create a JSON array from data of multiple rows. The syntax of the jsonb_agg() function is :

jsonb_agg(expression)

Let's take an example from actor's table to understand it in better way. In the below query, jsonb_agg function puts together Actor's first name and last name and creates a JSON array

SELECT 
  jsonb_agg(
    jsonb_build_object('first_name', first_name, 'last_name', last_name)
  ) AS actorName 
FROM 
  actor;


The jsonb_object_agg() function can be used to collect key/value pairs into a JSON object and aggregate data from multiple rows into a single JSON object.


Here’s the syntax of the jsonb_object_agg() function:

jsonb_object_agg(key, value)

Let's take an example to understand this aggregate function in the better way. In the below query ,first we used array_agg function in the select statement that accepts a set of values from different columns (title, description, length and rating) and returns an array in which each value in the set is assigned to an element of the array. The ARRAY_AGG() is often used with the GROUP BY clause to decide the order of the elements in the result array.


In another select statement we used json_object_agg funtion that will return a JSON object that consists of key(film_id)/value (filmvalues) pairs.


With CTE_jsonValues AS
(select film_id,array_agg(title||','||description||','||length||','||rating) as filmvalues
FROM film
 group by film_id,title,description,length
)
select json_object_agg(film_id,filmvalues) as jsonformat from CTE_jsonValues

Conclusion

We have learnt how to use JSON functions and operators in PostgreSQL and gone through the basics of storing JSON data in PostgreSQL, how to query JSON data in PostgreSQL to make it readily accessible.

33 views

Recent Posts

See All
bottom of page