Postgres:检查数组字段是否包含具有特定属性的jsonb值

Question

下面是一个设置示例：

CREATE TABLE users (
id text NOT NULL,
pets jsonb[] NOT NULL
);
INSERT INTO users (id, pets) values ('random-id', array[
'{"name": "Spot", "toys": [{"color": "red"}, {"color": "blue"}]}',
'{"name": "Zeus", "toys": [{"color": "purple"}, {"color": "red"}]}'
]::jsonb[]);

查询示例：如何查找至少有一只宠物和至少一个红色玩具的所有用户的ID？

Answer 1

模式…有问题。实际上应该使用jsonb而不是jsonb[]，因为json支持开箱即用的数组。

无论如何，如果模式是原样的，那么您可以使用这样的东西：

select distinct tmp2.id
from (
select
tmp1.id,
jsonb_array_elements(tmp1.pets->'toys') as toys
from (
select
id,
unnest(pets) as pets
from users
) tmp1
) tmp2
where jsonb_extract_path_text(tmp2.toys, 'color') = 'red'

(通过使用with而不是嵌套选择，它可能会以更可读的方式编写(

解释：

unnest将把一个内部数组变成单独的行。

jsonb_array_elements做同样的事情，只是它在jsonb数组上操作(我们还必须打开内部toys数组(。

jsonb_extract_path_text检索存储在密钥下的内部文本。

请注意，查询采用您所拥有的特定格式。我没有针对其他json变体测试该查询。