The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Create JSONB | '{"status":"open"}'::jsonb | View examples |
| Read JSON field | payload -> 'customer' | View examples |
| Read text field | payload ->> 'status' | View examples |
| Read a nested path | payload #>> '{customer,email}' | View examples |
| Test containment | payload @> '{"status":"open"}'::jsonb | View examples |
| Test a key | payload ? 'status' | View examples |
| Match a JSON path | payload @? '$.items[*] ? (@.price > 100)' | View examples |
| Build an object | jsonb_build_object('id', id, 'name', name) | View examples |
| Aggregate JSON objects | jsonb_agg(jsonb_build_object('id', id) ORDER BY id) | View examples |
| Set a nested value | jsonb_set(payload, '{status}', '"closed"'::jsonb) | View examples |
| Expand an array | CROSS JOIN LATERAL jsonb_array_elements(payload -> 'items') AS item | View examples |
| Index JSONB operations | CREATE INDEX events_payload_gin_idx
ON events
USING GIN (payload); | View examples |
json preserves input text details; jsonb stores a decomposed binary representation optimized for processing and indexing. Use jsonb for most queryable documents, keep stable relational attributes in typed columns, and validate document shape at ingestion rather than scattering assumptions across queries.
Step by step
Detailed examples
Distinguish missing, JSON null, and SQL null
jsonb normalizes object representation and rejects some values that json text may accept. -> returns JSON, ->> returns text, and extraction returns SQL null when structure is absent rather than raising. JSON null is still a JSON value, so test type and key existence when the distinction matters.
WITH docs(payload) AS (VALUES ('{"customer":{"email":"ada@example.com"},"status":null}'::jsonb))
SELECT payload -> 'status' AS json_value, payload ->> 'status' AS text_value, payload #>> '{customer,email}' AS email FROM docs;Use containment for structural matching
@> tests whether JSONB contains a structure and is often easier to index than extracting and casting every value. ? checks top-level keys or string array elements. SQL/JSON path supports richer predicates; lax and strict modes differ when structures do not match.
SELECT event_id
FROM events
WHERE payload @> '{"status":"open"}'::jsonb
AND payload @? '$.items[*] ? (@.price > 100)';Construct JSON from typed SQL values
Build functions convert SQL values according to PostgreSQL JSON rules and avoid error-prone string concatenation. Order aggregate inputs inside jsonb_agg when array order is part of the API. jsonb_set returns a new value, so an UPDATE must assign its result.
SELECT jsonb_build_object(
'customer_id', customer_id,
'orders', jsonb_agg(jsonb_build_object('id', order_id, 'total', total) ORDER BY created_at)
)
FROM orders
GROUP BY customer_id;Expand documents with lateral set-returning functions
jsonb_array_elements emits one JSONB row per array item and belongs in FROM for clear cardinality. Use WITH ORDINALITY when source order matters. Validate that the target is an array or normalize with CASE, because calling an array function on another JSON type raises an error.
SELECT o.order_id, item.value ->> 'sku' AS sku, item.ordinality
FROM orders AS o
CROSS JOIN LATERAL jsonb_array_elements(o.payload -> 'items') WITH ORDINALITY AS item(value, ordinality)
ORDER BY o.order_id, item.ordinality;Index stable access patterns, not every document
The default jsonb GIN operator class supports key, containment, and jsonpath operators; jsonb_path_ops is smaller and specialized for containment/jsonpath but not key-existence operators. Expression indexes can target frequently extracted scalars. Cast and validate types consistently so queries match the index expression.
CREATE INDEX events_payload_gin_idx ON events USING GIN (payload);
CREATE INDEX events_status_idx ON events ((payload ->> 'status'));Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.