检查BigQuery中是否存在JSON key
检查BigQuery中是否存在JSON key
提问于 2021-03-31 19:27:31
我正在尝试检查键是否存在于JSON中，如果存在，则返回整个JSON。
我有一个如下的JSON：
json
--------
"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]
"fruit":{"apples":"1","oranges":"15"}
"fruit":{"apples":"5","oranges":"1"}
"fruit":[{"lettuce":"7","kale": "8"}] 输出:如果JSON数组或JSON对象有键apples，则返回键值
json |Output
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}] |[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]
"fruit":{"apples":"1","oranges":"15"} |{"apples":"1","oranges":"15"}
"fruit":{"apples":"5","oranges":"1"} |{"apples":"5","oranges":"1"}
"fruit":[{"lettuce":"7","kale": "8"}] | nullStack Overflow用户
发布于 2021-03-31 20:00:38
只需使用if即可
with mytable as (
select '{"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]}' as json union all
select '{"fruit":{"apples":"1","oranges":"15"}}' union all
select '{"fruit":{"apples":"5","oranges":"1"}}' union all
select '{"fruit":[{"lettuce":"7","kale": "8"}]}'
)
select if(json like '%apples%', json_extract(json, '$.fruit'), null) as output
from mytable
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66886950
复制相关文章
相似问题
腾讯云开发者
