Блог

Главная - Вопросы по программированию - BigQuery: извлечение ключей из объекта json, преобразование json из объекта в массив ключ-значение

BigQuery: извлечение ключей из объекта json, преобразование json из объекта в массив ключ-значение

#arrays #json #google-bigquery

# #массивы #json #google-bigquery

Вопрос:

У меня есть таблица со столбцом, который содержит json-объект, тип значения всегда является строкой.

Мне нужно 2 вида информации:

  • список ключей json
  • преобразование json в массив пар ключ-значение

Это то, что я получил до сих пор, и это работает:

 CREATE TEMP FUNCTION jsonObjectKeys(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";

CREATE TEMP FUNCTION jsonToKeyValueArray(input STRING)
RETURNS Array<Struct<key String, value String>>
LANGUAGE js AS """
  let json = JSON.parse(input);
  return Object.keys(json).map(e => {
    return { "key" : e, "value" : json[e] }
  });
""";

WITH input AS (
  SELECT "{"key1": "value1", "key2": "value2"}" AS json_column
  UNION ALL
  SELECT "{"key1": "value1", "key3": "value3"}" AS json_column
  UNION ALL
  SELECT "{"key5": "value5"}" AS json_column
)

SELECT
  json_column,
  jsonObjectKeys(json_column) AS keys,
  jsonToKeyValueArray(json_column) AS key_value
FROM input

Проблема в том, что FUNCTION это не самое лучшее с точки зрения оптимизации вычислений, поэтому я пытаюсь понять, есть ли способ использовать обычный SQL для достижения этих 2 потребностей (или, по крайней мере, 1 из них), используя только SQL без функций.

Ответ №1:

Ниже приведен стандартный SQL BigQuery

 #standardsql
select
  json_column,
  array(select trim(split(kv, ':')[offset(0)]) from t.kv kv) as keys,
  array(
    select as struct 
      trim(split(kv, ':')[offset(0)]) as key, 
      trim(split(kv, ':')[offset(1)]) as value
    from t.kv kv
  ) as key_value
from input, 
unnest([struct(split(translate(json_column, '{}"', '')) as kv)]) t

Если применить к образцу данных из вашего вопроса — вывод

введите описание изображения здесь

Комментарии:

1. Да, это работает очень хорошо, спасибо — я удивлен, что в BQ для этого нет служебной функции

2. Может ли быть так, что эта функция завершается с ошибкой, если ключи / значения содержат двоеточия и / или если значения представляют собой массивы строк (т. Е. Содержат " Символ)?

3. конечно, если у вас есть такой крайний случай и вам нужна помощь — отправьте свой вопрос, и мы поможем

4. @MikhailBerlyant Я пытаюсь расшифровать это, но мне трудно. В строке array(select trim(split(kv, ':')[offset(0)]) from t.kv kv) as keys , что означает t.kv kv? является ли t = dataset, kv = column?

5. есть ли другой способ выполнить UNNEST операцию? это поддерживается материализованными представлениями BQ

Метки: ,