It so happens I have a stringified array in a field in BigQuery
'["a","b","c"]'
and I want to convert it to an array that BigQuery understands. I want to be able to do this in standard SQL:
with k as (select '["a","b","c"]' as x)
select unnested_x from k, unnest(x) unnested_x
I have tried JSON_EXTRACT('["a","b","c"]','$') and everything else I could find online.
Any ideas?
Recently (2020) the JSON_EXTRACT_ARRAY function was added to the bigquery standard sql.
It makes it easy to get the expected behavior with no UDF or tricks
with k as (select JSON_EXTRACT_ARRAY('["a","b","c"]', '$') as x)
select unnested_x from k, unnest(x) unnested_x
Will result in:
╔══════════════╗
║ "unnested_x" ║
╠══════════════╣
║ "a" ║
║ "b" ║
║ "c" ║
╚══════════════╝
Below is for BigQuery Standard SQL
#standardSQL
WITH k AS (
SELECT 1 AS id, '["a","b","c"]' AS x UNION ALL
SELECT 2, '["x","y"]'
)
SELECT
id,
ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(x, 2 , LENGTH(x) - 2)))) AS x
FROM k
It transforms your string column into array column
This solution updates @northtree's answer, and more elegantly handles returning the members of the array as stringified JSON objects as opposed to returning [object Object] strings:
CREATE TEMP FUNCTION
JSON_EXTRACT_ARRAY(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return JSON.parse(input).map(x => JSON.stringify(x));
""";
with
raw as (
select
1 as id,
'[{"a": 5, "b": 6}, {"a": 7}, 456]' as body
)
select
id,
entry,
json_extract(entry, '$'),
json_extract(entry, '$.a'),
json_extract(entry, '$.b')
from
raw,
unnest(json_extract_array(body)) as entry
I want to offer an alternative. As the array is a string, simply extract the value using regexp_extract_all:
REGEXP_EXTRACT_ALL(your_string, r'[0-9a-zA-Z][^"]+') as arr
You may find the regex too restrictive to start with an alphanumeric; you can just tweak it to your liking.
It would be much easier via JS UDF.
CREATE TEMP FUNCTION
JSON_EXTRACT_ARRAY(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return JSON.parse(input);
""";
WITH
k AS (
SELECT
'["a","b","c"]' AS x)
SELECT
JSON_EXTRACT_ARRAY(x) AS x
FROM
k
