从jsonb数组中获取所有值匹配的元素

Question

我想做一些简单的事情，但是经过数小时的尝试，我仍然无法使它工作。我有一个表格，其中包含一个标签列，这是一个jsonb数组，看起来像这样：

{"{\"name\": \"e-commerce\", \"weight\": 400}","{\"name\": \"management\", \"weight\": 200}","{\"name\": \"content management\", \"weight\": 200}"}

我现在想写一个查询，当名称与搜索字符串匹配时，返回完整的对象。到目前为止，我想出了以下内容：

SELECT * FROM data
WHERE tags is not null
  AND EXISTS(
        SELECT FROM data ad WHERE (ad.tags -> 'name' ->> 'education')::text
    );

但是我遇到了这个错误：

[42883] ERROR: operator does not exist: jsonb[] -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

我不确定在哪里进行类型转换以及是否真的是问题所在。

我已经尝试了这些线程中的解决方案，但都没有成功 :-(

- Gh05d

---

这真的是你的JSON列的内容吗？那些转义的双引号看起来不对，似乎该值已被存储为单个标量值，而不是一个正确的JSON对象。 - user330315

我对Postgres中的数组和对象并不是非常熟悉。我的同事创建了这个表，但当我查询它时，返回一个对象数组。 - Gh05d

我刚刚检查了数据库定义，它确实是一个jsonb： tags jsonb[] default'{}'::jsonb[] - Gh05d

jsonb[]几乎没有意义。包含真正的JSON数组的“普通”jsonb是一个更好的选择。 - user330315

Answer 1

如果您想让每个匹配对象在单独的行上显示，您可以使用 jsonb_array_elements() 解析对象数组并进行过滤：

select o.obj
from data d
cross join lateral jsonb_array_elements(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

如果您有JSONB数组（因此数据类型为jsonb），那么这将起作用。

另一方面，如果您拥有一个JSON对象数组（即：jsonb[]），则可以使用 unnest ：

select o.obj
from data d
cross join lateral unnest(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

请注意，当两个对象在同一数组中匹配时，这会生成两行。如果您只想要一行，则可以使用exists代替：

select o.obj
from data d
where exists (
    select 1 from unnest(d.tags) o(obj) where o.obj ->> 'name' = 'education'
)

- GMB

Answer 2

你需要查询 json数组 中的对象。使用 jsonb_array_elements 创建这些对象，然后像这样查询json -

SELECT d.* FROM data d, jsonb_array_elements(d.tags) tag
WHERE tag is not null and  WHERE (tag -> 'name') ? 'education'

"也请注意，在您最初的查询中，这个 -"

"

这个 -

"

WHERE (ad.tags -> 'name' ->> 'education')::text

应该是 -

WHERE (ad.tags -> 'name') ? 'education'

或者

WHERE (ad.tags ->> 'name')::text = 'education'

---

编辑 1:

由于您的数据类型不是jsonb而是jsonb []，因此您需要使用unnest将其展开为jsonb -

SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagObj
WHERE tagObj is not null and  WHERE (tag -> 'name') ? 'education'

或者

select * from (
SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagobj
) a WHERE tag is not null and  WHERE (tag -> 'name') ? 'education'

第一个可能会出错，因为在该上下文中没有可用的tagObj

---

- Nikhil Patil

---

您的回答中第二个WHERE应该被删除。不幸的是，我遇到了以下错误： [42883] ERROR: 函数jsonb_array_elements(jsonb[])不存在 提示：没有与给定名称和参数类型匹配的函数。您可能需要添加显式的类型转换。 位置：32 - Gh05d

@Gh05d，是的，这是因为你的数据类型是jsonb[]而不是jsonb，你需要将其展开成jsonb。我已经更新了答案。 - Nikhil Patil