BigQuery解析JSON列报错：标量子查询返回多行，求解决方案

错误原因 #

你遇到的错误是因为处理JSON列中的数组时，使用了标量子查询（要求仅返回单个值），但实际每个JSON对象的items或condiment数组包含多个元素，导致子查询返回多行，违反了标量子查询的单行返回规则。而测试单个JSON字符串时，你可能仅处理了单个元素或采用了不会触发多行返回的写法，因此未报错。

解决方案SQL #

假设你的表名为your_table，存储JSON的列名为json_column，可以通过展开数组（UNNEST）+ 行转列实现预期结果：

WITH parsed_data AS (
  -- 展开items数组，再展开每个item下的condiment数组，给调味品标记序号
  SELECT
    JSON_VALUE(item, '$.name') AS drink_name,
    JSON_VALUE(condiment, '$.name') AS condiment_name,
    ROW_NUMBER() OVER(PARTITION BY JSON_VALUE(item, '$.name') ORDER BY JSON_VALUE(condiment, '$.name')) AS condiment_seq
  FROM
    your_table,
    UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.items')) AS item,
    UNNEST(JSON_EXTRACT_ARRAY(item, '$.condiment')) AS condiment
)
-- 将调味品按序号转成对应列
SELECT
  drink_name,
  MAX(IF(condiment_seq = 1, condiment_name, NULL)) AS condiment_1,
  MAX(IF(condiment_seq = 2, condiment_name, NULL)) AS condiment_2,
  MAX(IF(condiment_seq = 3, condiment_name, NULL)) AS condiment_3
FROM parsed_data
GROUP BY drink_name
ORDER BY drink_name;

代码说明 #

1. UNNEST展开数组：通过两次UNNEST分别拆解JSON中的items数组和每个item下的condiment数组，把嵌套结构转成扁平行数据。
2. 标记调味品序号：用ROW_NUMBER()给每个饮品下的调味品按名称排序并标记序号，为后续行转列做准备。
3. 行转列：使用MAX(IF(...))的方式，将同一饮品下不同序号的调味品映射到对应的列，缺失位置自动填充NULL。

如果JSON中存在超过3个调味品的情况，只需继续添加MAX(IF(condiment_seq = N, condiment_name, NULL)) AS condiment_N即可适配。

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