Postgres JSON 어레이에 문자열이 포함되어 있는지 확인합니다.

Postgres JSON 어레이에 문자열이 포함되어 있는지 확인합니다.

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

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

나는 그 질문이 마음에 안 든다.지저분하군요.

전임 토끼 사육사로서 데이터베이스 스키마를 변경할 시간이 없습니다.토끼에게 제대로 먹이를 주고 싶을 뿐이에요.그 질문을 할 수 있는 더 읽기 쉬운 방법이 있나요?

Postgre 기준SQL 9.4에서는 다음 연산자를 사용할 수 있습니다.

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

인덱스 할 수도 있습니다.?에 대해 질문하다"food"대신 jsonb 타입으로 전환할 경우 다음과 같이 입력합니다.

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

@> 연산자를 사용하여 다음과 같은 작업을 수행할 수 있습니다.

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[]);

배열이 jsonb 열의 루트에 있는 경우, 열은 다음과 같습니다.

음식.
['마음', '마음']
['애호박']

대괄호 안에 있는 열 이름만 사용합니다.

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

심플하지 않고 스마트하게:

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

이게 도움이 될 거야

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

JSONB에서 특정 키를 선택하려면 -> 를 사용해야 합니다.

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

하나의 키가 아닌 전체 json을 검사하려면 jsonb에서 텍스트로 직접 유형 캐스트를 수행할 수 있습니다.

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

언급URL : https://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string