Menu

gitpiper

PostgreSQL JSON Cheat Sheet in May 2024

Last Updated: 13 May 2024

README.md

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'

References


338+ more cheat sheets for you in May 2024

Subscribe to our Newsletter

Subscribe to get resources directly to your inbox. You won't receive any spam! ✌️

© 2024 GitPiper. All rights reserved

Rackpiper Technology Inc

Company

About UsBlogContact

Subscribe to our Newsletter

Subscribe to get resources directly to your inbox. You won't receive any spam! ✌️