在Presto中提取嵌套的嵌套JSON数组

Say I have a JSON object that looks like this:

假设我有一个如下所示的JSON对象:

{"attributes":{"blah":"bleh","transactionlist":[{"ids":["a","b","c","d"]}]}}

I've attempted to extract the ids (a,b,c,d) into rows in Presto. From looking at other resources, it seems I should be casting the "ids" element into a map and then array, and unnest eventually. However, I am having some trouble doing this as the "ids" element is nested within a nested element. Anyone have any tips?

我试图在Presto中将id(a,b,c,d)提取到行中。从查看其他资源来看,似乎我应该将“ids”元素转换为地图然后数组,最终不需要。但是,我在执行此操作时遇到了一些麻烦,因为“ids”元素嵌套在嵌套元素中。有人有任何提示吗?

Thanks!

1 个解决方案

#1

Since the ids element in an JSON array nested in another JSON array, you need to UNNEST twice:

由于JSON数组中的ids元素嵌套在另一个JSON数组中,因此需要两次UNNEST:

presto> SELECT id
     -> FROM (VALUES (JSON '{"attributes":{"blah":"bleh","transactionlist":[{"ids":["a","b","c","d"]}]}}')) t(x)
     -> CROSS JOIN UNNEST (CAST(json_extract(x, '$.attributes.transactionlist') AS ARRAY<JSON>)) u(transaction)
     -> CROSS JOIN UNNEST (CAST(json_extract(transaction, '$.ids') AS ARRAY<varchar>)) z(id)
     -> ;
 id
----
 a
 b
 c
 d
(4 rows)