CODEWITHSUNDEEP
sqlarraysjsongoogle-bigquery
emily wilson
emily wilson

Reputation: 35

Extract fields from json string converted from array

I'm using bigquery SQL, I had an array which i turned into a json string using:

TO_JSON_STRING(array)

which gives me a field that looks like this:

[{"Key":"helpId","Value":"abcd1234"},{"Key":"userid","Value":"abc123"},{"Key":"accountid","Value":"ab12"}]

i'm trying to extract the keys using this:

 JSON_EXTRACT(json_string, '$.Key')

but it keeps returning null - any ideas why?

Upvotes: 1

Views: 52

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '[{"Key":"helpId","Value":"abcd1234"},{"Key":"userid","Value":"abc123"},{"Key":"accountid","Value":"ab12"}]' json_string
)
SELECT 
  JSON_EXTRACT_SCALAR(kv, '$.Key') key, 
  JSON_EXTRACT_SCALAR(kv, '$.Value') value 
FROM `project.dataset.table`, 
UNNEST(JSON_EXTRACT_ARRAY(json_string)) kv

with output

Row key         value    
1   helpId      abcd1234     
2   userid      abc123   
3   accountid   ab12

Hope you can easily apply above example to your specific use case

Upvotes: 1

Related Questions