0

I have the following JSON payload stored in a single string column in a BQ table.

{
"customer" : "ABC Ltd",
"custom_fields" : [
     {
      "name" : "DOB",
      "value" : "2000-01-01"
     },
     {
      "name" : "Account_Open_Date",
      "value" : "2019-01-01"
     }
]
}

I am trying to figure out how I can extract the custom_fields name value pairs as columns?

Something like follows.

| Customer.name    | Customer.DOB    | Customer.Account_Open_Date |

| ABC Ltd          | 2000-01-01      | 2019-01-01                 |
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
Inam Imthiyaz
  • 39
  • 3

1 Answers1

1

You can use json-functions , such as

JSON_EXTRACT(json_string_expr, json_path_string_literal)

In your case will be

SELECT 
JSON_EXTRACT(json_text, '$.customer') as Customer.Name,
JSON_EXTRACT(json_text, '$.custom_fields[0].value') as Customer.DOB,
JSON_EXTRACT(json_text, '$.custom_fields[1].value') as Customer.Account_Open_Date
howie
  • 2,587
  • 3
  • 27
  • 43
  • I did look at this before, but i was looking at making this more generic by not hardcoding the column names. Is there anyway we can derive the column name based on the "name" field ? – Inam Imthiyaz Mar 05 '19 at 02:39
  • You may try unset json array unest($.custom_fields[].value) – howie Mar 05 '19 at 02:44
  • https://stackoverflow.com/questions/52120182/bigquery-json-extract-all-elements-from-an-array or you have to write some UDF. – howie Mar 05 '19 at 02:46