Postgres 17 reminded me JSON is table work too

JSON lets messy data move before its final schema is known. Webhooks, model outputs, preferences, telemetry, and vendor payloads often arrive that way.

The mistake is treating the blob as the end of modeling. Producers organize data around their own APIs. Analysis wants rows, columns, joins, filters, null semantics, and stable names.

JSON_TABLE can form the bridge:

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

Now the nested array can be joined, aggregated, and checked like rows rather than extracted repeatedly in application code.

Nulls need an explicit policy. Missing fields, JSON null, empty strings, wrong types, path failures, and omitted model keys are different states. The query layer should decide which states are accepted, rejected, or surfaced.

I prefer raw JSON plus stable projections:

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';

Keep the original payload for replay and drift checks. If the product depends on a JSON field, that field is already part of the schema. SQL just makes the contract visible.