如何在Google BigQuery中提取多属性JSON数组的指定字段

嘿，我完全懂你用字符串拆分替换的痛苦——不仅代码乱糟糟的，还容易出问题，尤其是JSON结构稍微变一点就炸了！其实BigQuery早就提供了专门处理JSON数组的函数，几步就能搞定你的需求，而且效率高多了。

核心思路 #

我们需要先把JSON数组拆成单个的JSON对象，再把每个对象展开成单独的行，最后提取每个字段就和你熟悉的单条JSON处理一样了。这里要用到两个关键函数：JSON_EXTRACT_ARRAY和UNNEST。

完整SQL示例 #

假设你的表名为your_table，存储JSON数组的列名为prize_ranks，下面的SQL就能直接提取你要的所有字段：

SELECT
  -- 把字符串类型的数值转换成INT64，方便后续计算或排序
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.rank_start') AS INT64) AS rank_start,
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.rank_end') AS INT64) AS rank_end,
  JSON_EXTRACT_SCALAR(item, '$.prize.unit_type') AS unit_type,
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.prize.units') AS INT64) AS units,
  JSON_EXTRACT_SCALAR(item, '$.prize.unit_currency') AS unit_currency
FROM
  your_table,
  -- 先把JSON数组转换成BigQuery数组，再展开成多行
  UNNEST(JSON_EXTRACT_ARRAY(prize_ranks)) AS item

关键步骤解释 #

1. JSON_EXTRACT_ARRAY(prize_ranks): 把列中的JSON字符串转换成BigQuery原生的数组类型，每个元素就是数组里的一个JSON对象。
2. UNNEST(...) AS item: 将数组的每个元素拆分成单独的行，这样每一行就对应一个排名区间的JSON对象了。
3. JSON_EXTRACT_SCALAR(...): 针对每个单独的JSON对象item，提取你需要的字段。用SAFE_CAST是为了把字符串形式的数值转换成整数类型，避免后续操作出错。

注意：非标准JSON的处理 #

你给出的示例JSON里，键名是没有双引号的（比如rank_start:而不是"rank_start":），这其实不符合标准JSON格式，BigQuery的JSON函数可能无法正常解析。如果你的原始数据确实是这种格式，需要先把它转换成标准JSON，比如通过嵌套REPLACE来补全引号：

SELECT
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.rank_start') AS INT64) AS rank_start,
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.rank_end') AS INT64) AS rank_end,
  JSON_EXTRACT_SCALAR(item, '$.prize.unit_type') AS unit_type,
  SAFE_CAST(JSON_EXTRACT_SCALAR(item, '$.prize.units') AS INT64) AS units,
  JSON_EXTRACT_SCALAR(item, '$.prize.unit_currency') AS unit_currency
FROM
  your_table,
  UNNEST(JSON_EXTRACT_ARRAY(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      prize_ranks,
      '{rank_start:', '{"rank_start":'),
      ', rank_end:', ', "rank_end":'),
      ', prize: {unit_type:', ', "prize": {"unit_type":'),
      ', units:', ', "units":'),
      ', unit_currency:', ', "unit_currency":'),
      '}}', '}}')
  )) AS item

不过还是建议尽量让上游数据输出标准JSON，这样后续处理会省心很多！

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