Postgres 17 reminded me JSON is table work too

Postgres 17 was a reminder that JSON is table work too.

Application teams like JSON because it lets messy data move before the schema is fully understood. Product events, webhook payloads, model outputs, user preferences, telemetry envelopes, and third-party responses often arrive before anyone knows the final relational shape. That is fine. The mistake is pretending the blob is the end of the modeling work.

document shape is not analysis shape

JSON usually mirrors the producer. A webhook payload mirrors a vendor API. A model output mirrors a prompt schema. A UI settings blob mirrors a screen. Analysis wants something else: rows, columns, joins, filters, null handling, and stable names.

the bridge is the useful part

JSON_TABLE lets you project JSON into a table source inside SQL.

SELECT item.sku, item.qty
FROM orders o,
JSON_TABLE(
  o.payload,
  '$.items[*]'
  COLUMNS (
    sku text PATH '$.sku',
    qty int PATH '$.quantity'
  )
) AS item;

That is the right shape for a query because it makes the nested array behave like rows. You can join it, aggregate it, compare it with a products table, and catch bad payloads without reimplementing the same extraction logic in application code.

nulls need decisions

JSON gives you several different “nothing” cases: missing field, JSON null, empty string, wrong type, path failure, or a model that omitted a key because the prompt was vague. Those are not the same thing, and the SQL layer should force a choice about what each one means.

keep the raw blob, expose the contract

I like raw JSON plus stable relational projections. A view, generated column, or materialized view can give analysts the shape they need while keeping the original payload around for replay and drift checks.

CREATE VIEW extracted_invoice_fields AS
SELECT
  id,
  JSON_VALUE(output, '$.invoice_number') AS invoice_number,
  JSON_VALUE(output, '$.vendor_name') AS vendor_name,
  JSON_VALUE(output, '$.total' RETURNING numeric) AS total
FROM model_runs
WHERE task = 'invoice_extraction';

That is the part I keep coming back to. If the product depends on a JSON field, that field is already part of the schema. Postgres just makes it harder to keep pretending otherwise.