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?
Advertisement
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 |