PostgreSQL 18 reference · JSON Functions

PostgreSQL JSONB_PATH_QUERY() Function

Return every JSON item matched by an SQL/JSON path expression, such as '$.items[*] ? (@.price > 10)'.

PostgreSQL 18 Since 12

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

jsonb_path_query(target, path, vars)

A set of jsonb rows.

Filter with a path

SELECT jsonb_path_query('{"items":[{"n":"tea","p":3},{"n":"cake","p":12}]}', '$.items[*] ? (@.p > 5).n') AS expensive;

Output

 expensive
-----------
 "cake"
(1 row)

Run this on your computer — see “Get Started” for setup.

Test a path with @?

SELECT '{"items":[{"p":3},{"p":12}]}'::jsonb @? '$.items[*] ? (@.p > 10)' AS any_over_10;

Output

 any_over_10
-------------
 t
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 12	Added

JSON_TABLE() · Turn JSON into a relational table in the FROM clause, with typed columns taken from SQL/JSON paths. Added in PostgreSQL 17.
@> and ? · Test JSON containment with @> (left contains right) and key existence with ?, both of which a GIN index can speed up.
SQL String Functions · tutorial