5

PostgreSQL JSON cheatsheet

 2 years ago
source link: https://devhints.io/postgresql-json
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK