检查 Postgres JSON 数组是否包含字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19925641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if a Postgres JSON array contains a string
提问by Snowball
I have a table to store information about my rabbits. It looks like this:
我有一张桌子来存储关于我的兔子的信息。它看起来像这样：
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"]}');
How should I find the rabbits who like carrots? I came up with this:
我该如何找到喜欢胡萝卜的兔子？我想出了这个：
select info->>'name' from rabbits where exists (
select 1 from json_array_elements(info->'food') as food
where food::text = '"carrots"'
);
I don't like that query. It's a mess.
我不喜欢那个查询。一团糟。
As a full-time rabbit-keeper, I don't have time to change my database schema. I just want to properly feed my rabbits. Is there a more readable way to do that query?
作为一名全职养兔者，我没有时间更改我的数据库架构。我只想正确地喂养我的兔子。是否有更易读的方式来执行该查询？
回答by Snowball
As of PostgreSQL 9.4, you can use the ?operator:
从 PostgreSQL 9.4 开始，您可以使用?运算符：
select info->>'name' from rabbits where (info->'food')::jsonb ? 'carrots';
You can even index the ?query on the "food"key if you switch to the jsonbtype instead:
如果切换到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';
Of course, you probably don't have time for that as a full-time rabbit keeper.
当然，作为全职养兔人，您可能没有时间这样做。
Update:Here's a demonstration of the performance improvements on a table of 1,000,000 rabbits where each rabbit likes two foods and 10% of them like carrots:
更新：以下是对 1,000,000 只兔子的表的性能改进的演示，其中每只兔子喜欢两种食物，其中 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
回答by gori
You could use @> operator to do this something like
您可以使用 @> 运算符来执行此操作，例如
SELECT info->>'name'
FROM rabbits
WHERE info->'food' @> '"carrots"';
回答by chrmod
Not smarter but simpler:
不更聪明但更简单：
select info->>'name' from rabbits WHERE info->>'food' LIKE '%"carrots"%';
回答by macias
A small variation but nothing new infact. It's really missing a feature...
一个小的变化，但实际上没什么新意。真的少了一个功能...
select info->>'name' from rabbits
where '"carrots"' = ANY (ARRAY(
select * from json_array_elements(info->'food'))::text[]);
