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

brgchamk  于 2022-11-19  发布在  其他
关注(0)|答案(8)|浏览(274)

我有一个表来存储有关我的兔子的信息。它看起来像这样:

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

我不喜欢这个问题。太乱了。
作为一个全职的兔子饲养员,我没有时间去改变我的数据库模式。我只想正确地喂养我的兔子。有没有一种更可读的方法来完成这个查询?

2eafrhcq

2eafrhcq1#

从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';

当然,作为一个全职的兔子饲养员,你可能没有时间做这些。

**更新:**下面是一个性能改进的演示,在一张有1,000,000只兔子的table上,每只兔子喜欢两种食物,其中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
3b6akqbq

3b6akqbq2#

您可以使用@〉操作符来执行类似于

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

qc6wkl3g3#

不是更聪明,而是更简单:

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

kxxlusnw4#

一个小的变化,但没有什么新的事实。它真的失去了一个功能...

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

5hcedyr05#

如果数组位于jsonb列的根位置,即列如下所示:
| 食物|
| - -|
| [“生菜”、“胡萝卜”]|
| [“胡萝卜”、“西葫芦”]|
只需在括号内直接使用列名:

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

ghg1uchk6#

不是更简单,而是更聪明:

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

x8diyxa77#

这可能会有所帮助。

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
oewdyzsn

oewdyzsn8#

如果你想检查完整的json而不是一个键,你可以直接从jsonb到text进行类型转换。

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

相关问题