How to convert varchar to array in Presto Athena
You can use a combination of parsing the value as JSON, casting it to a structured SQL type (array/map/row), and UNNEST WITH ORDINALITY to extract the elements from the array as separate rows. Note that this only works if the array elements in the JSON payload don't have a trailing commas. Your example has one but it is removed from the example below.
WITH data(value) AS (VALUES
'[
{
"skuId": "5bc87ae20d298a283c297ca1",
"unitPrice": 0,
"id": "5bc87ae20d298a283c297ca1",
"quantity": "1"
},
{
"skuId": "182784738484wefhdchs4848",
"unitPrice": 50,
"id": "5bc87ae20d298a283c297ca1",
"quantity": "4"
}
]'
),
parsed(entries) AS (
SELECT cast(json_parse(value) AS array(row(skuId varchar)))
FROM data
)
SELECT ordinal, skuId
FROM parsed, UNNEST(entries) WITH ORDINALITY t(skuId, ordinal)
produces:
ordinal | skuId
---------+--------------------------
1 | 5bc87ae20d298a283c297ca1
2 | 182784738484wefhdchs4848
(2 rows)
Admin
Updated on June 16, 2022Comments
-
Admin almost 2 years
My data is in
VARCHAR
format. I want to split both the elements of this array so that I can then extract a key value from the JSON.Data format
[ { "skuId": "5bc87ae20d298a283c297ca1", "unitPrice": 0, "id": "5bc87ae20d298a283c297ca1", "quantity": "1" }, { "skuId": "182784738484wefhdchs4848", "unitPrice": 50, "id": "5bc87ae20d298a283c297ca1", "quantity": "4" }, ]
For example I want to extract
skuid
from the above column. So my data after extraction should look like:1 5bc87ae20d298a283c297ca1 2 182784738484wefhdchs4848
Cast to array doesn't work either:
SELECT CAST(col AS ARRAY)
gives the following error:
Unknown type: array
So I am not able to un-nest the array.
How do I do solve this problem in Presto Athena?