How to convert stringified array into array in BigQuery?

user_1389791 0
Viewed 31358

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?

google-bigquery
5 Answers

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"      ║
╚══════════════╝

JSON_EXTRACT_ARRAY doc

user_75815070

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
user_17002700

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.

user_51996320

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
user_4828990
Related