PostgreSQL 18 reference · JSON Functions
PostgreSQL #> and #>> JSON Path Operators
Read a nested JSON value by a path array: #> returns jsonb and #>> returns text, as in data #>> '{user,name}'.
PostgreSQL 18 Since 9.3
Syntax
jsonb #> path | jsonb #>> path
Parameters
| Parameter | Description |
|---|---|
jsonb |
A jsonb (or json) value. |
path |
A text array of keys and indexes, such as '{items,0,name}'. |
Return value
jsonb or text.
Examples
SELECT '{"user":{"name":"Ada","langs":["en","fr"]}}'::jsonb #>> '{user,name}' AS name,
'{"user":{"name":"Ada","langs":["en","fr"]}}'::jsonb #> '{user,langs}' AS langs;
Output
name | langs ------+-------------- Ada | ["en", "fr"] (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT ('{"a":{"b":[10,20]}}'::jsonb)['a']['b'][1] AS subscript;
Output
subscript ----------- 20 (1 row)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 9.3 | Added |
| PostgreSQL 14 | jsonb values can also be read and updated with subscripts: data['a']['b']. |
Related
-> and ->>· Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.JSONB_SET()· Return jsonb with the value at a path replaced or, by default, created. Combine with || to merge objects.- SQL String Functions · tutorial