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

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

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

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)그러나 인덱스는 사용되지 않으며 쿼리는 실제로 상당히 느립니다.

jsonbPostgres 9.4 이상

이진 JSON 데이터 유형은 인덱스 옵션을 크게 개선합니다.이제 GIN 인덱스를 사용할 수 있습니다.jsonb직접 배열:

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"}]';

@> GIN 인덱스를 사용할 수 있는 "포함" 연산자가 됩니다(비포함).json,오직.jsonb!)

또는 인덱스에 대해 보다 전문적인 기본값이 아닌 GIN 연산자 클래스를 사용할 수 있습니다.

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

같은 쿼리입니다.

현재의jsonb_path_ops서포트하고 있는@>교환입니다.하지만 일반적으로 훨씬 작고 빠릅니다.더 많은 인덱스 옵션이 있습니다. 자세한 내용은 매뉴얼을 참조하십시오.

---

컬럼의 경우] 컬럼artists는 예시와 같이 이름만 보유하므로 값만 JSON 텍스트 프리미티브로 저장하는 것이 효율적이며 다중 키는 열 이름이 될 수 있습니다.

JSON 개체와 원시 유형의 차이점에 주의하십시오.

• Postgre의 json 배열에서 인덱스 사용SQL

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;

이름이 매우 중복될 경우 더 효율적입니다.

jsonPostgres 9.3 이상

이것은, 다음의 기능과 함께 동작합니다.

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 인덱스를 지원하려면 어레이 연산자를 사용해야 합니다.
이 경우 "contained by" 연산자는 다음과 같습니다.

기능 변동성에 관한 주의사항

기능을 선언할 수 있습니다.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★★★★★★★★★★★★★★★★★★.

언급URL : https://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array