jsonb_path_query_first.md

title	PostgreSQL jsonb_path_query_first() Function
page_title	PostgreSQL jsonb_path_query_first() Function
page_description	Use the PostgreSQL jsonb_path_query_first() function to extract the first JSON value that matches a JSON path from a JSON document.
prev_url	https://www.postgresqltutorial.com/postgresql-json-functions/postgresql-jsonb_path_query_first/
ogImage
updatedOn	2024-02-24T02:56:19+00:00
enableTableOfContents	true
previousLink	title slug PostgreSQL jsonb_path_query_array() Function postgresql-json-functions/postgresql-jsonb_path_query_array
nextLink	title slug PostgreSQL jsonb_path_exists() Function postgresql-json-functions/postgresql-jsonb_path_exists

The `jsonb_path_query_first()` function is part of standard PostgreSQL, so everything here works on any Postgres deployment, not just [Neon](https://neon.com). If you're an enterprise running JSONB workloads at scale, [Lakebase](https://www.databricks.com/product/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.