无法在 Presto Athena 中将 varchar 转换为数组 [英] Unable to convert varchar to array in Presto Athena
问题描述
我的数据是 varchar 格式.我想拆分这个数组的两个元素,以便我可以从 json 中提取一个键值.
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"
},
]
例如我想从上列中提取 skuid.所以我提取后的数据应该是这样的:
For e.g. I want to extract skuid from the above column. So my data after extraction should look like:
1 5bc87ae20d298a283c297ca1
2 182784738484wefhdchs4848
强制转换为数组不起作用例如 select cast(col as array) 给出以下错误:未知类型:数组
Cast to array doesn't work e.g select cast(col as array) gives the following error: Unknown type: array
所以我无法取消嵌套数组.
So I am not able to unnest the array.
我如何在 Athena 中解决这个问题?
How do I do solve this problem in Athena?
推荐答案
你可以组合使用 将值解析为 JSON,将其转换为结构化 SQL 类型(数组/地图/行),以及 UNNEST WITH ORDINALITY 从数组中提取元素作为单独的行.请注意,这仅在 JSON 有效负载中的数组元素没有尾随逗号时才有效.您的示例有一个,但已从下面的示例中删除.
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)
产生:
ordinal | skuId
---------+--------------------------
1 | 5bc87ae20d298a283c297ca1
2 | 182784738484wefhdchs4848
(2 rows)
这篇关于无法在 Presto Athena 中将 varchar 转换为数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!