Presto - How to convert a field with map<string,string> to rows

user_11793664 0
Viewed 76

I'm new to Presto SQL and I am stuck to a issue and I really need your help.

I have a table with a field "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]"}

I want to pull all order_id(s) and convert them as rows, the output may look like this:

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

My query:

SELECT Date, Info['ORDER_ID']
FROM test_table

I tried the query above but it can only returns the order_id as an array. Anyone know how to do it? Thank you so much and I appreciate your help!

presto arrays sql
1 Answers

Documentation is you friend. You are interested in json functions and unnest (in the code sample succinct version of syntax is used). In addition to this there is one trick - "[333222, 444555, 777888]" is a double encoded json array, so you need to parse it two times (first one is done by 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);

Output:

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

UPD

To address your question in the 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);
user_25012790
Related