sql - 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.

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

我已经尝试过来自 these 的解决方案threads , 但无济于事:-(

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'
)