PostgreSQL Tutorial: Query nested arrays in JSON columns

Summary: In this article, we will learn how to query nested arrays in PostgreSQL JSON columns.

Table of Contents

In a PostgreSQL database, when querying nested arrays in JSON columns, it is common to need to convert a JSON array into rows in a table. However, this can be a tricky task, as the process of converting the data can be complex and error-prone. In this article, we will explore several different methods for converting a JSON array into rows in a PostgreSQL table.

Using json_array_elements() function

To turn a JSON array into rows in PostgreSQL, you can use the json_array_elements() function. This function takes a JSON array as input and returns a set of rows, one for each element in the array. Here’s an example of how to use it:

SELECT * FROM json_array_elements('[1, 2, 3]') AS elem;

This will return a table with one column (elem) and three rows (1, 2, and 3).

You can also use json_array_elements() with a column that contains JSON data. For example, if you have a table mytable with a column myjson that contains a JSON array, you can use the following query to turn the array into rows:

SELECT * FROM mytable, json_array_elements(myjson) AS elem;

This will return a table with all the columns from mytable, plus a new column elem that contains the elements from the JSON array.

You can also use the json_array_elements() function with nested JSON arrays:

SELECT element FROM json_array_elements('[1, [2, 3], 4]') AS element;

This will return five rows, where the nested array is expanded into separate rows:

 element
---------
 1
 [2, 3]
 4
 2
 3

Using the json_array_elements() function in PostgreSQL, you can easily turn JSON arrays into rows, and can even handle nested arrays.

Using jsonb_array_elements() function

To turn a JSON array into rows in PostgreSQL, you can use the jsonb_array_elements() function. This function takes a JSON array as input and returns a set of rows, where each row contains one element from the array.

Here is an example of how to use the jsonb_array_elements() function:

SELECT jsonb_array_elements('[1, 2, 3]') AS element;

This will return three rows, each containing one element from the JSON array:

 element
---------
 1
 2
 3

You can also use the jsonb_array_elements() function with a column that contains a JSON array. Let’s create a table first.

CREATE TABLE 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.

Let’s insert some data into the JSONB column after creating a table.

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
}]');

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
}]');

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;

With ordinality, PostgreSQL will now be instructed to store the ordinal position. Note that this begins at 1. This will return two rows, each containing one element from the JSON arrays in the subject_marks column:

 position |                             item_object
----------+----------------------------------------------------------------------
        1 | {"sub_id": 1, "sub_name": "Computer Architecture", "sub_marks": 140}
        2 | {"sub_id": 3, "sub_name": "Computer Networking", "sub_marks": 150}
(2 rows)

In summary, the jsonb_array_elements() function is a powerful tool for working with JSON arrays in PostgreSQL. It allows you to easily turn a JSON array into rows.

Using json_to_recordset() function

To turn a JSON array into rows in PostgreSQL, you can use the json_to_recordset() function. This function creates a set of rows from a JSON array, where each row corresponds to an object in the array.

Here’s an example of how to use json_to_recordset():

SELECT * FROM
  json_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]')
    AS t(id int, name text);

This query creates a table with two columns (id and name) and two rows, corresponding to the two objects in the JSON array.

Here’s a breakdown of the query:

• json_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]') creates a set of rows from the JSON array. The argument is the JSON array.
• AS t(id int, name text) specifies the column names and types for the resulting table. In this case, we’re creating two columns: id (type int) and name (type text).

You can also use jsonb_to_recordset() if your JSON array is of type jsonb.

Here’s an example of how to use jsonb_to_recordset():

SELECT * FROM
  jsonb_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]')
    AS t(id int, name text);

This query is similar to the previous one, but uses jsonb_to_recordset() instead of json_to_recordset().

In summary, to turn a JSON array into rows in PostgreSQL, you can use the json_to_recordset() or jsonb_to_recordset() function. These functions create a set of rows from a JSON array, where each row corresponds to an object in the array. You can specify the target table and column names/types for the resulting table.