PostgreSQL jsonb_path_query

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_path_query_first() function to extract the first JSON value that matches a JSON path expression from a JSON document.

Introduction to the PostgreSQL jsonb_path_query_first() function

The jsonb_path_query_first() function allows you to query data from a JSONB document based on a JSON path expression and return the first match.

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

jsonb_path_query_first(jsonb_data, json_path)Code language: SQL (Structured Query Language) (sql)

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 the jsonb_path_query_first() function doesn’t find any match, it returns NULL.

PostgreSQL jsonb_path_query_first() function examples

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

1) Basic jsonb_path_query_first() function example

The following example uses the jsonb_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;Code language: SQL (Structured Query Language) (sql)

Output:

 first_pet_name
----------------
 "Lucy"
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using the jsonb_path_query_first() function with table data

First, create a new table called person:

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    data JSONB
);Code language: SQL (Structured Query Language) (sql)

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

Second, insert data into the person 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 *;Code language: SQL (Structured Query Language) (sql)

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

SELECT jsonb_path_query_first(data, '$.pets[*].name') AS first_pet_name
FROM person;Code language: SQL (Structured Query Language) (sql)

Output:

 first_pet_name
----------------
 "Fluffy"
 "Max"
 "Snowball"
(3 rows)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

 jsonb_path_query_first
------------------------
 null
 null
 null
(3 rows)Code language: SQL (Structured Query Language) (sql)

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

Summary

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