JSONB and SQL/JSON

Description

JSON turns up in every backend: webhook payloads stored as they arrive, per-customer settings, feature flags, API responses you'd like the database to put together for you. PostgreSQL handles JSON very well - it can read it, search it, index it and build it. The catch is that JSON values follow their own rules, and SQL written as if they were ordinary columns goes wrong quietly:

• A filter for payments "over 1,000" returns a 900 payment, because the amounts were compared as text.
• An export drops every order whose item list is empty.
• An API response gives every customer without orders a phantom order: {"id": null, "total": null}.

We'll go from the basics to the parts that need care:

1. json versus jsonb - which one to use.
2. Reading values with -> and ->>, and the text-comparison trap.
3. Filtering with containment (@>) and SQL/JSON paths.
4. Turning JSON arrays into rows with JSON_TABLE.
5. Building JSON responses, and changing documents.
6. Indexing JSON, and when a plain column is the better home.

Examples use five events with payload jsonb, and two customers - one with orders, one without; every result was produced in the sandbox on PostgreSQL 18.

json or jsonb

Postgres has two JSON types. json stores the text exactly as given; jsonb parses it into a binary form - duplicate keys collapse to the last one, key order and whitespace aren't kept, and it can be indexed and compared:

SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;

Use jsonb unless you must keep the original text byte for byte (a signed webhook body you'll verify later, say).

Reading values: -> and ->>

-> returns JSON (jsonb), ->> returns text, and #>> follows a path of keys to text:

SELECT payload -> 'user'             AS user_json,
       payload -> 'user' ->> 'email'  AS email,
       payload #>> '{user,id}'        AS user_id
FROM events
WHERE id = 1;

payload -> 'currency' is the JSON string "usd" - quotes included - so comparing or displaying it as text needs ->>. And ->> always gives text, even for numbers, which makes comparisons alphabetical:

SELECT id,
       payload ->> 'amount'                    AS amount,
       payload ->> 'amount' > '1000'           AS as_text,
       (payload ->> 'amount')::numeric > 1000  AS as_number
FROM events
WHERE payload ->> 'type' = 'payment'
ORDER BY id;

As text, '900' > '1000' because 9 sorts after 1. Cast before comparing numbers or dates: (payload ->> 'amount')::numeric. The cast from ->> text also copes with event 4, whose amount arrived as the string "15000"; casting the JSON value itself ((payload -> 'amount')::numeric) works for JSON numbers but fails on strings.

A key that isn't there gives NULL, and jsonb_typeof tells you what's there:

SELECT id,
       payload ->> 'plan'                AS plan,
       jsonb_typeof(payload -> 'amount') AS amount_type
FROM events
ORDER BY id;

So a condition like payload ->> 'plan' <> 'free' quietly excludes every event without a plan (NULL isn't unequal to anything) - here it matches only event 1.

Filtering: containment and SQL/JSON paths

@> tests whether a document contains another - every key and value on the right appears on the left (for arrays, every element):

SELECT id FROM events
WHERE payload @> '{"type": "payment", "currency": "usd"}'
ORDER BY id;

? tests for a key (or an array element): payload -> 'tags' ? 'card'. For conditions inside arrays and nested values, SQL/JSON path expressions do the work; since Postgres 17 the standard functions JSON_EXISTS, JSON_VALUE and JSON_QUERY accept them:

SELECT id,
       JSON_VALUE(payload, '$.amount' RETURNING numeric) AS amount,
       JSON_EXISTS(payload, '$.items[*] ? (@.qty > 2)')  AS bulk_item
FROM events
WHERE JSON_EXISTS(payload, '$.amount')
ORDER BY id;

JSON_VALUE(... RETURNING numeric) extracts a scalar and converts it - event 4's string included. $.items[*] ? (@.qty > 2) means "some item with a quantity above 2".

Arrays to rows: JSON_TABLE

JSON_TABLE (Postgres 17+) turns a JSON array into rows with typed columns:

SELECT e.id, i.sku, i.qty
FROM events e,
     JSON_TABLE(e.payload, '$.items[*]'
                COLUMNS (sku text PATH '$.sku',
                         qty int  PATH '$.qty')) AS i
ORDER BY e.id, i.sku;

Listing it in FROM joins each event to its own items - and an event with no items (no items key, or an empty array) produces no rows, so it vanishes. To keep every event, use LEFT JOIN JSON_TABLE(...) ON true:

SELECT e.id, i.sku, i.qty
FROM events e
LEFT JOIN JSON_TABLE(e.payload, '$.items[*]'
                     COLUMNS (sku text PATH '$.sku',
                              qty int  PATH '$.qty')) AS i ON true
WHERE e.payload ->> 'type' <> 'payment'
ORDER BY e.id, i.sku;

A column can supply a value when the key is missing - qty int PATH '$.qty' DEFAULT 1 ON EMPTY - and NESTED PATH flattens arrays inside arrays. Before Postgres 17 the same job was CROSS JOIN LATERAL jsonb_array_elements(payload -> 'items') (or LEFT JOIN LATERAL ... ON true), with the same empty-array behavior.

Building JSON: jsonb_build_object and jsonb_agg

jsonb_build_object makes an object from key/value pairs, and jsonb_agg collects a group's values into an array - so one query can return a customer with their orders nested. Give jsonb_agg an ORDER BY, or the array's order is whatever the plan produced. The classic bug is a customer with no orders:

SELECT c.id,
       jsonb_agg(jsonb_build_object('id', o.id, 'total', o.total) ORDER BY o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;

The LEFT JOIN keeps Liam with one all-NULL order row, and jsonb_agg dutifully builds an object from it. (Aggregating o.id alone would give [null].) Skip those rows with FILTER, and turn "no rows at all" - which aggregates to NULL - into an empty array:

SELECT c.id,
       COALESCE(jsonb_agg(jsonb_build_object('id', o.id, 'total', o.total) ORDER BY o.id)
                    FILTER (WHERE o.id IS NOT NULL),
                '[]') AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;

Changing JSON

jsonb_set replaces the value at a path, || merges objects (right side wins), and - removes a key:

UPDATE events
SET payload = (jsonb_set(payload, '{plan}', '"business"')  -- replace a value
               || '{"beta": true}')                        -- add or overwrite keys
              - 'user'                                     -- remove a key
WHERE id = 1;

The parentheses matter: - binds more tightly than ||, so without them Postgres tries to subtract 'user' from '{"beta": true}' and fails. Each update rewrites the whole value (and the row), so a large document that changes often is better split into columns or its own table.

Indexing JSON

A GIN index serves containment on any key. The jsonb_path_ops operator class supports only @> (and path matches) and is often smaller and faster for them than the default - not always: the deep dive measures both. On 10,000 events, 2% of them refunds:

CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
EXPLAIN (COSTS OFF)
SELECT id FROM events WHERE payload @> '{"type": "refund"}';

Bitmap Heap Scan on events
  Recheck Cond: (payload @> '{"type": "refund"}'::jsonb)
  ->  Bitmap Index Scan on events_payload_idx
        Index Cond: (payload @> '{"type": "refund"}'::jsonb)

The same index does nothing for payload ->> 'type' = 'refund' - that plan is a Seq Scan - because it's a different operator. For a key you filter by value, an expression index fits: CREATE INDEX ON events ((payload ->> 'type')). Or write the filter as containment.

Column or JSON?

It's common to have both: promote the fields you query to columns (a generated column can extract them), and keep the full document beside them. As a decision:

Where it breaks

• Comparing ->> text as numbers or dates - cast first.
• -> where you meant ->> - you get JSON, quotes included.
• Missing keys - NULL, which drops rows from = and <> alike.
• JSON_TABLE or jsonb_array_elements in a plain join - rows with empty arrays disappear; LEFT JOIN ... ON true.
• jsonb_agg over a LEFT JOIN - a phantom null element; use FILTER and COALESCE(..., '[]'), and ORDER BY inside the aggregate.
• Expecting a GIN index to serve ->> equality - it serves @>; index the expression instead.

Tradeoffs

• jsonb vs columns: jsonb adds fields without migrations; columns give types, constraints, statistics and smaller rows. Promote what you query.
• Building JSON in SQL vs in the app: one query with jsonb_agg saves round trips and avoids N+1 queries; app-side mapping is easier to change and test.
• GIN jsonb_path_ops vs default jsonb_ops: path_ops serves only @> and path matches (not key-exists ?), and is often - not always - the smaller of the two.

Going further. The deep dive (next tab) covers how jsonb is stored and compressed, why updating one key rewrites the whole document, the planner's blind spot for JSON fields (and the statistics that fix it), GIN index internals and sizes, validating JSON with constraints, and how other databases handle JSON.

Check yourself

1. Why does WHERE payload ->> 'amount' > '1000' return a payment of 900?

2. An export flattens doc -> 'items' with JSON_TABLE in the FROM list. What happens to an order whose items array is empty?

3. A customer with no orders gets "orders": [{"id": null, "total": null}]. What's the fix?

Key takeaways

• Use jsonb; -> returns JSON, ->> text, #>> text at a path, and a missing key is NULL.
• Cast ->> values before comparing numbers or dates - text compares alphabetically.
• Filter with @> and SQL/JSON paths (JSON_EXISTS, JSON_VALUE in Postgres 17+); flatten arrays with JSON_TABLE, and LEFT JOIN ... ON true to keep rows with empty arrays.
• Build responses with jsonb_build_object and jsonb_agg(... ORDER BY ...), guarding empty groups with FILTER and COALESCE(..., '[]').
• A GIN index serves @>; an expression index serves ->> equality. Promote fields you query often to columns.