Home > database >  Extract a parts of a JSON object that is part of an Array contained in a BigQuery table
Extract a parts of a JSON object that is part of an Array contained in a BigQuery table

Time:02-16

I want extract the content of what I think is an Array with JSON in BigQuery.

This is Current Table in BigQuery:

CreatitveID ResponsiveSearchAdHeadlines
501 [{ "assetText": "Object 999 Car", "assetId": 883,"assetPerformanceLabel": "PENDING","assetApprovalStatus": "APPROVED" }, { "assetText": "Die Schönheit des Rennsports", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Mehr erfahren", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Form folgt Funktion", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Hier entdecken", "assetId": 8832, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Eine neue Dimension des Stils", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Alle Details auf einen Blick", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "999 Car Probefahrt", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "999 Car Konfigurator", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Angebot anfordern", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Probefahrt vereinbaren", "assetId": 883, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Grandioser Stil", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Einsteigen und losfahren", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Sportlich – rasant – Abenteuer", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Erwarte den Rausch", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }]
501 [{ "assetText": "Object 999 Car", "assetId": 883,"assetPerformanceLabel": "PENDING","assetApprovalStatus": "APPROVED" }, { "assetText": "Die Schönheit des Rennsports", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Mehr erfahren", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Form folgt Funktion", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Hier entdecken", "assetId": 8832, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Eine neue Dimension des Stils", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Alle Details auf einen Blick", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "999 Car Probefahrt", "assetId": 605, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "999 Car Konfigurator", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Angebot anfordern", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Probefahrt vereinbaren", "assetId": 883, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Grandioser Stil", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Einsteigen und losfahren", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Sportlich – rasant – Abenteuer", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }, { "assetText": "Erwarte den Rausch", "assetId": 134, "assetPerformanceLabel": "PENDING", "assetApprovalStatus": "APPROVED" }]

Desired resulting table:

CreatitveID ResponsiveSearchAdHeadlines_assetText_ID ResponsiveSearchAdHeadlines_assetText
501 883 Object 999 Car
501 605 Die Schönheit des Rennsports
501 134 Mehr erfahren
... ... ...

I believe I should use JSON_EXTRACT_SCALAR(), but I don´t really know how the whole query should look like.

Some Ideas?

CodePudding user response:

Consider below approach

select CreatitveID, 
  json_value(Headline, '$.assetId') assetId,
  json_value(Headline, '$.assetText') assetText
from your_table, 
unnest(json_extract_array(ResponsiveSearchAdHeadlines)) Headline    

if applied to sample data in your question - output is

enter image description here

  • Related