Как преобразовать строковый массив в массив в BigQuery?

Question

Так получилось, у меня есть строковый массив в поле в BigQuery

'["a","b","c"]'

и я хочу преобразовать его в массив, который понимает BigQuery. Я хочу быть в состоянии сделать это в стандартном SQL:

with k as (select '["a","b","c"]' as x)
select x from k, unnest(x) x

я пытался JSON_EXTRACT('["a","b","c"]','$') и все остальное, что я мог найти в Интернете.

Есть идеи?

Answer 1

Ниже для 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

Он превращает ваш строковый столбец в столбец массива

Answer 2

Недавно (2020 г.) JSON_EXTRACT_ARRAY функция была добавлена ​​в стандартный sql.

Это позволяет легко получить ожидаемое поведение без UDF или уловок

with k as (select JSON_EXTRACT_ARRAY('["a","b","c"]', '$') as x)
select unnested_x from k, unnest(x) unnested_x

Результатом будет:

╔══════════════╗
║ "unnested_x" ║
╠══════════════╣
║     "a"      ║
║     "b"      ║
║     "c"      ║
╚══════════════╝

JSON_EXTRACT_ARRAY документ

Answer 3

Это решение обновляет ответ @northtree и более элегантно обрабатывает возврат членов массива в виде строковых объектов JSON, а не возврат [object Object] струны:

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

Answer 4

Я хочу предложить альтернативу. Поскольку массив является строкой, просто извлеките значение, используя regexp_extract_all:

REGEXP_EXTRACT_ALL(your_string, r'[0-9a-zA-Z][^"]+') as arr

Вы можете счесть, что регулярное выражение слишком ограничено, чтобы начинать с буквенно-цифровых символов; Вы можете просто настроить его по своему вкусу.

Answer 5

Было бы намного проще через 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