How to unpack array as columns

user_18145842 0
Viewed 166

I have a table that looks like this:

date volume_info
2022-01-01 {"temple": 18348, "benny": 8524, "polly": 1698, "sally": 5860}
2022-01-02 {"temple": 2000, "benny": 1000, "polly": 3904, "sally": 1776, "benjamin": 2}

And I am trying to set it up like this:

date temple benny polly sally benjamin
2022-01-01 18348 8524 1698 5860 NULL
2022-01-02 2000 1000 3904 1776 2

But I don't know how many items are in the map (the first table is updated once a day) so I would like to unpack the map and have them be set as columns without knowing ahead of time how many items are in the map.

Is it possible to "pivot" these values without knowing ahead of time what the keys in the map are?

presto sql
1 Answers

AFAIK presto does not support dynamic pivoting. Also if the number of "records" is unbounded I would argue it does not make much sense to turn them into unknown number of columns. Though you can turn your data into "traditional" 3 columns like date, name, value:

-- sample data
WITH dataset (date, volume_info) AS (
    VALUES   
('2022-01-01',  '{"temple": 18348, "benny": 8524, "polly": 1698, "sally": 5860}'),
('2022-01-02',  '{"temple": 2000, "benny": 1000, "polly": 3904, "sally": 1776, "benjamin": 2}')
) 

--query
select date, name, value
from(
 select date, cast(json_parse(volume_info) as map(varchar, integer)) m
 from dataset
)
cross join unnest(map_keys(m), map_values(m)) as t(name, value)

Output:

date name value
2022-01-01 temple 18348
2022-01-01 benny 8524
2022-01-01 polly 1698
2022-01-01 sally 5860
2022-01-02 temple 2000
2022-01-02 benny 1000
2022-01-02 polly 3904
2022-01-02 sally 1776
2022-01-02 benjamin 2
user_25012790
Related