检查Postgres JSON数组是否包含一个字符串。

Question

检查Postgres JSON数组是否包含一个字符串。

257

我有一张表格用于存储关于我的兔子的信息。它看起来像这样：

create table rabbits (rabbit_id bigserial primary key, info json not null);
insert into rabbits (info) values
  ('{"name":"Henry", "food":["lettuce","carrots"]}'),
  ('{"name":"Herald","food":["carrots","zucchini"]}'),
  ('{"name":"Helen", "food":["lettuce","cheese"]}');

我该如何找到喜欢胡萝卜的兔子？我想到了这个方法：

select info->>'name' from rabbits where exists (
  select 1 from json_array_elements(info->'food') as food
  where food::text = '"carrots"'
);

我不喜欢那个查询。它很混乱。

作为一名全职养兔人，我没有时间更改我的数据库模式。我只想正确地喂养我的兔子。有没有更易读的方法来执行那个查询？

- Snowball

1

有趣的问题。我已经尝试过了，但是突然意识到，我不确定你所说的“更好”是什么意思。你根据什么标准来评判你的答案呢？可读性？效率？还是其他方面？ - David S

@DavidS：（我更新了问题。）我更喜欢可读性而不是效率。由于我保持模式固定，因此我肯定不希望有比完整表扫描更好的东西。 - Snowball

如何检查包含一系列JSON的JSON数组中是否存在一个JSON？基本上，在这种形式的JSON中：{"a":[{"id":1,mark:23},{"id":2,mark:45}], "b":[{"id":4324,mark:21233},{"id":2131,mark:52123}]}，我想要找出是否有这样的组合可用：{"a":{"id":1,mark:23}, "b":{"id":2131,mark:52123}}。 - Veloxigami

9个回答

64

你可以使用 @> 运算符来完成这个操作，类似这样：

SELECT info->>'name'
FROM rabbits
WHERE info->'food' @> '"carrots"';

- gori

1

当项目为空时，这非常有用。 - Lucio

4

请注意 ' 引号包围着 "carrots" 的部分......如果省略这些引号，即使你检查的是整数，它也会出错。（我花了三个小时来寻找整数，但只有在将数字用 ' 引号包裹起来后才神奇地成功了） - skplunkerin

@skplunkerin 应该将json值用'引号括起来形成字符串，因为在JSONB类型中，所有内容都是字符串。例如，布尔值：'true'，字符串：'"example"'，整数：'123'。 - 1valdis

LINE 3: WHERE ingredients->'ingredientId' @> '"0v3yPeZdo"';``` - Oliver Dixon

"@>" 运算符仅适用于 JSONB 列，并被描述为检查“第一个 JSON 值是否包含第二个？”更多详情请参见 https://www.postgresql.org/docs/current/functions-json.html 的“表 9.46. 附加 jsonb 运算符”。 - AlbinoDrought

29

不是更聪明，而是更简单：

select info->>'name' from rabbits WHERE info->>'food' LIKE '%"carrots"%';

- chrmod

2

如果记录中出现像这样的子字符串"carrots"，并且带有其他上下文信息，例如：{"name":"Henry", "food":["lettuce","foocarrots"]}，该怎么办？ - qdev

1

这个答案的简洁性不仅帮助了我，而且与PostgreSQL文档相一致。但是，我必须删除双引号（'"'）才能使其正常工作。注意：似乎第一个字符需要是通配符（'%'）才能完全使用它们。 ... WHERE info->>'food' LIKE '%carrots%'; - A-Diddy

嗨，有比这个更快的版本吗？即使使用索引，在许多行具有JSON数据的情况下，它似乎仍然很慢。 - Baradé

20

有点小变化，事实上没有什么新东西。真的缺少一个功能...

select info->>'name' from rabbits 
where '"carrots"' = ANY (ARRAY(
    select * from json_array_elements(info->'food'))::text[]);

- makasprzak

16

如果数组位于jsonb列的根部，即列看起来像这样：

food
["lettuce", "carrots"]
["carrots", "zucchini"]

直接在方括号内使用列名即可：

select * from rabbits where (food)::jsonb ? 'carrots';

- lmb

如果我有一个数字数组，这种方法行不通。除了将数字转换为字符串（例如，存储["1"，"2"，"3"]而不是[1,2,3]），我还没有找到其他可行的方法。 - iPhoney

5

为了选择 JSONB 中的特定键，您应该使用 ->。

select * from rabbits where (info->'food')::jsonb ? 'carrots';

- cbenitez

4

如果您想检查完整的JSON而不是一个键，可以直接将jsonb转换为文本进行类型转换。

select * from table_name
where 
column_name::text ilike '%Something%';

- martinkaburu

1

这就是我一直在寻找的东西。非常感谢你。在我的Rail 7应用上运行得很好。 - blyscuit

1

不是更简单，而是更聪明：

select json_path_query(info, '$ ? (@.food[*] == "carrots")') from rabbits

- Sam Hughes

1

这可能会有所帮助。

SELECT a.crops ->> 'contentFile' as contentFile
FROM ( SELECT json_array_elements('[
    {
        "cropId": 23,
        "contentFile": "/menu/wheat"
    },
    {
        "cropId": 25,
        "contentFile": "/menu/rice"
    }
]') as crops ) a
WHERE a.crops ->> 'cropId' = '23';

输出：

/menu/wheat

- M. Hamza Rajput

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Snowball · Accepted Answer

从PostgreSQL 9.4版本开始，您可以使用? 运算符：

select info->>'name' from rabbits where (info->'food')::jsonb ? 'carrots';

如果你使用jsonb类型，甚至可以在"food"键上索引?查询：

alter table rabbits alter info type jsonb using info::jsonb;
create index on rabbits using gin ((info->'food'));
select info->>'name' from rabbits where info->'food' ? 'carrots';

当然，作为一名全职的养兔人，你可能没有时间做那些事情。

更新：下面是在一个包含100万只兔子的数据表格中展示了性能提升的演示，每只兔子喜欢两种食物，其中10%的兔子喜欢胡萝卜：

d=# -- Postgres 9.3 solution
d=# explain analyze select info->>'name' from rabbits where exists (
d(# select 1 from json_array_elements(info->'food') as food
d(#   where food::text = '"carrots"'
d(# );
 Execution time: 3084.927 ms

d=# -- Postgres 9.4+ solution
d=# explain analyze select info->'name' from rabbits where (info->'food')::jsonb ? 'carrots';
 Execution time: 1255.501 ms

d=# alter table rabbits alter info type jsonb using info::jsonb;
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 465.919 ms

d=# create index on rabbits using gin ((info->'food'));
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 256.478 ms