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?
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
