BigQuery中如何将嵌套JSON数组展开为独立列？

你不需要用子查询绕弯子，直接在主查询里用JSON提取函数从原数据中获取字段即可；如果一定要保留原查询结构，也可以通过子查询的正确写法实现字段提取。

方法1：直接从原数据提取（推荐） #

跳过单独提取items字段的步骤，直接从dv对象中获取嵌套的amount、status、code：

SELECT 
  JSON_VALUE(dv, '$.items.amount') AS amount,
  JSON_VALUE(dv, '$.items.status') AS status,
  JSON_VALUE(dv, '$.items.code') AS code
FROM mytable t
CROSS JOIN UNNEST(JSON_QUERY_ARRAY(t.jsonish_field)) AS dv
WHERE t.type = 'adjustment';

方法2：用子查询包裹原查询 #

将你原来的查询作为子查询，在外层语句中从items字段提取对应值：

SELECT 
  JSON_VALUE(items, '$.amount') AS amount,
  JSON_VALUE(items, '$.status') AS status,
  JSON_VALUE(items, '$.code') AS code
FROM (
  -- 原查询作为子查询
  SELECT 
    JSON_QUERY_ARRAY(dv,'$.items') AS items
  FROM mytable t
  CROSS JOIN UNNEST(JSON_QUERY_ARRAY(t.jsonish_field)) AS dv
  WHERE t.type = 'adjustment'
) AS sub;

注：如果你的数据库JSON提取函数不是JSON_VALUE，可替换为对应数据库的函数（比如JSON_EXTRACT_SCALAR、JSON_UNQUOTE(JSON_EXTRACT(...))等），核心是从JSON对象中提取指定路径的标量值。

内容的提问来源于stack exchange，提问作者booksoverborders