Operators

Accessors

SELECT * FROM users WHERE data->>'name' = 'John';
SELECT data->>'name' AS name FROM users;
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

> returns JSON, >> returns text.

Boolean operators

SELECT * FROM users WHERE data->tags ? 'admin';
SELECT data->tags ? 'admin' AS is_admin FROM users;
Operator Description Example
? str Does data have key name? data ? 'name'
?| text[] Does data have a or b? data ?| array['a','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

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'];
Operator Example Description
|| json data || array['a','b'] Concatenate
- str data - 'a' Delete a key
- int data - 1 Delete an array item
#- text[] data #- '{us,name}' Delete a path

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'

References

0 Comments for this cheatsheet. Write yours!