在Presto中提取嵌套的嵌套JSON数组 [英] Extract nested nested JSON array in Presto

问题描述

说我有一个看起来像这样的JSON对象：

Say I have a JSON object that looks like this:

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

我试图将ID（a，b，c，d）提取到Presto中的行中。通过查看其他资源，似乎我应该将 ids元素转换为映射，然后转换为数组，并最终嵌套。但是，由于 ids元素嵌套在嵌套元素中，因此我在执行此操作时遇到了一些麻烦。有人有提示吗？

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?

谢谢！

推荐答案

由于嵌套在另一个JSON数组中的JSON数组中的ids 元素，您需要两次 UNNEST ：

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

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)

这篇关于在Presto中提取嵌套的嵌套JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！