Prestodb - Cast value from array(row("name" varchar,"value" varchar)) when name matches a string

Question

in a prestodb table, I have a column with the following structure.

array(row("name" varchar,"value" varchar))

Let's call the column complexcolumn. The data would look like this:

id complexcolumn
0 [{name=accept-charset, value=UTF-8},{name=accept-language, value=it-IT},{name=user-agent, value=Mozilla/5.0},{name=x-forwarded-port, value=8443}]
1 [{name=accept-encoding, value=identity},{name=accept-charset, value=UTF-8},{name=accept-language, value=de-DE},{name=user-agent, value=Chrome/55.0},{name=x-forwarded-port, value=8443}]
2 [{name=accept-encoding, value=identity},{name=accept-charset, value=UTF-8},{name=x-forwarded-port, value=8443}]

A couple of additional notes:

• The struct may not always have the values I'm searching for.
• The order maybe differ from row to row. For example, name=accept-language can be the second items in the array, and in another row can be the thrid.

What I'd like to do is to cast the value of a couple of specific columns. The statement in the CAST only represents the logic of what I intend to do.

SELECT 
      CAST(value for value in complexcolumn if value == 'accept-language' ) as al,
      CAST(value for value in complexcolumn if value == 'user-agent' ) as ue
FROM mytable ...

Is there a way in prestodb to search for a specific structure in an array and cast the value of the structure with the matching name?

I tried to CROSS JOIN UNNEST(complexcolumn) as T(element) but it will create duplicate rows for each struct row in the array. Ideally I'd like having the multiple values in the same row, not in multple rows because it duplicate the data of the other columns.

Answer 1

You need to look for array functions. There is find_first but it was only added in 0.277 version and AFAIK is not present in Trino. More compatible way would be combining element_at with filter. Something along these lines:

select 
   element_at(filter(complexcolumn, r -> r.value = 'accept-language'), 1) al, 
   element_at(filter(complexcolumn, r -> r.value = 'user-agent'), 1) ue 
from ...