i have table with a column that has two columns: ID (bigint), Vals (map<bigint, float>).

123134    {"0":0.234, "1":-431.4358, "2":58.213, "3":6.124, ... "99":-32.123}

The bigint keys for Vals are just 0, 1, 2, ..., 99

I just need to extract float values from this column using presto SQL per each ID. For instance, in the above example, for the ID: 123134, I just need the following:

    [0.345, -431.4358, 58.213, 6.124, ..., -32.123]

As of now, the only way I could think of doing it is the following:

   select ID, ARRAY[vals[0], vals[1], vals[2], ..., vals[99]] from table

However, this seems super inefficient.

Is there a smart way of doing it?

Any help or reference would be greatly appreciated.

CodePudding user response：

You may use unnest to turn map into rows and map_values to get values as an array:

with a(id, sample) as (
  select 1,
    map(
      array[1,2,3],
      array[0.123, 1.234,23.45]
    )
  union all
  
  select 2,
    map(
      array[6,7],
      array[123.4,567]
    )
)
select
  a.id,
  map_values(a.sample) as vals,
  t.k,
  t.val
from a
  cross join unnest(a.sample) as t(k,val)