Extract/ Unnest Arrays from Google Ads BigQuery Data Transfer
I am trying to extract the Unnest data contained in JSON Arrays that Google Ads sends via BigQuery Data Transfers. Unfortunately, I am getting stuck in the middle.
Original Data in a BigQuery Table:
CreativeId | ResponsiveSearchAdDescriptions |
---|---|
487067671679 | [{"assetText":"SearchAds Description Text 1","assetId":12443453594,"pinnedField":"DESCRIPTION_1","assetPerformanceLabel":"PENDING","assetApprovalStatus":"APPROVED"},{"assetText":"SearchAds Description Text 2","assetId":12443453165,"assetPerformanceLabel":"PENDING","assetApprovalStatus":"APPROVED"},{"assetText":"SearchAds Description Text 3","assetId":12443453168,"assetPerformanceLabel":"PENDING","assetApprovalStatus":"APPROVED"},{"assetText":"SearchAds Description Text 4","assetId":12443419160,"assetPerformanceLabel":"PENDING","assetApprovalStatus":"APPROVED"}] |
Desired Outcome:
CreativeId | ResponsiveSearchAdDescriptions_assetText | ResponsiveSearchAdDescriptions_assetId | ResponsiveSearchAdDescriptions_pinnedField | ResponsiveSearchAdDescriptions_assetPerformanceLabel | ResponsiveSearchAdDescriptions_assetApprovalStatus |
---|---|---|---|---|---|
487067671679 | SearchAds Description Text 1 | 12443453594 | DESCRIPTION_1 | PENDING | APPROVED |
487067671679 | SearchAds Description Text 2 | 12443453165 | --- | PENDING | APPROVED |
487067671679 | SearchAds Description Text 3 | 12443453168 | --- | PENDING | APPROVED |
487067671679 | SearchAds Description Text 4 | 12443419160 | --- | PENDING | APPROVED |
This is the query that got me the closest but is still showing JSON.
SELECT CreativeId, JSON_QUERY_ARRAY(ResponsiveSearchAdDescriptions) AS Ads FROM `priXXXXXX.sandbox.Ad_XXXXXXX` WHERE ResponsiveSearchAdDescriptions IS NOT NULL LIMIT 100
The Query should be able to include this condition ResponsiveSearchAdDescriptions IS NOT NULL
Some ideas?
Use below approach
select Creativeld, json_extract_scalar(el, '$.assetText') as assetText, json_extract_scalar(el, '$.assetId') as assetId, json_extract_scalar(el, '$.pinnedField') as pinnedField, json_extract_scalar(el, '$.assetPerformanceLabel') as assetPerformanceLabel, json_extract_scalar(el, '$.assetApprovalStatus') as assetApprovalStatus from `priXXXXXX.sandbox.Ad_XXXXXXX`, unnest(json_extract_array(ResponsiveSearchAdDescriptions, '$')) elif applied to sample data in your question - output is