Introduction to PostgreSQL jsonb_path_query_array() function

Thejsonb_path_query_array() function allows you to queryJSONB data using aJSON path expression.

Here’s the basic syntax of thejsonb_path_query_array() function:

jsonb_path_query_array(jsonb_data, json_path)

In this syntax:

• First, specify thejsonb_data that you want to query.
• Second, provide ajson_path that you want to match elements within thejsonb_data.

Thejsonb_path_query_array() function returns the matched elements as a JSON array.

If the function does not find any matched element, it returns an empty array.

If either argument isNULL, the function returnsNULL.

PostgreSQL jsonb_path_query_array() function example

Let’s explore some examples of using thejsonb_path_query_array() function

1) Basic PostgreSQL jsonb_path_query_array() function example

The following example uses thejsonb_path_query_array() function to get the employee names as an array:

SELECT  jsonb_path_query_array(    '{"employees": [{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]}',    '$.employees[*].name'  );

Output:

jsonb_path_query_array------------------------ ["Alice", "Bob"](1 row)

In this example, the JSON path expression$.employees[*].name locates the value of thename key of all elements in theemployees array.

2) Using jsonb_path_query_array() function with table data

First,create a new table calledemployees:

CREATE TABLE employees (    idSERIAL PRIMARY KEY,    data JSONB);

In theemployees table, thedata column has the type ofJSONB.

Second,insert some rows into theemployees table:

INSERT INTO employees (data)VALUES    ('{"name": "Alice", "age": 30, "pets": [{"type": "cat", "name": "Fluffy"}, {"type": "dog", "name": "Buddy"}]}'),    ('{"name": "Bob", "age": 35, "pets": [{"type": "dog", "name": "Max"}]}'),    ('{"name": "Charlie", "age": 40, "pets": [{"type": "rabbit", "name": "Snowball"}]}')RETURNING*;

Output:

id |                                                    data----+-------------------------------------------------------------------------------------------------------------  1 | {"age": 30, "name": "Alice", "pets": [{"name": "Fluffy", "type": "cat"}, {"name": "Buddy", "type": "dog"}]}  2 | {"age": 35, "name": "Bob", "pets": [{"name": "Max", "type": "dog"}]}  3 | {"age": 40, "name": "Charlie", "pets": [{"name": "Snowball", "type": "rabbit"}]}(3 rows)

Third, use thejsonb_path_query_array() function to retrieve the pet names of employees as a JSON array:

SELECT jsonb_path_query_array(data,'$.pets[*].name')AS employee_pet_namesFROM employees;

Output:

employee_pet_names--------------------- ["Fluffy", "Buddy"] ["Max"] ["Snowball"](3 rows)

3) Handling missing paths

If the specified path doesn’t exist in theJSONB data, thejsonb_path_query_array() function returns an empty array. For example:

SELECT jsonb_path_query_array(data,'$.address')FROM employees;

Output:

jsonb_path_query_array------------------------ [] [] [](3 rows)

In this example, the employee object doesn’t have anaddress key, so the result is an empty array.

Summary

• Use thejsonb_path_query_array() function to query JSONB data using a JSON path and return matched elements as a JSON array.

Last updated onFebruary 24, 2024

On this page

• Introduction to PostgreSQL jsonb_path_query_array() function
• PostgreSQL jsonb_path_query_array() function example
• Summary