Working with JSONB Data
1. Creating JSONB Columns
CREATE TABLE events ( id bigserial PRIMARY KEY , payload jsonb NOT NULL , created_at timestamptz NOT NULL DEFAULT now () );
Type Choose When jsonb Default; binary, indexable, dedups keys json Need verbatim text preservation
2. Inserting JSON Data
INSERT INTO events(payload) VALUES ( '{"user_id":42,"action":"login"}' ), ( '{"user_id":7,"items":[1,2,3]}' );
3. Using jsonb_build_object
SELECT jsonb_build_object( 'id' , id, 'email' , email, 'tags' , tags) FROM customers;
4. Using jsonb_build_array
SELECT jsonb_build_array(id, email, created_at) FROM customers;
5. Accessing JSON Fields
Operator Returns -> jsonb child ->> text child #> Nested via path array (jsonb) #>> Nested as text
SELECT payload -> 'user_id' , payload ->> 'action' FROM events; SELECT payload# > '{address,city}' , payload# >> '{items,0}' FROM events;
6. Accessing JSON Text
SELECT (payload ->> 'user_id' ):: int AS user_id FROM events;
Note: -> keeps the JSON type; cast ->> to native types before comparisons for index use.
7. Using Path Operators
SELECT payload # > '{address,zip}' FROM events; SELECT payload # - '{deprecated}' FROM events; -- remove path
8. Filtering with Containment
SELECT * FROM events WHERE payload @ > '{"action":"login"}' ; SELECT * FROM events WHERE payload @ > '{"items":[1]}' ; -- contains element
9. Using Existence Operators
Operator Test ? Top-level key exists ?| Any of keys exist ?& All keys exist
SELECT * FROM events WHERE payload ? 'user_id' ;
10. Using jsonb_set
UPDATE events SET payload = jsonb_set(payload, '{status}' , '"processed"' , true) WHERE id = 7 ;
Arg Meaning create_missing true → create path; false → only update existing
11. Using jsonb_insert
UPDATE events SET payload = jsonb_insert(payload, '{items,1}' , '99' , true);
12. Removing JSON Keys
UPDATE events SET payload = payload - 'temp' ; UPDATE events SET payload = payload - ARRAY ['a','b']; -- multiple UPDATE events SET payload = payload # - '{nested,key}' ;
13. Using jsonb_strip_nulls
SELECT jsonb_strip_nulls( '{"a":1,"b":null,"c":{"d":null}}' ); -- {"a":1,"c":{}}
14. Converting JSON to Records
SELECT * FROM jsonb_to_record( '{"a":1,"b":"hi"}' ::jsonb) AS x(a int , b text ); SELECT * FROM jsonb_to_recordset( '[{"a":1},{"a":2}]' ::jsonb) AS x(a int ); SELECT key , value FROM jsonb_each( '{"a":1,"b":2}' ::jsonb);
15. Using JSONB Aggregation
SELECT jsonb_agg(payload) FROM events WHERE created_at >= current_date; SELECT jsonb_object_agg( key , value ) FROM settings;