AWS Athena Parse массив объектов JSON в строки

Работаем с AWS Athena и пытаемся проанализировать данные, найденные в столбце с определенным типом данных массива, чтобы каждый объект JSON в массиве был разбит на отдельную строку. когда я запускаю этот запрос:

SELECT properties_textarray
FROM "sample"."property_detail" 
where propertyid = '5bb0a33f-3ca6-4f9c-9676-0b4d62dbb195'

результатом является массив объектов JSON. :

[{"updatedDate":"2023-11-16T23:23:27.677639+00:00","city":"Portland","customerName":"SMITH ALBERT","state":"OR","updatedBy":"Bill Armstrong "}, 
 {"updatedDate":"2023-11-16T21:11:11.077907+00:00","city":"Seattle","customerName":"JONES JACKIE L","state":"WA","updatedBy":"Bill Armstrong "}, 
 {"updatedDate":"2023-12-29T19:52:01.276227+00:00","city":"Boise","customerName":"JOHNSON BOB","state":"ID","updatedBy":"Brian Smith "}]

Я попробовал CROSS JOIN UNNEST, но не получил результатов, на которые надеюсь, что запрос, который я запускаю для определенного свойства данных, вернет каждый объект JSON в виде отдельной строки. Я планирую использовать эти результаты для дополнительной обработки по мере необходимости.

Какой результат select typeof(properties_textarray)? Какой результат вы получаете?

— 16.05.2024 20:10

Это ответ: массив (varchar)

— 16.05.2024 20:21

sql json amazon-athena presto trino

16.05.2024 19:14

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Свойства JSON не будут извлечены за вас (по крайней мере, насколько мне известно), вы можете извлечь их вручную. Лично я предпочитаю подход приведения к массиву карт, что-то вроде следующих строк (обратите внимание на краткий синтаксис, используемый для развёртывания):

-- sample data
with dataset(properties_textarray) as (
values ('[{"updatedDate":"2023-11-16T23:23:27.677639+00:00","city":"Portland","customerName":"SMITH ALBERT","state":"OR","updatedBy":"Bill Armstrong "},
 {"updatedDate":"2023-11-16T21:11:11.077907+00:00","city":"Seattle","customerName":"JONES JACKIE L","state":"WA","updatedBy":"Bill Armstrong "},
 {"updatedDate":"2023-12-29T19:52:01.276227+00:00","city":"Boise","customerName":"JOHNSON BOB","state":"ID","updatedBy":"Brian Smith "}]')
)

-- query
select element_at(json, 'customerName') customerName,
       element_at(json, 'city') city,
       element_at(json, 'state') state,
       element_at(json, 'updatedBy') updatedBy,
       element_at(json, 'updatedDate') updatedDate
from dataset
, unnest(cast(json_parse(properties_textarray) as array(map(varchar, json)))) as t(json);

Выход:

Имя Клиента город состояние обновлено обновленная дата СМИТ АЛЬБЕРТ Портленд ИЛИ Билл Армстронг 2023-11-16T23:23:27.677639+00:00 ДЖОНС ДЖЕКИ Л. Сиэтл Вашингтон Билл Армстронг 2023-11-16T21:11:11.077907+00:00 ДЖОНСОН БОБ Бойсе ИДЕНТИФИКАТОР Брайан Смит 2023-12-29T19:52:01.276227+00:00

Существуют и другие подобные подходы, например приведение к array(json) и использование json_extract или json-запроса. Также, возможно, будет работать приведение к ROW.

УПД

Вот ответ: array(varchar)

Тогда я бы просто использовал json_extract_scalar/json_extract:

-- sample data
with dataset(properties_textarray) as (
values (array['{"updatedDate":"2023-11-16T23:23:27.677639+00:00","city":"Portland","customerName":"SMITH ALBERT","state":"OR","updatedBy":"Bill Armstrong "}',
 '{"updatedDate":"2023-11-16T21:11:11.077907+00:00","city":"Seattle","customerName":"JONES JACKIE L","state":"WA","updatedBy":"Bill Armstrong "}',
 '{"updatedDate":"2023-12-29T19:52:01.276227+00:00","city":"Boise","customerName":"JOHNSON BOB","state":"ID","updatedBy":"Brian Smith "}'])
)

-- query
select json_extract_scalar(json, '$.customerName') customerName,
       json_extract_scalar(json, '$.city') city,
       json_extract_scalar(json, '$.state') state,
       json_extract_scalar(json, '$.updatedBy') updatedBy,
       json_extract_scalar(json, '$.updatedDate') updatedDate
from dataset
, unnest(properties_textarray) as t(json);

Спасибо за ответ! Два вопроса: если объекты JSON в массиве являются переменными, если они существуют, или элементы внутри объекта JSON, будет ли этот подход карты неудачным, если элемент не существует? Также в примере кода вы можете объяснить «как t(json)»?

— 16.05.2024 20:28

@JeffA "Не будет ли подход к отображению неудачным, если элемент не существует?" - нет, я использую element_at специально по этой причине, он вернет ноль, если ключ отсутствует, но теперь вы должны это сделать, поскольку этот подход не будет работать так, как для array(varchar), его следует немного скорректировать. «Можете ли вы объяснить as t(json)» — это псевдоним «виртуальной таблицы», которую «создает» unnest, вот и все.

— 16.05.2024 20:31

16.05.2024 20:22