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

3 を使用します。 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 中国語 Web サイトの他の関連記事を参照してください。