PostgreSQL에서 JSONB 배열을 효율적으로 인덱싱하고 쿼리하는 방법은 무엇입니까?

Postgres JSONB 배열의 구조 쿼리를 위한 적절한 인덱스

Postgres JSONB 필드에 저장된 구조화된 데이터 배열을 효과적으로 인덱스하고 쿼리하려면, 다음 단계를 고려하세요.

1. 올바른 배열 액세스:

JSONB 컨텍스트에서 배열 요소에 액세스하려면 올바른 구문을 사용하세요. 예:

e->0->>'event_slug'

2. JSONB용 사용자 정의 연산자:

JSONB 필드에서 보다 큼 또는 작음 비교를 위해 jsonpath 연산자를 활용합니다. Postgres 12 이상에서는 @?:

e->0->>'end_time' @> '2014-10-13'

을 사용하거나 이전 버전에서는 jsonb_path_ops:

CREATE INDEX events_gin_idx ON locations USING GIN (events jsonb_path_ops);

를 사용합니다. Postgres 12용 기본 솔루션 :

동일성 검사의 경우 @? 연산자:

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1")';

OR 유형 필터의 경우 다음 구문을 사용합니다.

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1") ? (@.start_time.datetime() < "2014-10-13".datetime() || @.end_time.datetime() < "2014-10-13".datetime())';

4. 모든 Postgres 버전에 대한 기본 솔루션:

동일성 검사의 경우 @> 연산자:

SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';

크거나 같음을 확인하려면 하위 쿼리를 사용하세요.

SELECT l.*
FROM locations l
JOIN jsonb_array_elements(l.events) e ON l.events @> '[{"event_slug":"test_1"}]'
WHERE (e->>'end_time')::timestamp >= '2014-10-30 14:04:06'::timestamptz;

5. 구체화된 뷰를 사용한 고급 솔루션:

복잡한 쿼리에서 최적의 성능을 얻으려면 관련 데이터를 정규화된 형식으로 저장하는 구체화된 뷰 생성을 고려하세요.

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

6. 구체화된 뷰 색인화 및 쿼리:

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

위 내용은 PostgreSQL에서 JSONB 배열을 효율적으로 인덱싱하고 쿼리하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!