JSONB and Arrays

SELECT body -> 'user' ->> 'id' AS user_id
FROM events;
-- user_id as text

SELECT id FROM docs
WHERE body @> '{"type":"invoice"}'::jsonb;
-- docs with type invoice

SELECT jsonb_set('{"a":1}'::jsonb, '{a}', '2', false);
-- {"a": 2}

SELECT ARRAY[1,2,3] && ARRAY[3,4];
-- t

SELECT unnest(ARRAY['a','b']) AS x;
--
-- x
-- a
-- b

SELECT * FROM jsonb_each('{"a":1,"b":2}'::jsonb);
-- key | value

SELECT * FROM jsonb_array_elements('[1,2,3]'::jsonb) AS t(val);
-- val: 1,2,3 as jsonb

SELECT jsonb_build_object('id', 1, 'ok', true);
-- {"id": 1, "ok": true}

SELECT body @? '$.items[*] ? (@.qty > 0)'
FROM carts;
-- t if any positive qty

SELECT array_append(ARRAY[1,2], 3);
-- {1,2,3}

SELECT id FROM posts WHERE 'sql' = ANY (tags);
-- posts tagged sql

SELECT jsonb_strip_nulls('{"a":1,"b":null}'::jsonb);
-- {"a": 1}

SELECT user_id, jsonb_agg(jsonb_build_object('id', id)) AS orders
FROM orders GROUP BY user_id;

UPDATE docs
SET body = body || '{"status":"done"}'::jsonb
WHERE id = 1;
-- merges keys

SELECT cardinality(ARRAY[1,2,3]);
-- 3