JSON in a relational database

The question before the syntax

A JSON column is the right answer when the shape genuinely varies — a webhook payload you did not design, per-tenant settings nobody enumerated, an audit record of whatever happened. It is the wrong answer when the shape is known and you reached for it to avoid a migration.

What you give up by putting a known field in JSON:

• Constraints. No NOT NULL, no foreign key, no unique. The database will store {"emial": "..."} without a word.
• Types. Everything is text, a number, a boolean or null, and the difference between "5" and 5 becomes your problem.
• Statistics. The planner knows how many distinct values a column has. Inside a document it mostly guesses.

A useful split: JSON for what you receive, columns for what you query.

Reading one field

Syntax is the part that differs most between engines.

SELECT id, json_extract(payload, '$.customer.email') AS email
FROM events
WHERE json_extract(payload, '$.type') = 'order.paid';

That WHERE reads a field out of every row — a full scan, because the condition is an expression, not a column. Which is the next problem.

Indexing inside a document

Two options, and they answer different questions:

-- 1. Index one known path, as if it were a column
CREATE INDEX idx_events_type ON events ((payload->>'type'));

-- 2. Index the whole document for containment queries (PostgreSQL, jsonb)
CREATE INDEX idx_events_payload ON events USING GIN (payload);

The expression index makes exactly the query above fast, and nothing else. The GIN index supports @> containment across any key, at the cost of size and write time. If you find yourself adding expression indexes for a third path, that is the schema telling you those three fields are columns.

In PostgreSQL, pick jsonb over json: json stores the text as it arrived, jsonb stores a parsed form that can be indexed, and only jsonb supports the operators worth having.

An array becomes rows

The part that unlocks the rest — an array inside a document is not a set the query can join against until it is expanded:

-- SQLite
SELECT e.id, item.value AS tag
FROM events e, json_each(e.payload, '$.tags') AS item;

-- PostgreSQL
SELECT e.id, tag
FROM events e, jsonb_array_elements_text(e.payload->'tags') AS tag;

One row per element. From there it is an ordinary table: group it, join it, count it.

The reverse — collapsing rows back into one array — is the shape that produces a nested API response in a single query:

SELECT o.id,
       json_group_array(json_object('sku', i.sku, 'qty', i.qty)) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

Updating a field is rewriting the document

UPDATE events
SET payload = json_set(payload, '$.status', 'processed')
WHERE id = 1;

There is no partial write: the engine reads the document, produces a new one, and stores it. On a large document updated often, that is real cost — and two concurrent updates to two different fields will still clobber each other, because each wrote a whole document. If a field changes on its own schedule, it wants to be its own column.