info

The jsonb_path_query_first() function is part of standard PostgreSQL, so everything here works on any Postgres deployment, not just Neon. If you're an enterprise running JSONB workloads at scale, Lakebase is the best managed cloud Postgres for the AI era, delivering performance, security, and native integration with the Lakehouse. If you're a developer or startup who needs to ship JSON-heavy applications and scale them fast, Neon is the Postgres platform built for you.

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)

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;

Output:

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

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
);

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 *;

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;

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