[sql] JSON 배열에서 요소를 찾기위한 색인

Program / 글쓴이

다음과 같은 테이블이 있습니다.

CREATE TABLE tracks (id SERIAL, artists JSON);

INSERT INTO tracks (id, artists)
  VALUES (1, '[{"name": "blink-182"}]');

INSERT INTO tracks (id, artists)
  VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

이 질문과 관련이없는 다른 열이 몇 개 있습니다. JSON으로 저장하는 데는 이유가 있습니다.

내가하려는 것은 특정 아티스트 이름 (정확히 일치) 이있는 트랙을 찾는 것입니다 .

이 쿼리를 사용하고 있습니다.

SELECT * FROM tracks
  WHERE 'ARTIST NAME' IN
    (SELECT value->>'name' FROM json_array_elements(artists))

예를 들면

SELECT * FROM tracks
  WHERE 'The Dirty Heads' IN
    (SELECT value->>'name' FROM json_array_elements(artists))

그러나 이것은 전체 테이블 스캔을 수행하며 매우 빠르지 않습니다. 함수를 사용하여 GIN 인덱스를 만들려고 시도 names_as_array(artists)하고 사용 'ARTIST NAME' = ANY names_as_array(artists)했지만 인덱스가 사용되지 않고 쿼리가 실제로 상당히 느립니다.

답변

jsonb Postgres 9.4 이상

새로운 바이너리 JSON 데이터 유형을 사용하여 jsonbPostgres 9.4는 크게 향상된 인덱스 옵션을 도입했습니다 . 이제 jsonb배열에 직접 GIN 인덱스를 가질 수 있습니다 .

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);

배열을 변환하는 함수가 필요하지 않습니다. 이것은 쿼리를 지원합니다.

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';

@>jsonbGIN 색인을 사용할 수 있는 새로운 ‘포함’연산자 입니다. (유형 json이 아니라 만 jsonb!)

또는jsonb_path_ops 인덱스에 대해 더 전문화 된 기본이 아닌 GIN 연산자 클래스 를 사용합니다.

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);

같은 쿼리.

현재 jsonb_path_ops는 @>연산자 만 지원합니다 . 그러나 일반적으로 훨씬 작고 빠릅니다. 매뉴얼에 더 많은 색인 옵션, 세부 사항이 있습니다 .

artists예에 표시된대로 이름 만 보유하는 경우 시작하려면 덜 중복 된 JSON 값을 저장하는 것이 더 효율적입니다. 값만 텍스트 프리미티브 로 저장하고 중복 키 는 열 이름에있을 수 있습니다.

JSON 객체와 기본 유형의 차이점에 유의하세요.

CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks  VALUES (2, '["The Dirty Heads", "Louis Richards"]');

CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);

질문:

SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';

?객체 값 에는 작동하지 않고 키 와 배열 요소 만 작동합니다 .
또는 (이름이 자주 반복되는 경우 더 효율적) :

CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING  gin (artistnames jsonb_path_ops);

질문:

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;

json Postgres 9.3 이상

이것은 IMMUTABLE 함수 와 함께 작동 합니다 .

CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';

이 기능 색인을 만듭니다 .

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

그리고 이와 같은 쿼리를 사용 하십시오. WHERE절의 표현식 은 색인 의 표현식 과 일치해야합니다.

SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));

의견에 대한 피드백으로 업데이트되었습니다. GIN 인덱스를 지원 하려면 배열 연산자 를 사용해야 합니다.
은 연산자 “에 포함되는”<@ 이 경우.

기능 변동성에 대한 참고 사항

IMMUTABLE그렇지 json_array_elements() 않은 경우에도 함수를 선언 할 수 있습니다 .
대부분의 JSON함수는로 사용 STABLE하지 IMMUTABLE. 이를 변경하기 위해 해커 목록에 대한 토론이있었습니다. 대부분은 IMMUTABLE지금입니다. 확인 :

SELECT p.proname, p.provolatile
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'pg_catalog'
AND    p.proname ~~* '%json%';

기능 색인은 IMMUTABLE기능 에서만 작동 합니다.

답변