Wie kann man JSONB-Arrays in PostgreSQL effizient indizieren und abfragen?

Richtiger Index zum Abfragen von Strukturen in Arrays in Postgres JSONB

Um Arrays strukturierter Daten, die in einem Postgres-JSONB-Feld gespeichert sind, effektiv zu indizieren und abzufragen, Beachten Sie die folgenden Schritte:

1. Korrekter Array-Zugriff:

Verwenden Sie die richtige Syntax, um auf Array-Elemente in einem JSONB-Kontext zuzugreifen. Zum Beispiel:

e->0->>'event_slug'

2. Benutzerdefinierte Operatoren für JSONB:

Verwenden Sie JSONPATH-Operatoren für Größer-als- oder Kleiner-als-Vergleiche in JSONB-Feldern. Verwenden Sie in Postgres 12 oder höher @?:

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

Oder verwenden Sie in älteren Versionen jsonb_path_ops:

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

3. Grundlegende Lösung für Postgres 12:

Verwenden Sie für Gleichheitsprüfungen das @? Operator:

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

Für OR-Filter verwenden Sie die folgende Syntax:

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. Grundlegende Lösung für jede Postgres-Version:

Für Gleichheitsprüfungen verwenden Sie das @> Operator:

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

Für Prüfungen, die größer oder gleich sind, verwenden Sie eine Unterabfrage:

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. Erweiterte Lösung mit materialisierter Ansicht:

Für eine optimale Leistung bei komplexen Abfragen sollten Sie die Erstellung einer materialisierten Ansicht in Betracht ziehen, die relevante Daten in normalisierter Form speichert:

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. Indizieren und Abfragen der materialisierten Ansicht:

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;

Das obige ist der detaillierte Inhalt vonWie kann man JSONB-Arrays in PostgreSQL effizient indizieren und abfragen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!