Picking just a single value from Map of Arrays in SQL

Question

This is SQL query.

Let's assume that you have column having the data type map of arrays

    profiles     map(int, array(int))

so in the table, you see

    profiles     
    {1: [1, 2]}
    {2: [3, 4]}
    {3: [10]}
    {4: []}

Let's assume that you just wanna pick the first value from each (none if empty), so in the above case

    profiles     
    {1: 1}
    {2: 3}
    {3: 10}
    {4: NULL}

and the new data type would be

   profiles  map(int, int)

is there a way to do this?

Answer 1

You can use transform_values (see the map functions documentation for more details):

-- sample data
WITH dataset(profiles) AS (
   values (MAP(ARRAY[1], ARRAY[ARRAY[1, 2]])),
          (MAP(ARRAY[2], ARRAY[ARRAY[3, 4]]))
)

-- query
select transform_values(profiles, (k, v) -> element_at(v, 1))
from dataset;

Output:

 _col0
-------
 {1=1}
 {2=3}