[json] Postgres JSON 배열에 문자열이 포함되어 있는지 확인

Program / 글쓴이

내 토끼에 대한 정보를 저장할 테이블이 있습니다. 다음과 같이 보입니다.

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

그 쿼리가 마음에 들지 않습니다. 엉망입니다.

전임 토끼 키퍼로서 저는 데이터베이스 스키마를 변경할 시간이 없습니다. 토끼에게 제대로 먹이고 싶어요. 해당 쿼리를 수행하는 더 읽기 쉬운 방법이 있습니까?

답변

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

물론, 당신은 풀 타임 토끼 사육자로서 그럴 시간이 없을 것입니다.

업데이트 : 다음은 각 토끼가 두 가지 음식을 좋아하고 그중 10 %가 당근을 좋아하는 토끼 1,000,000 마리의 테이블에서 성능 향상을 보여줍니다.

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

답변

@> 연산자를 사용하여 다음과 같이 할 수 있습니다.

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

답변

더 똑똑하지는 않지만 더 간단합니다.

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

답변

작은 변형이지만 새로운 사실은 없습니다. 정말 기능이 없습니다 …

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

답변