Introduction to the PostgreSQL jsonb_path_query() function

Thejsonb_path_query() function allows you to queryJSONB data based on a JSON path expression.

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

jsonb_path_query(jsonb_data, path_expression)

In this syntax:

• jsonb_data is the JSONB data that you want to query.
• path_expression is a JSON path expression that locates values or elements in the JSONB data.

Thejsonb_path_query() function returns JSONB data that matches the specified JSON path expression.

If thepath_expression does not locate any element in thejsonb_data, the function returnsNULL.

PostgreSQL jsonb_path_query() function example

Let’s take some examples of using thejsonb_path_query() function.

Setting up a sample table

First,create a table namedproductswith a JSONB column namesattributes to store product attributes:

CREATE TABLE products (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    attributes JSONB);

Second,insert some rows into theproducts table:

INSERT INTO products (name, attributes)VALUES    ('Laptop','{"brand": "Dell", "price": 1200, "specs": {"cpu": "Intel i7", "ram": "16GB"}}'),    ('Smartphone','{"brand": "Samsung", "price": 800, "specs": {"os": "Android", "storage": "128GB"}}');

1) A basic jsonb_path_query() function example

The following example uses thejsonb_path_query() function to retrieve the brand and price of all products:

SELECT jsonb_path_query(attributes,'$.brand')AS brand,       jsonb_path_query(attributes,'$.price')AS priceFROM products;

Output:

brand   | price-----------+------- "Dell"    | 1200 "Samsung" | 800(2 rows)

2) More complex JSON path example

The following example uses thejsonb_path_query() function to query nested attributes such as retrieving the CPU specification of laptops:

SELECT jsonb_path_query(attributes,'$.specs.cpu')AS cpuFROM products;

Output:

cpu------------ "Intel i7"(1 row)

Summary

• Use thejsonb_path_query() function to query JSONB data based on JSON path expressions.

Last updated onFebruary 23, 2024

On this page

• Introduction to the PostgreSQL jsonb_path_query() function
• PostgreSQL jsonb_path_query() function example
• Summary