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 path8. Filtering with Containment
SELECT * FROM events WHERE payload @> '{"action":"login"}';
SELECT * FROM events WHERE payload @> '{"items":[1]}'; -- contains element9. 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;