Querying a JSON array of objects in PostgreSQL

Do you have a NoSQL database that has no rigid shape and is causing data analysis complexity nightmares? With JSON in PostgreSQL, you can have a solution to your complex problem.

PostgreSQL is a high-performing, open-sourced object-relational database with two JSON data storage types, JSON and JSONB. 

JSONB (JavaScript Object Notation Binary) offers seamless and fast data exchange over a network in a small and simple-to-use data format.

JSONB, created as a JavaScript extension, has quickly displaced XML in many sectors.

Additionally, developers are now enhancing the functionality of their product by integrating PostgreSQL’s robust query processing environment with the JSONB data from their application.

This blog post will teach you how to Query JSONB Array of Objects in PostgreSQL and deep dive into performing basic operations on JSONB Array of Objects in PostgreSQL. 

Table of Contents

• Prerequisites
• What is JSONB Array of Objects in PostgreSQL?
• How to Query JSONB Array of Objects in PostgreSQL
  • Creating Database
  • Insertion Query 
  • SELECT Query 
  • SELECT With WHERE Clause Query 
  • Expand a JSONB Array to a Set of JSON Values
  • UPDATE  Operation on JSONB Array of Objects
  • DELETE Operation on JSONB Array of Objects
• Conclusion

Prerequisites

• Basic knowledge of writing PostgreSQL Queries
• pgAdmin software

What is JSONB Array of Objects in PostgreSQL?

JSON is an abbreviation for JavaScript Object Notation. It is commonly used to store data in the form of key-value pairs. In contrast to other formats, JSON is the text humans can read.

Since version 9.2, PostgreSQL has supported native JSON data types. It provides a plethora of methods and operators for working with JSON data.

PostgreSQL supports two forms of JSON data storage:

• JSON
• JSONB

• The JSON and JSONB data formats accept almost similar value sets as input. The primary design distinction is one of efficiency.
• The JSON data type stores an exact copy of the input text, which processing functions must reparse on each execution, whereas JSONB data is stored in a decomposed binary format, which is slightly slower to input due to additional conversion overhead, but significantly faster to process because no reparsing is required.
• Indexing is also supported by JSONB, which might be a substantial benefit.

How to Query JSONB Array of Objects in PostgreSQL

The main application of JSONB is the transmission of data in a text format that humans understand between a web server and a web application.
You must first comprehend the essential operation on the JSONB array of Objects in PostgreSQL that are listed below to operate with JSONB and PostgreSQL:

• Creating Database
• Insertion Query 
• SELECT Query 
• SELECT With WHERE Clause Query 
• Expand a JSONB Array to a Set of JSON Values
• UPDATE  Operation on JSONB Array of Objects
• DELETE Operation on JSONB Array of Objects

Creating Database

The general syntax for creating a table in PostgreSQL with a JSONB column is as follows:

CREATE TABLE Table_Name(
    Column_name Datatype PRIMARY KEY,
    Column_name JSONB
);

Before jumping to the Query JSONB array of objects in PostgreSQL, we need a database. So Let’s create a database first. Follow the below steps to create a database.

• Open pgAdmin and build the database you want.
• Choose the Query Tool by performing a right-click on the database name.
• Use the code snippet below to construct a straightforward table with an ID, the name of the students, and a JSONB column to hold an array of subject marks details.

CREATE TABLE public.students(
   id serial PRIMARY KEY,
   name varchar(50),
   subject_marks jsonb
);

This will build a table with the 3 columns listed below:

• The id Column: It acts as the primary key and identifies the order placed.
• The name Column: It stores the names of students.
• The subject_marks Column: It stores your data as JSONB documents

Insertion Query

The syntax for Inserting values in the database is as follows:

INSERT INTO TABLE_NAME(col1, col2, col3) VALUES(val1, val2, val3);

It is now time to insert data into the JSONB column after creating a PostgreSQL Table. To add a new record to your Students table, use the INSERT statement shown below:

INSERT INTO students(name ,subject_marks ) VALUES ('Dandelions',
'[{
 "sub_id": 1,
 "sub_name": "Computer Architecture",
 "sub_marks": 130
},
{
 "sub_id": 2,
 "sub_name": "Operating Systems",
 "sub_marks": 120

}]');

The above code represents that the student name Dandelions has 2 subjects. Now, you can use the code below to insert other students’ details into your table simultaneously.
Just copy and paste below code snippet in your editor.

INSERT INTO students(name ,subject_marks) VALUES('Cardi B',
'[{
 "sub_id": 1,
 "sub_name": "Computer Architecture",
 "sub_marks": 140

}, {
 "sub_id": 3,
 "sub_name": "Computer Networking",
 "sub_marks": 150
}]');


INSERT INTO students (name ,subject_marks) VALUES ('Ted Mosbby',
'[{
 "sub_id": 5,
 "sub_name": "Database Management",
 "sub_marks": 160
},
{
  "sub_id": 3,
 "sub_name": "Computer Networking",
 "sub_marks": 190
}]');
INSERT INTO students (name ,subject_marks) VALUES ('Shawn',
'[{
 "sub_id": 6,
 "sub_name": "Discrete Mathematics",
 "sub_marks": 180
},
{
  "sub_id": 7,
 "sub_name": "Human Values and Ethics",
 "sub_marks": 140
}]');

SELECT Query

The general syntax for the SELECT command is as follows:

Select * from table_name;

Finally, it’s time to query the data that you just successfully inserted into PostgreSQL. To show your data like other native data types, you can just use the SELECT statement:

 Select * from students;

Output:

SELECT with WHERE Clause Query

It might be the case that you want to display just a particular JSONB column. The general syntax for showing a JSONB column is the same as the native data type. 

SELECT with Where clause is used in this case. The general syntax is as follows:

Select jsonb_column_name from table_name where condition;

Example:

select subject_marks from students where id =3;

Output:

Expand a JSONB Array to a Set of JSON Values

There might be cases when you want to analyze JSONB documents in Array format.
PostgreSQL Operator and Functions provide jsonb_array_elements() function to expand JSONB document into array format. 

The general syntax we are going to use is as follows:

jsonb_array_elements(subject_marks) with ordinality arr(subject_marks, position)

• The jsonb column containing an array of objects was supplied to jsonb_array_elements. As a result, the array is split into various JSON objects.
• With ordinality, PostgreSQL will now be instructed to store the ordinal position. Note that this begins at 1. However, when working with an array, it begins at 0.
• arr is an arbitrary name.
• The term “subject_marks,” which in our case refers to a subject marks jsonb object, is arbitrary.
• The term “position” refers to a keyword.

Example: In this example, we will expand the JSONB document to an array format for the student having id =2.

SELECT arr.position,arr.item_object
FROM students,
jsonb_array_elements(subject_marks) with ordinality arr(item_object, position) 
WHERE id=2;

Output:

Get the elements of the Expanded JSONB Document 

You can also access the array index level elements after expanding the JSONB document. Follow the following command to get the element at index 1 of the expanded JSONB document having id = 2.
Note: Ordinal value starts with the number 1, a jsonb array of objects starts at 0. Make sure for arr.position you provide an ordinal number of array elements.

Example:

SELECT arr.item_object
FROM students,jsonb_array_elements(subject_marks) with ordinality arr(item_object, position) 
WHERE id=2 and arr.position=2;

Output:

UPDATE  Operation on JSONB Array of Objects

To add a subject to the subject_marks array for the student having id=1, run the following command:

UPDATE students SET subject_marks = subject_marks || 
'{"sub_name": "Major Project",
        "sub_marks": 190,
        "sub_id" : 8}' ::jsonb
WHERE id=1;

The important things to notice are:

•  concatenation operator( || ) is used to concatenate on to the array a JSONB objects.
• The array is wrapped like ‘{ }’:: JSON[].

You can check the updated value by executing the following command:

select subject_marks from students where id =1;

Output:

 subject_marks                                                                                              
---------------------------------------------------------
 [{"sub_id": 1, "sub_name": "Computer Architecture", "sub_marks": 130}, {"sub_id": 2, "sub_name": "Operating Systems", "sub_marks": 120}, {"sub_id": 8, "sub_name": "Major Project", "sub_marks": 190}]
(1 row)

DELETE Operation on JSONB Array of Objects

To Delete the array element with a specified index, you can simply use the ‘-’ operator.

Example: Student having id =1 dropped “Major Project” subject. Let’s remove item number 3 from her subject_marks.
The first query for student subjects is to see if she has three and then remove item 3.

select subject_marks from students where id =1;

Output:

 subject_marks                                                                                              
---------------------------------------------------------
 [{"sub_id": 1, "sub_name": "Computer Architecture", "sub_marks": 130}, {"sub_id": 2, "sub_name": "Operating Systems", "sub_marks": 120}, {"sub_id": 8, "sub_name": "Major Project", "sub_marks": 190}]
(1 row)

Subject number 3, Major Project, is at array position 2. Therefore, 2 will be used with the “-” operator

UPDATE students SET subject_marks = subject_marks - 2
WHERE id=1;

Run the following command to see the resultant record.

select subject_marks from students where id =1;

Output:

subject_marks                                                               
-----------------------------
[{"sub_id": 1, "sub_name": "Computer Architecture", "sub_marks": 130}, {"sub_id": 2, "sub_name": "Operating Systems", "sub_marks": 120}]
(1 row)

Conclusion

You can have the best JSONB datatype by storing & querying JSON/JSONB data in your PostgreSQL tables. Postgres JSONB Query offers you the adaptability and effectiveness of a NoSQL database combined with all the advantages of a relational database. 

The article demonstrated the various operations you may use while working on Postgres JSONB Query. You can apply the above-discussed Queries JSONB data in your own PostgreSQL database by following our examples as a guide.

Are you ready for another enriching deep dive? Check out these exciting articles at Hevo:

• Postgres JSON Extraction of Data: Made Easy 101 
• Work Easily With JSON Using PostgreSQL Parse JSON 

If you’re an avid user of PostgreSQL, copying data into a warehouse using ETL for Data Analysis might be an extensive task.

Hevo, a No-Code Data Pipeline, is at your service for your rescue. You can save your engineering efforts by setting up a Data Pipeline and start replicating your data from PostgreSQL to your desired warehouse in a matter of minutes using Hevo. 

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to give Hevo a try?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience understanding the PostgreSQL JSONB Query in the comment section below! We would love to hear your thoughts.