How to extract elements from Presto ARRAY(MAP(VARCHAR, VARCHAR))

I have an array of maps and data format is ARRAY(MAP(VARCHAR, VARCHAR)); I'd like to extract "id" and "description" features from this "Item_Details" column:

+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+--+ | Company | Country | Item_Details | | | +===========+=============+============================================================================================================================================================+==+==+ | Apple | US | [{"created":"2019-09-15","product":"apple watch", "amount": "$7,900"},{"created":"2022-09-19","product":"iPhone", "amount": "$78,300"},{"created":"2021-01-13","product":"Macbook Pro", "amount": "$163,980"}] | | | | Google | US | [{"created":"2020-07-15","product":"Nest", "amount": "$78,300"},{"created":"2021-07-15","product":"Google phone", "amount": "$178,900"}] | | | +-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ 

My expected outputs would be:

+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+--+ | Company | Country | Item_Details | | | +===========+=============+============================================================================================================================================================+==+==+ | Apple | US | ["product":["apple watch", "iPhone", "Macbook Pro"], "amount":[ "$7,900", "$78,300","$163,980"] | | | | Google | US | ["product":["Nest", "Google phone"], "amount": "$78,300", "$178,900"] | | | +-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ 

or

+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+--+ | Company | Country | Product | Amount | | | +===========+=============+============================================================================================================================================================+==+==+ | Apple | US | apple watch | $7,900 | | | | Apple | US | iPhone | $78,300 | | | | Apple | US | Macbook Pro | $163,980 | | | ... +-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ 

I tried element_at(Item_Details, 'product') and json_extract_scalar(Item_Details, '$.product') but received error "Unexpected parameters (array(map(varchar,varchar)), varchar(23)) for function element_at. "

Any suggestions is much appreciated! Thank you in advance

1 Answer

For the second one you can unnest the array and access elements of map:

-- sampel data WITH dataset(Company, Country, Item_Details) AS ( values ('Google', 'US', array[ map(array['created', 'product', 'amount'], array['2019-09-15', 'Nest', '$78,300']), map(array['created', 'product', 'amount'], array['2019-09-16', 'Nest1', '$79,300']) ]) ) -- query select Company, Country, m['product'] product, m['amount'] amount from dataset d, unnest(Item_Details) as t(m); 

Output: