JSON_TABLE turns a document into rows

A JSON array in a column can be joined against as a table, which is occasionally the right answer and is more often a sign the data wanted a table.

SELECT o.id, l.sku, l.qty
FROM orders o,
JSON_TABLE(
  o.lines, '$[*]'
  COLUMNS (
    sku VARCHAR(32) PATH '$.sku',
    qty INT PATH '$.qty' DEFAULT '0' ON EMPTY
  )
) AS l;

-- no index can help this. every row is parsed.

The ON EMPTY and ON ERROR clauses are worth setting explicitly, because the default is to produce NULL silently for a malformed document — which turns a data quality problem into a quiet undercount in a report. The performance characteristic is the important one: this parses every document in the scanned set and no index can help, so it belongs in a one-off migration or a small result set rather than on a hot path.