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.