SQLearnAcademy
←Function reference
JSONPostgreSQL only

JSONB_PATH_QUERY

Gets values out of JSON with an SQL/JSON path expression — including with conditions.

Syntax

JSONB_PATH_QUERY(<json>, '<path>')
JSONB_PATH_QUERY_FIRST(<json>, '<path>')
JSONB_PATH_EXISTS(<json>, '<path>')

A path starts with $ — the whole document. Then .<key> moves to a field, [*] to all the elements of an array, and ? (@.price > 100) filters by a condition. Every value found becomes a separate row of the result. JSONB_PATH_QUERY_FIRST returns only the first one, and JSONB_PATH_EXISTS says whether anything was found.

A field by path
SELECT JSONB_PATH_QUERY('{"customer": {"name": "Emma", "city": "London"}}', '$.customer.city') AS city;
city
London
All elements of an array
SELECT JSONB_PATH_QUERY('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*].name') AS item;
item
Mouse
Monitor
With a conditionAdvanced
SELECT JSONB_PATH_QUERY('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*] ? (@.price > 100).name') AS expensive_item,
JSONB_PATH_EXISTS('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*] ? (@.price > 1000)') AS has_over_1000;
expensive_itemhas_over_1000
Monitorfalse

@ is the current element: only the products with a price above 100 remain.

Watch out

The result is jsonb, so comparing with text breaks: JSONB_PATH_QUERY(…) = 'London' throws invalid input syntax for type json. Convert it to text: JSONB_PATH_QUERY(…) #>> '{}'.

A path with no match gives zero rows, not NULL, so the table row disappears from the result. JSONB_PATH_QUERY_FIRST returns a single value or NULL.

A key with a space goes in double quotes: '$."order id"'. And with the word strict at the start of the path, a missing key becomes an error: JSON object does not contain key.