How to extract values from key/value object array in TRINO and create comma separated array of the values

user_12093577 0
Viewed 54

Working in Trino (aka Presto). I have a table of data that's structured like so:

company_id company_name related_companies
xy12345 Cool Company [{company_id=we23456,type=PARTNER},{company_id=tr45678, type=PARTNER},{company_id=fu76354,type=PARTNER}]

The related_companies field is of type list<struct<company_id: string, type: string>>

How can I structure my query to get the output to result in:

company_id company_name related_companies
xy12345 Cool Company we23456, tr45678, fu76354
trino presto sql
1 Answers

You can leverage array functions - transform and array_join:

-- sample data
with dataset (company_id, company_name, related_companies) as (
    values ('xy12345', 'Cool Company', array[cast(row('we23456','PARTNER') as row(company_id varchar, "type" varchar)), 
                                            cast(row('tr45678','PARTNER') as row(company_id varchar, "type" varchar))])
)

-- query
select company_id, 
   company_name, 
   array_join(transform(related_companies, r -> r.company_id), ', ') related_companies
from dataset;

Output:

company_id company_name related_companies
xy12345 Cool Company we23456, tr45678
user_25012790
Related