Sql Presto 如何将带有 map 的字段转换为行

发布于08月13日

我是Presto SQL的新手，我被一个问题缠住了，我真的需要你的帮助.

我有一个带有"Info"字段的表:

Date                  Info
2021-01-01            {"user_id":"12345", "order_id": "[333222, 444555, 777888]"}
2021-01-02            {"user_id":"67891", "order_id": "[948263]"}
2021-01-03            {"user_id":"93846", "order_id": "[937846, 889213, 886534, 991827]"}
2021-01-04            {"user_id":"63792", "order_id": "[]"}
2021-01-05            {"user_id":"04937", "order_id": "[837462, 837213]"}

我想提取所有的order_id并将它们转换为行，输出可能如下所示:

Date                  order_id
2021-01-01            333222
2021-01-01            444555
2021-01-01            777888
2021-01-02            948263
2021-01-03            937846
...
2021-01-05            837213

我的疑问是:

SELECT Date, Info['ORDER_ID']
FROM test_table

我try 了上面的查询，但它只能以数组形式返回orderid.有人知道怎么做吗？非常感谢您，感谢您的帮助！

推荐答案

文档是你的朋友.您对json functions和unnest感兴趣(在代码样例中使用了简明版本的语法).除此之外，还有一个诀窍--"[333222, 444555, 777888]"是一个双编码的json数组，因此您需要对其进行两次解析(第一次由json_extract_scalar完成):

-- sample data
WITH dataset (Date, Info) AS (
   VALUES ('2021-01-01', '{"user_id":"12345", "order_id": "[333222, 444555, 777888]"}')
)

-- query
select Date, order_id
from dataset,
unnest (cast(json_parse(json_extract_scalar(Info, '$.order_id')) as array(integer))) as t(order_id);

输出:

Date	order_id
2021-01-01	333222
2021-01-01	444555
2021-01-01	777888

UPD个

在 comments 中回答您的问题:

-- sample data
WITH dataset (Date, Info) AS (
   VALUES ('2021-01-01', map(array['user_id', 'order_id'], array['12345', '[333222, 444555, 777888]']))
)

-- query
select Date, order_id
from dataset,
    unnest (cast(json_parse(Info['order_id']) as array(integer))) as t(order_id);