PrestoのArray, Mapの使い方調査

#arrayを出力

select array['a','b','c','d','e']

出力

List [ "a", "b", "c", "d", "e" ]

#複数のarrayをmapに変換する

select map(array['a', 'b', 'c', 'd', 'e'], array[1, 2, 3, 4, 5]) as map

出力

Map { "a": 1, "b": 2, "c": 3, "d": 4, "e": 5 }

#mapを表形式に変換

select 
  key, 
  value 
from
  (select map(array['a', 'b', 'c', 'd', 'e'], array[1, 2, 3, 4, 5]) as map1) as t1 
cross join unnest(t1.map1) as t(key, value)

出力

key	value
a	1
b	2
c	3
d	4
e	5

#表形式のものをmapに変換

select 
  map(key, value) 
from
  (
  select 
    1, array_agg(key) as key, array_agg(value) as value 
  from 
    (values('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5)) as t(key, value)
  group by 1
  )

出力

Map { "a": 1, "b": 2, "c": 3, "d": 4, "e": 5 }

mapをjsonに変換

単純にcastするだけ

select 
  cast(map(key, value) as json) 
from
  (
  select 
    1, array_agg(key) as key, array_agg(value) as value 
  from 
    (values('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5)) as t(key, value)
  group by 1
  )

出力

{"a":1,"b":2,"c":3,"d":4,"e":5}