MySQL
MySQL
SELECT o.id, jt.sku, jt.qty
FROM orders o,
JSON_TABLE(o.items, '$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
qty INT PATH '$.qty'
)) AS jt
WHERE o.id = 100;PostgreSQL 等价
PostgreSQL 等价
SELECT o.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item;| MySQL | JSON_TABLE(expr, path COLUMNS(...))；支持嵌套子句 NESTED PATH |
|---|---|
| PostgreSQL | jsonb_array_elements / jsonb_to_recordset + LATERAL |
| 用途 | 订单明细、事件属性、标签数组的规范化查询 |