如何从Presto ARRAY(MAP(VARCHAR, VARCHAR))中提取元素

Question

我有一个数组的地图和数据格式是array (MAP(VARCHAR, VARCHAR));我想提取&;id&;和";description"从这个"Item_Details"列:

+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+--+
| 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"}]                             |  |  |
+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

我期望的输出是:

+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+--+
| 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"]                             |  |  |
+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

或

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

我尝试了element_at(Item_Details, 'product')和json_extract_scalar(Item_Details, '$.product')，但收到错误"意外参数(数组(map(varchar,varchar))， varchar(23))为函数element_at。">

任何建议都非常感谢!提前谢谢你

Answer 1

对于第二个，您可以unnest数组并访问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);

输出:

<表类>公司国家产品数量tbody><<tr>谷歌我们巢78300谷歌我们Nest179300