PostgreSQL JSON Operations

INSERT INTO users (data) VALUES ('{"name": "John", "age": 25}'::jsonb); # insert JSON

SELECT data->'name' FROM users; # returns JSON
SELECT data->'address'->'city' FROM users; # nested access

SELECT data->>'name' FROM users; # returns text
SELECT data->'address'->>'city' FROM users; # nested text

SELECT * FROM users WHERE data->>'age' > '18'; # filter by JSON field
SELECT * FROM users WHERE data @> '{"city": "NYC"}'; # contains

UPDATE users SET data = jsonb_set(data, '\{age\}', '26'); # update field
UPDATE users SET data = data - 'age'; # remove field
UPDATE users SET data = data || '{"city": "NYC"}'; # add/merge field

jsonb_array_length(data->'tags') # array length
jsonb_each(data) # expand to key-value pairs
jsonb_object_keys(data) # get all keys

@> # contains
<@ # is contained by
? # key exists
?| # any key exists
?& # all keys exist