I have sample data in below way: {"_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"}]}

If i want to get all respective group_id and group_names into two columns how can i do that?, i know that we can get a single item like writing below code:

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

CodePudding user response：

Extract items from array first with json_query_array. Then extract fields of items with 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