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.
[
{
"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?
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With