JSONB_EXTRACT_PATH_TEXT
Returns a value from JSON by a path of keys, as text.
PostgreSQL 17.5
JSONB_EXTRACT_PATH_TEXT(from_json, path[, ...])Parameters
- from_jsonjsonb
- Value of the jsonb type
- pathtext
- Keys or array element numbers in nesting order
Return value
A string (text); NULL if there is no such path
Examples
City from a nested object
PostgreSQL 17.5
SELECT JSONB_EXTRACT_PATH_TEXT(
'{"user": {"name": "Ann", "city": "Paris"}}',
'user',
'city'
) AS city;Promo code from event data
PostgreSQL 17.5
SELECT event_id,
JSONB_EXTRACT_PATH_TEXT(metadata, 'promo_code') AS promo_code
FROM events
LIMIT 5;Details
It does the same as a chain of operators: JSONB_EXTRACT_PATH_TEXT(data, 'user', 'city') equals data -> 'user' ->> 'city'. For the json type there is JSON_EXTRACT_PATH_TEXT, and to get the value as JSON, use JSONB_EXTRACT_PATH.