时间戳

首页 > 解决方案 > 在 JSON 中查找某个对象

sql - 在 JSON 中查找某个对象

问题描述

我正在尝试查询嵌套 JSON 结构中是否存在特定列。有没有办法做到这一点？例如，在同一个 JSON 中，我有一些在“Friends”中包含“favoriteNumber”，而有些则没有。

例如，这些是我们遇到的不同情况：

{   "name": "Kyle",
    "favoriteNumber":3
    "isProgrammer":3,
    "hobbies":["weight Lifting","Bowling"],
    "friends":[{
     "name":"Joey",
     "favoriteNumber":100,
     "isProgrammer":false,
     "friends"[...]
}]
}

还有一些“好友”中没有“favoriteNumber”的情况

{   "name": "Travis",
    "favoriteNumber":3
    "isProgrammer":3,
    "hobbies":["Scuba Diving","Bowling"],
    "friends":[{
     "name":"Monica",
     "isProgrammer":false,
     "friends"[...]
}]
}

我想在“朋友”中找到所有具有“favoriteNumber”的 json。所以在上面的例子中，只有第一个应该显示。我怎样才能做到这一点？所以我正在寻找一个特定的对象是否存在。

标签： sqlarraysjsonobjectsnowflake-cloud-data-platform

解决方案

如果您每行有一个 JSON 对象，并且您想知道是否有朋友有喜欢的数字。我们假设名称在数据中是不同的（所以凯尔有三个朋友，其中两个有最喜欢的数字，我们只得到一个，那么这应该有效：

SELECT DISTINCT j.value:name::text as name, j.value as json
FROM data_table t,
LATERAL FLATTEN(input=>t.json) j,
LATERAL FLATTEN(input=>j.value:friends) f
WHERE IS_INTEGER(f.value:favoriteNumber);

好吧，上面的代码将展开该行的 json 中的所有人（如果它是一个数组），因此在您的示例中，顶级 json 不是数组，j可以像这样跳过展平：

SELECT DISTINCT t.json:name::text as name, t.json as json
FROM data_table t,
LATERAL FLATTEN(input=>t.json:friends) f,
WHERE IS_INTEGER(f.value:favoriteNumber);

推荐阅读