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

그러나, 이것은 전체 테이블 스캔을 수행하고, 매우 빠르게 없습니다. 나는 함수 names_as_array (예술가)를 사용하여 GIN 인덱스를 생성했는데, '아티스트 이름'= ANY names_as_array (예술가)를 사용하지만 인덱스는 사용되지 않으며 쿼리가 상당히 느린 사실이다.

해결법

1. ==============================

   1.새로운 바이너리 JSON 데이터 형식 jsonb으로, 포스트 그레스 9.4은 주로 인덱스 옵션을 개선 소개했다. 이제 직접 jsonb 배열에 GIN 인덱스를 가질 수 있습니다 :

   새로운 바이너리 JSON 데이터 형식 jsonb으로, 포스트 그레스 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"}]';
   

   @> 새로운 jsonb되는 것은 GIN 인덱스를 사용하여 조작자를 '포함'. (형태가 아닌 JSON에 대한 만 jsonb!)

   아니면 인덱스에 대한보다 전문, 기본이 아닌 GIN 연산자 클래스 jsonb_path_ops을 사용합니다 :

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

   같은 쿼리.

   현재는 jsonb_path_ops에서 @> 연산자를 지원합니다. 그러나 일반적으로 훨씬 더 작고 더 빠른입니다. 매뉴얼에 더 많은 인덱스 옵션 사항이 있습니다.

   단지 텍스트 프리미티브로 값과 중복 키가 열 이름에있을 수 있습니다 : 예에 표시되는 아티스트 이름 만 보유하면,로 시작하는 덜 중복 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;
   

   이것은 불변의 기능을 작동합니다 :

   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 인덱스를 지원하기 위해 배열 연산자를 사용합니다. 이 경우에는 오퍼레이터 @ < "에 포함된다."

   당신은 json_array_elements ()가 아니었다되지 않은 경우에도 함수의 불변 선언 할 수 있습니다. 대부분의 JSON 기능은 안정 불변하지로 사용됩니다. 그 변화에 해커 목록에 대한 논의가 있었다. 대부분은 지금은 불변입니다. 확인 :

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

   기능 인덱스는 불변적인 기능과 함께 작동합니다.

from https://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array by cc-by-sa and MIT license