Introduction to the PostgreSQL jsonb_path_query_first() function

Thejsonb_path_query_first() function allows you to query data from aJSONB document based on aJSON path expression and return the first match.

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

jsonb_path_query_first(jsonb_data, json_path)

In this syntax:

• First, specify a JSONB data from which you want to query data.
• Second, provide a JSON path to match elements in the JSONB data.

If thejsonb_path_query_first() function doesn’t find any match, it returnsNULL.

PostgreSQL jsonb_path_query_first() function examples

Let’s explore some examples of using thejsonb_path_query_first() function.

1) Basic jsonb_path_query_first() function example

The following example uses thejsonb_path_query_first() function to get the first pet of a person:

SELECT jsonb_path_query_first(    '{"name": "Alice", "pets": ["Lucy","Bella"]}',    '$.pets[*]')AS first_pet_name;

Output:

first_pet_name---------------- "Lucy"(1 row)

2) Using the jsonb_path_query_first() function with table data

First,create a new table calledperson:

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

In theperson table, thedata column has the type of JSONB that stores employee information including name, age, and pets.

Second,insert data into theperson table:

INSERT INTO person (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*;

Third, retrieve the first pet name using thejsonb_path_query_first() function:

SELECT jsonb_path_query_first(data,'$.pets[*].name')AS first_pet_nameFROM person;

Output:

first_pet_name---------------- "Fluffy" "Max" "Snowball"(3 rows)

3) Handling missing paths

The following example attempts to find an element whose path does not exist:

SELECT jsonb_path_query_first(data,'$.email')FROM person;

Output:

jsonb_path_query_first------------------------ null null null(3 rows)

In this case, the person object doesn’t have an email key, therefore the result isNULL.

Summary

• Use thejsonb_path_query_first() function to extract the first JSON value that matches a JSON path expression from a JSON document.

Last updated onFebruary 24, 2024

On this page

• Introduction to the PostgreSQL jsonb_path_query_first() function
• PostgreSQL jsonb_path_query_first() function examples
• Summary