CODEWITHSUNDEEP
google-bigquery
Inam Imthiyaz
Inam Imthiyaz

Reputation: 39

BigQuery JSON Field Extraction

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                 |

Upvotes: 0

Views: 462

Answers (1)

howie
howie

Reputation: 2685

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

Upvotes: 1

Related Questions