Given a table that contains a column of JSON like this:
{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}
{"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}
How can I write a Presto query to give me the average b
value across all entries?
So far I think I need to use something like Hive's lateral view explode, whose equivalent is cross join unnest in Presto.
But I'm stuck on how to write the Presto query for cross join unnest
.
How can I use cross join unnest
to expand all array elements and select them?
Here's an example of that
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
defines a common table expression (CTE) named example
with a column aliased as message
VALUES
returns a verbatim table rowset
UNNEST
is taking an array within a column of a single row and returning the elements of the array as multiple rows.
CAST
is changing the JSON
type into an ARRAY
type that is required for UNNEST
. It could easily have been an ARRAY<MAP<
but I find ARRAY(ROW(
nicer as you can specify column names, and use dot notation in the select clause.
JSON_EXTRACT
is using a jsonPath expression to return the array value of the payload
key
avg()
and group by
should be familiar SQL.