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?
>Solution :
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
