sql - 如何使用 json 函数遍历 bigQuery 中的 json 对象

Question

我有以下方式的示例数据： {"_id": {"$oid": "60c1c3660a7b446da70f7b5f"}, "type": "AppInstalled", "created": "2021-06-01T11:04:55.663+00:00 ", "修改": "2021-06-01T11:04:55.663+00:00", "active": true, "facebookGroups": [{"groupID": "378564210251572", "groupName": "Carli Test" }，{“groupID”：“885308445383776”，“groupName”：“测试组 2”}，{“groupID”：“155199286660022”，“groupName”：“测试组 1”}，{“groupID”：“317354919962979” , "groupName": "测试组 3"}, {"groupID": "3017378798492909", "groupName": "测试组 4"}, {"groupID”：“1965228136958920”，“groupName”：“测试组 5”}，{“groupID”：“853388548596122”，“groupName”：“测试组 6”}]}

如果我想将所有相应的 group_id 和 group_names 分成两列，我该怎么做？我知道我们可以得到一个项目，比如编写下面的代码：

选择 JSON_EXTRACT(facebookData, '$.facebookGroups[0].groupID') AS GroupID, JSON_EXTRACT(facebookData, '$.facebookGroups[0].groupName') AS GroupName FROM X.Y.Z

标签： sqlgoogle-bigquery

Answer 1

先用 . 从数组中提取项目json_query_array。然后提取项目的字段json_query：

with mytable as (
    select '{"_id": {"$oid": "60c1c3660a7b446da70f7b5f"}, "type": "AppInstalled", "created": "2021-06-01T11:04:55.663+00:00", "modified": "2021-06-01T11:04:55.663+00:00", "active": true, "facebookGroups": [{"groupID": "378564210251572", "groupName": "Carli Test"}, {"groupID": "885308445383776", "groupName": "testing group 2"}, {"groupID": "155199286660022", "groupName": "Testing group 1"}, {"groupID": "317354919962979", "groupName": "Testing group 3"}, {"groupID": "3017378798492909", "groupName": "Testing group 4"}, {"groupID": "1965228136958920", "groupName": "Testing group 5"}, {"groupID": "853388548596122", "groupName": "Testing group 6"}]}' as facebookData
)
select json_query(item, '$.groupID') as groupID, json_query(item, '$.groupName') as groupName
from mytable, unnest(json_query_array(facebookData, '$.facebookGroups')) as item