PostgreSQL allows the user to store their structured data in the databases using rows and columns in the table. The user can store data in different formats/types like string, integer, character, JSON, JSONB, etc. JSON format stores data in the form of text and JSONB refers to the binary format of data storage to provide better efficiency.

This guide will explain querying the JSONB array of objects in PostgreSQL.

How to Query JSONB Array of Objects in PostgreSQL?

To query the data from the JSONB-type array of objects in PostgreSQL, use the following query:

SELECT * FROM emp;

The above query displays the data from the emp table containing JSONB type of column named data:

Use the following query to get the data using the WHERE clause that filters data with respect to some specific condition:

SELECT data FROM emp WHERE emp_id =1;

The above query gets the JSONB type data from the emp table where the emp_id column has value 1:

Use the following command that utilizes the JSONB operator (->>) to get the data associated with the name key from the JSONB column and retrieve it as text:

SELECT data ->> 'name' AS Name FROM emp;

The above command fetches names from the data field and displays them in the name field:

Use the following query to use another JSONB operator (->) which returns the JSONB object from the emp table:

SELECT data -> 'name' AS Name FROM emp;

The above query gets the names from the JSONB data field and displays them as individual objects:

Use the following query to get the subject_marks field from the “students” table. The “subject_marks” column contains data for the student in the JSONB data type:

Select subject_marks from students;

Running the above code displays that the “subject_marks” column contains the subject id, subject name, and their respective marks:

Use the following query to expand the subjects_marks field into an array format for a student having id 2:

--Array functions to Query JSONB
SELECT arr.position,arr.item_object
 FROM students,
 jsonb_array_elements(subject_marks) with ordinality arr(item_object, position) 
 WHERE id=2;

The query selects the position and object of an Array from the students' table. After that, a function named jsonb_array_element is used to extract elements from subject_marks. The stated function takes two arguments: a JSONB array and the element index to be extracted:

That’s all about querying the JSONB array of objects in PostgreSQL.

Conclusion

To query the JSONB array of objects in PostgreSQL, simply create a table with a JSONB column and insert data into the table. Use different operators to query the JSONB data and retrieve the desired information from the table. Use SQL queries to get data based on the position and objects within the array. This guide has explained the process of querying the JSONB array of objects in PostgreSQL.