JSONB_EXTRACT_PATH_TEXT
Returns a value from JSON by a path of keys, as text.
PostgreSQL 17.9
JSONB_EXTRACT_PATH_TEXT(from_json, path[, ...])- [ ]optional part
- ...can be repeated
- from_json
- Value of the jsonb typejsonb
- path
- Keys or array element numbers from 0, in nesting ordertext
Return value
A string (text); NULL if there is no such path
Examples
City from a nested object
PostgreSQL 17.9
SELECT JSONB_EXTRACT_PATH_TEXT(
'{"user": {"name": "Ann", "city": "Paris"}}',
'user',
'city'
) AS city;Promo code from event data
PostgreSQL 17.9
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.