âš¡IntermediateJSON & JSONB
Advanced JSONB Operations
Query, filter, and update JSONB documents using advanced operators and functions.
Overview
Beyond basic extraction, JSONB supports JSONPath queries (PostgreSQL 12+), the @? operator for path existence, and @@ for jsonpath predicates. You can delete keys with the - operator, concatenate JSONB objects with ||, and use jsonb_path_query for complex extractions.
Understanding these operations is key to using PostgreSQL as a document store for semi-structured data.
Key Points
- •jsonb - 'key' delete a key from JSONB
- •jsonb || jsonb merge/update JSONB objects
- •jsonb_path_query(doc, '$.field') JSONPath queries (PG 12+)
- •@? 'jsonpath' check if path exists
- •jsonb_to_recordset() convert JSONB array to rows
- •jsonb_build_object(k1,v1,k2,v2) construct JSONB in SQL
Examples
JSONB manipulation
-- Delete a key
SELECT '{"a":1,"b":2}'::jsonb - 'b'; -- {"a": 1}
-- Merge objects (right wins on conflict)
SELECT '{"a":1,"b":2}'::jsonb || '{"b":99,"c":3}'::jsonb;
-- {"a": 1, "b": 99, "c": 3}
-- JSONPath query
SELECT jsonb_path_query('{"users":[{"name":"Alice"},{"name":"Bob"}]}', '$.users[*].name');
-- Build JSONB
SELECT jsonb_build_object('name', 'Alice', 'age', 30);Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4