fetch key value pairs from array objects in sql BigQuery

Question

I need to parse the column mapping and fetch the key value pairs from the second object in array. I would like to create new columns with the fetched data. The data looks like this:

row                 mapping 
1       [{'adUnitCode': ca, 'id': 35, 'name': ca}, {'adUnitCode': hd, 'id': 11, 'name': HD}]
2       [{'adUnitCode': bb, 'id': 56, 'name': jk}, {'adUnitCode': hm, 'id': 12, 'name': HM}]
3       [{'adUnitCode': gh, 'id': 78, 'name': ff}, {'adUnitCode': hk, 'id': 13, 'name': HK}]

The desired output:

row                 adUnitCode                  id                  name
1                     hd                       11                     HD
2                     hm                       12                     HM
3                     hk                       13                     HK

and just to double check - there is no apostrophe around let's say ca value? (so it is not valid json from bq prospective) — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-10-12T21:35:41

Below is one of the approaches (BigQuery Standard SQL)

#standardSQL
select 
  json_extract_scalar(second_object, "$.adUnitCode") as adUnitCode,
  json_extract_scalar(second_object, "$.id") as id,
  json_extract_scalar(second_object, "$.name") as name
from `project.dataset.table`, unnest(
  [json_extract_array(regexp_replace(mapping, r"(: )(\w+)(,|})", "\\1'\\2'\\3"))[safe_offset(1)]]
) as second_object

if to apply above to sample data from your question - result is

fetch key value pairs from array objects in sql BigQuery

1 Answers