Back to Cheatsheets
PostgreSQL JSON
Databases2018-12-06
Operators
Accessors
SELECT * FROM users WHERE data->>'name' = 'John';
SELECT data->>'name' AS name FROM users;
{: .-setup}
| Operator | Description | Example | Returns |
|---|---|---|---|
-> int | Get array element 2 | data->2 | JSON |
-> text | Get object key name | data->'name' | JSON |
#> text[] | Get keypath a,b (eg, data.a.b) | data#>'{a,b}' | JSON |
| - | |||
->> int | Get array element 2 | data->>2 | Text |
->> text | Get object key name | data->>'name' | Text |
#>> text[] | Get keypath a,b (eg, data.a.b) | data#>>'{a,b}' | Text |
| {: .-headers.-shortcuts} |
> returns JSON, >> returns text.
Boolean operators
SELECT * FROM users WHERE data->tags ? 'admin';
SELECT data->tags ? 'admin' AS is_admin FROM users;
{: .-setup}
| Operator | Description | Example |
|---|---|---|
? str | Does data have key name? | data ? 'name' |
| `? | ` text[] | Does data have a or b? |
?& text[] | Does data have a and b? | data ?& array['a','b'] |
@> jsonb | Does left include right? | data @> '{"b":2}'::jsonb |
<@ jsonb | Does right include left? | data <@ '{"a":1,"b":2}'::jsonb |
| {: .-headers.-shortcuts.-left-align} |
When ?/?|/?& works on objects, it checks keys; when it works on arrays, it checks for elements.
Updating
Arrays and objects
UPDATE users SET tags = tags || array['admin'];
{: .-setup}
| Operator | Example | Description |
|---|---|---|
| ` | ` json | |
- str | data - 'a' | Delete a key |
- int | data - 1 | Delete an array item |
#- text[] | data #- '{us,name}' | Delete a path |
| {: .-headers.-shortcuts} |
Only available in PostgreSQL 9.5+.
jsonb_set
UPDATE users SET data = jsonb_set(data, '{name}', '"John"');
Only available in PostgreSQL 9.5+.
Functions
fn(json) → json
jsonb_set(data, '{path}', value)
jsonb_strip_nulls(data)
fn(···) → json
to_json("Hello"::text)
array_to_json('{1,2}'::int[])
Iteration
SELECT * from json_each('{"a":1, "b":2}')
SELECT * from json_each_text('{"a":1, "b":2}')
-- key | value
This is an incomplete list, there's way too many!
See: JSON functions
More examples
'{"a":1}'::jsonb ? 'a''["a"]'::jsonb ? 'a'