Introduction to the PostgreSQL jsonb_path_exists() function

Thejsonb_path_exists() function allows you to check if a JSON path matches any element in a JSON document.

Here’s the syntax of thejsonb_path_exists() function:

jsonb_path_exists(jsonb_data, json_path)

In this syntax:

• jsonb_data is a JSON document where you want to check for a JSON path.
• json_path is the path that you want to check.

Thejsonb_path_exists() function returns true if thejson_path returns any elements in thejsonb_data document or false otherwise.

PostgreSQL jsonb_path_exists() function example

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

Setting up a sample table

First,create a new table calledproducts that store product information:

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

Theproducts table has theattributes column whose data type isJSONB.

Second,insert 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"}}')RETURNING*;

Output:

id |    name    |                                     attributes----+------------+------------------------------------------------------------------------------------  1 | Laptop     | {"brand": "Dell", "price": 1200, "specs": {"cpu": "Intel i7", "ram": "16GB"}}  2 | Smartphone | {"brand": "Samsung", "price": 800, "specs": {"os": "Android", "storage": "128GB"}}(2 rows)

1) Basic jsonb_path_exists() function example

The following example uses thejsonb_path_exists() function to check whether the CPU specification exists for any product:

SELECT name,       jsonb_path_exists(attributes,'$.specs.cpu')AS cpu_existsFROM products;

Output:

name    | cpu_exists------------+------------ Laptop     | t Smartphone | f(2 rows)

2) Using the jsonb_path_exists() function in the WHERE clause

The following example uses thejsonb_path_exists() function with thejsonb_path_query() function to retrieve theCPU specification of any products that haveCPU spec:

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

Output:

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

Summary

• Use thejsonb_path_exists() function to check the existence of JSON Path expressions within JSONB data

Last updated onFebruary 23, 2024

On this page

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