BigQuery如何将嵌套JSON数组展开为每个tag对应一行的多行数据

实现代码 #

将以下语句中的your_table_name替换为你的实际表名即可直接运行：

SELECT
  id,
  title,
  -- 如果需要数值类型的tag，可替换为 CAST(tag AS INT64) AS tag
  tag
FROM
  `your_table_name`,
  -- 第一次展开metadata顶层的JSON数组，得到单个JSON对象
  UNNEST(JSON_EXTRACT_ARRAY(metadata)) AS metadata_obj,
  -- 第二次展开JSON对象内部的tags数组，得到单个tag值
  UNNEST(JSON_VALUE_ARRAY(metadata_obj, '$.tags')) AS tag

逻辑说明 #

• 第一层UNNEST配合JSON_EXTRACT_ARRAY：先将metadata字段存储的顶层JSON数组解析为BigQuery可识别的数组结构，再拆分为多行，每行对应一个包含tags、categories字段的JSON对象。
• 第二层UNNEST配合JSON_VALUE_ARRAY：从拆分得到的单个JSON对象中提取tags字段的数组，再次拆分为多行，每行对应一个tag值，最终得到每行一个tag的预期输出结果。

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