Unable 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, skuIdFROM parsed, UNNEST(entries) WITH ORDINALITY t(skuId, ordinal)produces:
ordinal | skuId---------+-------------------------- 1 | 5bc87ae20d298a283c297ca1 2 | 182784738484wefhdchs4848(2 rows)