Блог

Главная - Вопросы по программированию - извлечение данных из массива объектов sql BigQuery

извлечение данных из массива объектов sql BigQuery

#sql #parsing #google-bigquery

#sql #синтаксический анализ #google-bigquery

Вопрос:

Мне нужно извлечь пары ключ-значение из второго объекта в массиве. Кроме того, необходимо создать новые столбцы с извлеченными данными. Меня интересует только второй объект, в некоторых массивах есть 3 объекта, в некоторых — 4 и т.д. Данные выглядят следующим образом:

 [{'adUnitCode': ca-pub, 'id': 35, 'name': ca-pub}, {'adUnitCode': hmies, 'id': 49, 'name': HMIES}, {'adUnitCode': moda, 'id': 50, 'name': moda}, {'adUnitCode': nova, 'id': 55, 'name': nova}, {'adUnitCode': listicle, 'id': 11, 'name': listicle}]
[{'adUnitCode': ca-pub, 'id': 35, 'name': ca-pub-73}, {'adUnitCode': hmiuk-jam, 'id': 23, 'name': HM}, {'adUnitCode': recipes, 'id': 26, 'name': recipes}]
[{'adUnitCode': ca-pub, 'id': 35, 'name': ca-pub-733450927}, {'adUnitCode': digital, 'id': 48, 'name': Digital}, {'adUnitCode': movies, 'id': 50, 'name': movies}, {'adUnitCode': cannes-film-festival, 'id': 57, 'name': cannes-film-festival}, {'adUnitCode': article, 'id': 57, 'name': article}]

Желаемый результат:

 adUnitCode           id             name 
hmies                49             HMIES
hmiuk-jam            23             HM
digital              48             Digital

Ответ №1:

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

 #standardSQL
select 
  json_extract_scalar(second_object, "$.adUnitCode") as adUnitCode,
  json_extract_scalar(second_object, "$.id") as id,
  json_extract_scalar(second_object, "$.name") as name
from `project.dataset.table`, unnest(
  [json_extract_array(regexp_replace(mapping, r"(: )([w-] )(,|})", "\1'\2'\3"))[safe_offset(1)]]
) as second_object

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

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

как вы можете видеть, «хитрость» здесь заключается в использовании правильного регулярного выражения в функции regexp_replace. Теперь я включил любые алфавитные символы и - . вы можете добавить больше, если сочтете нужным, в качестве альтернативы вы можете попробовать regexp_replace(mapping, r"(: )([^,}] )", "\1'\2'") , как в примере ниже — так вы охватите потенциально больше случаев без изменений в коде

 #standardSQL
select 
  json_extract_scalar(second_object, "$.adUnitCode") as adUnitCode,
  json_extract_scalar(second_object, "$.id") as id,
  json_extract_scalar(second_object, "$.name") as name
from `project.dataset.table`, unnest(
  [json_extract_array(regexp_replace(mapping, r"(: )([^,}] )", "\1'\2'"))[safe_offset(1)]]
) as second_object

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

1. Вы волшебник sql! Второй подход сработал для моей проблемы. Большое вам спасибо! Принял ответ и проголосовал.

2. рад, что это помогло: o)

3. @MikhailBerlyant спасибо за этот ответ. Как можно было бы сделать это не только для второго объекта, но и для каждого объекта?

4. @Arsik36 — нет способа ответить на это в комментариях. если у вас есть какие-либо вопросы — пожалуйста, опубликуйте их со всеми подробностями, и я буду рад ответить: o) Тем временем, если приведенный выше ответ был полезен для вас — рассмотрите возможность голосования: o)

5. Спасибо, @MikhailBerlyant, я опубликую свой вопрос сейчас, как только закончу над ним работать