How to unpack array as columns

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?

Answer

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