Bagaimana untuk Mengindeks dan Meminta Array JSONB dengan Cekap dalam PostgreSQL?

Indeks yang Betul untuk Struktur Pertanyaan dalam Tatasusunan dalam Postgres JSONB

Untuk mengindeks dan menanya tatasusunan data berstruktur yang disimpan dalam medan JSONB Postgres dengan berkesan, pertimbangkan langkah berikut:

1. Akses Tatasusunan yang Betul:

Gunakan sintaks yang betul untuk mengakses elemen tatasusunan dalam konteks JSONB. Contohnya:

e->0->>'event_slug'

2. Operator Tersuai untuk JSONB:

Gunakan operator jsonpath untuk perbandingan yang lebih besar daripada atau kurang daripada pada medan JSONB. Dalam Postgres 12 atau lebih baru, gunakan @?:

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

Atau, dalam versi lama, gunakan jsonb_path_ops:

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

3. Penyelesaian Asas untuk Postgres 12 :

Untuk semakan kesaksamaan, gunakan @? operator:

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

Untuk penapis jenis ATAU, gunakan sintaks berikut:

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. Penyelesaian Asas untuk Mana-mana Versi Postgres:

Untuk semakan kesaksamaan, gunakan @> operator:

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

Untuk semakan yang lebih besar daripada-atau-sama, gunakan subkueri:

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. Penyelesaian Lanjutan Menggunakan Paparan Terwujud:

Untuk prestasi optimum pada pertanyaan kompleks, pertimbangkan untuk mencipta paparan terwujud yang menyimpan data yang berkaitan dalam bentuk ternormal:

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. Indeks dan Tanya Pandangan Terwujud:

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;

Atas ialah kandungan terperinci Bagaimana untuk Mengindeks dan Meminta Array JSONB dengan Cekap dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!