Presto unnest json [英] Presto unnest json
问题描述
回答这个问题：如何交叉加入unnest一个json数组presto
我尝试运行提供的示例，但是
我得到并且出错，同时这样做
SQL命令：
从
中选择xn
unnest（cast （json_extract（'{payload'：[{type：b，value：9}，
{type：a，value：8}] }'，'$。payload'）as array< varchar>））as x（n）
我得到的错误：
值无法转换为数组< varchar>
java.lang.RuntimeException：java.lang.NullPointerException：string is null
SELECT JSON_EXTRACT（'{payload'：[{type：b，value：9}，{type：a，value：8 }}}'，'$。payload'）
给出：
[{type：b，value：9}，{type：a，value：8}]
即 ARRAY
您可以将您的查询改为：
SELECT xn
FROM
UNNEST（CAST（JSON_EXTRACT（'{payload'：[ type：b，value：9}，{type：a，value：8}]}'，'$。payload'）AS ARRAY< MAP< VARCHAR ，VARCHAR>>））AS x（n）
follwing this question: how to cross join unnest a json array in presto
I tried to run the example provided but I get and error while doing so
the SQL command:
select x.n
from
unnest(cast(json_extract('{"payload":[{"type":"b","value":"9"},
{"type":"a","value":"8"}]}','$.payload') as array<varchar>)) as x(n)
the error I got:
Value cannot be cast to array<varchar>
java.lang.RuntimeException: java.lang.NullPointerException: string is null
SELECT JSON_EXTRACT('{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}','$.payload')
gives:
[{"type":"b","value":"9"}, {"type":"a","value":"8"}]
which is ARRAY<MAP<VARCHAR,VARCHAR>>.
you can change your query to:
SELECT x.n
FROM
UNNEST (CAST(JSON_EXTRACT('{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}','$.payload') AS ARRAY<MAP<VARCHAR, VARCHAR>>)) AS x(n)
这篇关于Presto unnest json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
