Как выполнить перекрестное соединение, чтобы вставить массив JSON в Presto
Учитывая таблицу, которая содержит столбец JSON, как это:
{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}
{"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}
Как я могу написать запрос Presto, чтобы получить среднее значение b значение для всех записей?
До сих пор я думаю, что мне нужно использовать что-то вроде разнесения бокового обзора Хайва, что эквивалентно перекрестному соединению в Presto.
Но я застрял на том, как написать запрос Presto для cross join unnest,
Как я могу использовать cross join unnest развернуть все элементы массива и выделить их?
3 ответа
Вот пример этого
with example(message) as (
VALUES
(json '{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}'),
(json '{"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}')
)
SELECT
n.type,
avg(n.value)
FROM example
CROSS JOIN
UNNEST(
CAST(
JSON_EXTRACT(message,'$.payload')
as ARRAY(ROW(type VARCHAR, value INTEGER))
)
) as x(n)
WHERE n.type = 'b'
GROUP BY n.type
with определяет имена общих табличных выражений (CTE) example с колонкой под псевдонимом message
VALUES возвращает набор строк из дословной таблицы
UNNEST принимает массив в столбце одной строки и возвращает элементы массива в виде нескольких строк.
CAST меняет JSON введите в ARRAY тип, который требуется для UNNEST, Это могло легко быть ARRAY<MAP< но я нахожу ARRAY(ROW( приятнее, так как вы можете указать имена столбцов и использовать точечную запись в предложении select.
JSON_EXTRACT использует выражение jsonPath для возврата значения массива payload ключ
avg() а также group by должен быть знаком SQL.
Как вы указали, это было наконец реализовано в Presto 0.79.:)
Вот пример синтаксиса для приведения отсюда:
select cast(cast ('[1,2,3]' as json) as array<bigint>);
Особый совет: в Presto нет типа 'string', как в Hive. Это означает, что если ваш массив содержит строки, убедитесь, что вы используете тип 'varchar', в противном случае вы получите сообщение об ошибке, в котором говорится, что 'массив типов не существует', что может ввести в заблуждение.
select cast(cast ('["1","2","3"]' as json) as array<varchar>);
Проблема заключалась в том, что я работал на старой версии Presto.
unnest был добавлен в версии 0.79