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?

Tag:google-ads-api, sql, google-bigquery, multidimensional-array, nested-lists

Only one comment.

  1. Mikhail Berlyant

    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, '$')) el

    if applied to sample data in your question - output is

    Reply

Add a new comment.