Presto filter json数组
Presto filter json数组
提问于 2021-04-29 00:33:10
如何在Presto中过滤json数组？
WITH dataset AS (
SELECT * FROM (VALUES
(JSON '{"turnovers": [{"purpose": "Bla Bla", "amount": 3, "tag": "E"},
{"purpose": "Blub", "amount": 3, "tag": "F"}]}'),
(JSON '{"turnovers": [{"purpose": "Palim", "amount": 3, "tag": "E"},
{"purpose": "Palim Palim", "amount": 3, "tag": "E"}]}')
) AS t (snapshot)
)
SELECT
json_extract(snapshot, '$.turnovers')
FROM
dataset我希望只获得成交额与标签E，而不是所有的交易。在此示例中，应排除具有标记F的一个事务。
这有可能吗？
我本希望使用这样的东西，但这不起作用
WITH dataset AS (
SELECT * FROM (VALUES
(JSON '{"turnovers": [{"purpose": "Bla Bla", "amount": 3, "tag": "E"},
{"purpose": "Blub", "amount": 3, "tag": "F"}]}'),
(JSON '{"turnovers": [{"purpose": "Palim", "amount": 3, "tag": "E"},
{"purpose": "Palim Palim", "amount": 3, "tag": "E"}]}')
) AS t (snapshot)
)
SELECT
filter(json_extract(snapshot, '$.turnovers'), x -> json_extract_scalar(x, '$.tag')='E')
FROM
dataset回答 2
Stack Overflow用户
回答已采纳
发布于 2021-04-29 03:18:20
您可以尝试在expression json_extract(snapshot, '$.turnovers[?(@.tag == "E")]')中使用json path，但如果它失败了，就像对我一样-将数据转换为行数组并过滤这些数组：
WITH dataset AS (
SELECT * FROM (VALUES
(JSON '{"turnovers": [{"purpose": "Bla Bla", "amount": 3, "tag": "E"},
{"purpose": "Blub", "amount": 3, "tag": "F"}]}'),
(JSON '{"turnovers": [{"purpose": "Palim", "amount": 3, "tag": "E"},
{"purpose": "Palim Palim", "amount": 3, "tag": "E"}]}')
) AS t (snapshot)
)
SELECT
filter(CAST(json_extract(snapshot, '$.turnovers') as ARRAY(ROW(purpose VARCHAR, amount INTEGER, tag VARCHAR))), x -> x.tag = 'E')
FROM
dataset如果在过滤后需要，您可以选择转换回json。
Stack Overflow用户
发布于 2021-04-29 20:35:06
这对我很有效。ARRAY(MAP(VARCHAR, JSON))非常灵活，并且还允许在json数组中嵌套json。
WITH dataset AS (
SELECT * FROM (VALUES
(JSON '{"turnovers": [{"purpose": "Bla Bla", "amount": 3, "tag": "E"},
{"purpose": "Blub", "amount": 3, "tag": "F"}]}'),
(JSON '{"turnovers": [{"purpose": "Palim", "amount": 3, "tag": "E"},
{"purpose": "Palim Palim", "amount": 3, "tag": "E"}]}')
) AS t (snapshot)
)
SELECT
CAST(filter(CAST(json_extract(snapshot, '$.turnovers') AS ARRAY(MAP(VARCHAR, JSON))), x -> json_format(x['tag']) = '"E"') AS JSON)
FROM
dataset页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67304280
复制相关文章
相似问题
腾讯云开发者
