Postgres从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.

我不确定我应该在哪里打字，也不确定这是否真的是问题所在。

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

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对象数组(so：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'
)

Answer 2

您需要查询json array中的对象。使用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在上下文中不可用

---