JSON in SQL (jsonb in Postgres)

TL;DR

Postgres has two JSON types:

• json — stores the input text verbatim. Whitespace and key order preserved. Slow to query (re-parses every read).
• jsonb — binary, decomposed storage. No whitespace preserved, key order normalized, duplicates dropped. Fast to query, indexable with GIN. This is what you want, always.

The jsonb operators worth memorizing:

JSON in SQL is the right tool when you have genuinely irregular schema — third-party API payloads, raw event blobs, ML inference outputs with varying shapes — and the wrong tool when you’re using it to avoid designing a schema. Reach for it when you must; promote frequently-queried fields to real columns when you can.

A worked example

CREATE TABLE events (
    id      bigserial PRIMARY KEY,
    ts      timestamptz NOT NULL,
    kind    text,
    payload jsonb
);

INSERT INTO events (ts, kind, payload) VALUES
  ('2026-04-01 10:00', 'view',     '{"user": {"id": 1, "country": "US"}, "page": "/home"}'),
  ('2026-04-01 10:05', 'click',    '{"user": {"id": 1, "country": "US"}, "target": "buy", "price": 19.99}'),
  ('2026-04-01 11:00', 'purchase', '{"user": {"id": 2, "country": "UK"}, "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}], "total": 50.0}'),
  ('2026-04-01 12:00', 'error',    '{"user": {"id": 1}, "code": 500, "message": "boom"}');

Extracting fields

SELECT id, kind,
       payload -> 'user' ->> 'id'      AS user_id,
       payload -> 'user' ->> 'country' AS country,
       (payload ->> 'price')::numeric  AS price
FROM   events;

+----+----------+---------+---------+--------+
| id | kind     | user_id | country | price  |
+----+----------+---------+---------+--------+
|  1 | view     |    1    | US      | NULL   |
|  2 | click    |    1    | US      |  19.99 |
|  3 | purchase |    2    | UK      | NULL   |
|  4 | error    |    1    | NULL    | NULL   |
+----+----------+---------+---------+--------+

The chain payload -> 'user' ->> 'id' first drills into the user object (returning jsonb via ->), then extracts id as text via ->>. The final ::numeric cast handles price.

Mnemonic. -> returns JSON (good for chaining); ->> returns text (good for casting and end use). End every chain with ->> unless you need further drilling.

Filtering on JSON

-- Click events for US users with price > 10
SELECT id, payload->>'target' AS target
FROM   events
WHERE  kind = 'click'
  AND  payload @> '{"user": {"country": "US"}}'
  AND  (payload->>'price')::numeric > 10;

@> (“contains”) is the workhorse: “does the left jsonb contain the right jsonb as a subset?” It’s GIN-indexable (see below).

Existence checks

-- Events that have an 'items' array
SELECT * FROM events WHERE payload ? 'items';

-- Events whose payload contains either 'price' or 'total'
SELECT * FROM events WHERE payload ?| array['price', 'total'];

-- Events whose payload contains both 'user' and 'page'
SELECT * FROM events WHERE payload ?& array['user', 'page'];

Working with arrays inside JSON

jsonb_array_elements unnests a JSON array into rows:

SELECT e.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM   events e,
       jsonb_array_elements(e.payload -> 'items') item
WHERE  e.kind = 'purchase';

+----+-----+-----+
| id | sku | qty |
+----+-----+-----+
|  3 | A   |  2  |
|  3 | B   |  1  |
+----+-----+-----+

The comma , between events e and jsonb_array_elements(...) is an implicit LATERAL CROSS JOIN. Each row in events is paired with the unnested elements of its items array.

Indexing jsonb with GIN

A GIN (Generalized Inverted Index) index on a jsonb column makes @> lookups fast:

CREATE INDEX events_payload_gin ON events USING gin (payload);

Now WHERE payload @> '{"kind": "click"}' is index-supported. The GIN index covers all keys; for selective subsets (only the user.id key), use a more targeted expression index:

CREATE INDEX events_user_id ON events ((payload -> 'user' ->> 'id'));

Then WHERE payload -> 'user' ->> 'id' = '1' is a B-tree lookup.

The GIN-vs-expression-index tradeoff:

• GIN on the whole column: covers any contains-query; bigger index; slower writes.
• Expression index on a specific path: smaller; faster for the one query you support; doesn’t help anything else.

For ML pipelines, where you usually query a handful of fields repeatedly, expression indexes are often better.

Building jsonb — jsonb_build_object, jsonb_agg

-- Build a per-event compact summary
SELECT jsonb_build_object(
           'id',     id,
           'kind',   kind,
           'user',   payload -> 'user',
           'ts_iso', to_char(ts, 'YYYY-MM-DD"T"HH24:MI:SSZ')
       ) AS summary
FROM   events;

-- Aggregate items per purchase event into a JSON array
SELECT id, jsonb_agg(item) AS items
FROM   events e,
       jsonb_array_elements(e.payload -> 'items') item
GROUP  BY id;

Useful for emitting JSON to APIs without a separate serializer.

Updating jsonb fields

-- Set / overwrite a field
UPDATE events SET payload = jsonb_set(payload, '{verified}', 'true') WHERE id = 1;

-- Delete a field
UPDATE events SET payload = payload - 'temp' WHERE payload ? 'temp';

-- Merge / shallow union (right wins)
UPDATE events SET payload = payload || '{"reviewed": true, "reviewer": "me"}' WHERE id = 2;

jsonb_set is path-based and creates intermediate objects on demand. || is shallow merge; for deep merges Postgres has jsonb_strip_nulls and a few helpers, but the recursive deep merge is something you write yourself.

Common pitfalls

• json vs jsonb. Always pick jsonb for new columns. json exists for “preserve the exact input” use cases; that’s almost never what you need.
• -> vs ->>. -> returns jsonb; ->> returns text. Forgetting this gives you cryptic comparison errors (you can’t = a jsonb to a text directly).
• Casting strings to numeric naively. (payload->>'price')::numeric errors if any payload’s price isn’t a valid number. Use (payload->>'price')::numeric only on rows you know are numeric, or wrap with CASE WHEN payload->>'price' ~ '^[0-9.]+$' THEN ....
• Missing keys return NULL. payload->>'nonexistent' is NULL, which then triggers all the NULL gotchas.
• No index → table scan. A WHERE payload @> '{"x":1}' without a GIN index reads every row. For tables with millions of rows, that’s an outage.
• Schemaless drift. payload->>'user_id' works until someone renames the field to userId. Schemaless = no contract = silent breakage. Promote stable fields to typed columns.

Production patterns for ML

1. Raw event ingestion + extracted feature columns. The standard two-layer pattern:

CREATE TABLE raw_events (
    id      bigserial PRIMARY KEY,
    ts      timestamptz NOT NULL,
    payload jsonb       NOT NULL
);

CREATE TABLE feature_events (
    id          bigint   PRIMARY KEY REFERENCES raw_events(id),
    user_id     int      NOT NULL,
    country     text,
    kind        text     NOT NULL,
    amount      numeric,
    raw_payload jsonb        -- still kept for debugging
);

INSERT INTO feature_events (id, user_id, country, kind, amount, raw_payload)
SELECT id,
       (payload->'user'->>'id')::int,
       payload->'user'->>'country',
       payload->>'kind',
       (payload->>'amount')::numeric,
       payload
FROM   raw_events
WHERE  id NOT IN (SELECT id FROM feature_events);

Raw layer captures everything verbatim; feature layer has typed columns and indexes. Models query the feature layer; debugging falls back to raw.

2. Snowflake / BigQuery equivalents. Snowflake has VARIANT (their jsonb-equivalent) and dot/colon access (payload:user.id). BigQuery has JSON and STRUCT types with JSON_VALUE / JSON_QUERY. Same idea, slightly different syntax.

Resources

• PostgreSQL — JSON functions — postgresql.org/docs
• PostgreSQL — JSON types and indexing — postgresql.org/docs
• PostgreSQL — GIN indexes — postgresql.org/docs
• PostgreSQL documentation — postgresql.org/docs