How to Query JSONB 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 using the capabilities that include applications of 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. 

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

This is one of the Postgres jsonb query examples. 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 Postgres jsonb array, 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 an 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 onto the array of 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)

SQL/JSON Path Expression Usage in PostgreSQL 12

PostgreSQL 12 and later versions introduced native support for SQL/JSON Path expressions, providing a powerful way to query and manipulate JSON data within the database. Here’s a breakdown of key functions:

Key Functions:

• jsonb_path_exists(jsonb, text): Checks if a given JSON Path expression matches any part of the JSON document.
• jsonb_path_query(jsonb, text): Extracts the first matching JSON element based on the provided path expression.
• jsonb_path_query_array(jsonb, text): Extracts an array of all matching JSON elements.
• jsonb_path_ops: Provides various operators for constructing and manipulating JSON Path expressions.

Benefits of Using SQL/JSON Path Expressions:

• Standard Compliance: Adheres to the SQL:2016 standard for JSON support.
• Efficient JSON Data Handling: Leverage PostgreSQL’s optimized JSON support for fast and efficient queries.
• Powerful Querying Capabilities: Express complex queries using the expressive JSON Path syntax.
• Improved Readability: JSON Path expressions can make your queries more concise and easier to understand.

Read about using ODBC driver in PostgreSQL and easily connect to any specified back-end database.

Usage of jsonb_array_elements() for Nested Key Extraction

jsonb_array_elements() is a powerful function in PostgreSQL that allows you to extract individual elements from a JSON array. When dealing with nested JSON structures, you can chain multiple jsonb_array_elements() calls to access deeply nested keys.

Example:

Consider a table named users with a jsonb column called profile containing the following data:

{
  "name": "John Doe",
  "addresses": [
    {
      "street": "123 Main St",
      "city": "Anytown",
      "state": "CA"
    },
    {
      "street": "456 Elm St",
      "city": "Othertown",
      "state": "NY"
    }
  ]
}

To extract the city from the first address, you can use the following query:

SELECT
    (jsonb_array_elements(profile -> 'addresses')) ->> 'city' AS city
FROM
users;

Conclusion

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. If you’re an avid user of PostgreSQL, copying data into a warehouse using ETL for data analysis might be an extensive task.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready. 

FAQ on Query JSONB Array