0

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
Chique_Code
  • 1,422
  • 3
  • 23
  • 49

1 Answers1

1

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

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Thank you for the solution. I have accepted the answer. What if my array has more than 2 objects? Sometimes it has 4 or 5. It returns all Nulls in this case. How can the query be modified to still grab everything from the 2nd object? The data I need is always in the second object. Thank you in advance. – Chique_Code Oct 13 '20 at 13:52
  • 1
    query in my answer will always pick second object as long as they are formatted same way as in your question's example. If this is not a case - please post new question with respective details, examples and we will be happy to further help :o) – Mikhail Berlyant Oct 13 '20 at 13:58
  • Ok, thank you very much, they are formatted the same way, the only difference is that the array can have different number of objects in it. But it is always a second object I need the data from and the format is consistent throughout the dataset. Somehow when I ran the code I have Nulls in return. Anyway, I posted the new question https://stackoverflow.com/questions/64336882/fetch-the-data-from-array-of-objects-sql-bigquery with the full example of the sample data. – Chique_Code Oct 13 '20 at 14:15