如何在BigQuery中使用JSON函数遍历JSON对象提取Facebook群组数据

你可以通过JSON_EXTRACT_ARRAY结合UNNEST打平数组的方式，无需手动指定下标即可遍历所有facebookGroups元素，完整实现代码如下：

SELECT
  JSON_EXTRACT_SCALAR(group_item, '$.groupID') AS GroupID,
  JSON_EXTRACT_SCALAR(group_item, '$.groupName') AS GroupName
FROM
  `X.Y.Z`,
  UNNEST(JSON_EXTRACT_ARRAY(facebookData, '$.facebookGroups')) AS group_item

逻辑说明 #

• JSON_EXTRACT_ARRAY(facebookData, '$.facebookGroups')：将JSON字段中facebookGroups数组提取为BigQuery原生数组类型，数组内每个元素为对应单个群组的JSON对象
• UNNEST(...) AS group_item：将数组拆分为多行，每行对应一个群组的JSON对象，别名设置为group_item
• JSON_EXTRACT_SCALAR：从单个群组JSON对象中提取对应字段值，会自动去除JSON字符串自带的双引号，返回纯文本内容，比JSON_EXTRACT更适配字符串类字段的提取需求

可选扩展 #

如果需要保留facebookGroups为空/不存在的原始行，可以将隐式交叉连接改为左连接，调整如下：

SELECT
  JSON_EXTRACT_SCALAR(group_item, '$.groupID') AS GroupID,
  JSON_EXTRACT_SCALAR(group_item, '$.groupName') AS GroupName
FROM
  `X.Y.Z`
LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(facebookData, '$.facebookGroups')) AS group_item ON TRUE

如果需要同时保留原始数据的其他字段，直接在SELECT子句中补充对应字段的提取逻辑即可。

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