Extract value from Row object

Question

Following the office documentation https://trino.io/docs/current/functions/map.html. I use "map_entries" break map into array(row(K, V)) structure. My question is how to extract K and V value from the row object? Currently I have to recast it to a Row object that I defined.

select 
    type.n , type.b
from
    (select  
         CAST(type AS ROW(n VARCHAR, b VARCHAR)) as type
     from 
         search.yli_tmp_trevi_requests_mini
     cross join 
         unnest (map_entries(cast(json_extract(req, '$.types') as MAP<VARCHAR, VARCHAR>))) t (type)
    ) t
where 
    type.n = 'apple'

Answer 1

What version of Presto are you running? Recent versions of Trino (formerly known as Presto SQL) (0.207+) fixed an issue to make UNNEST return multiple columns when unnesting arrays of rows, as expected by the SQL specification.

This is how you would do it in the current version of Presto:

SELECT t.n, t.b
FROM <table>, UNNEST (map_entries(...)) t(n, b)
WHERE t.n = 'apple'