我有以下方式的示例資料：{"_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": "3173549199629 , "groupName": "測驗組 3"}, {"groupID": "3017378798492909", "groupName": "測驗組 4"}, {"groupID": "1965228136958920", "groupName": "Testing group 5"}, {"groupID": "853388548596122", "groupName": "Testing group 6"}]}
如果我想將所有相應的 group_id 和 group_names 放入兩列，我該怎么做？，我知道我們可以獲得一個專案，例如撰寫以下代碼：
SELECT JSON_EXTRACT(facebookData, '$.facebookGroups[0].groupID') AS GroupID, JSON_EXTRACT(facebookData, '$.facebookGroups[0].groupName') AS GroupName FROM
X.Y.Z
uj5u.com熱心網友回復：
首先從陣列中提取專案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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/316318.html
標籤：sql 谷歌-bigquery
上一篇：SQL注入中間查詢
下一篇：巧妙地縮放影像，無需外部庫