Wie kann ich in PostgreSQL effizient nach Elementen in JSON-Arrays suchen?

Tipps für die effiziente Suche von JSON-Array-Elementen in PostgreSQL

Um die Suche nach bestimmten Elementen in einem JSON-Array zu optimieren, sollten Sie unbedingt die PostgreSQL-Version und Datenstruktur berücksichtigen.

PostgreSQL 9.4 und JSONB

In PostgreSQL 9.4 und höher bietet der binäre JSON-Datentyp jsonb deutlich verbesserte Indizierungsfunktionen. Sie können einen GIN-Index direkt im jsonb-Array erstellen:

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

Dieser Index ermöglicht effiziente Abfragen mit dem @>-Operator:

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';</code>

Alternativ können Sie die spezielle jsonb_path_ops GIN-Operatorklasse verwenden:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !</code>

Die Abfrageanweisung bleibt unverändert.

PostgreSQL 9.3 und JSON

Erwägen Sie für PostgreSQL 9.3 die Verwendung einer IMMUTABLE-Funktion, um die Elemente eines JSON-Arrays in ein Array von Textwerten zu extrahieren:

<code class="language-sql">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)';</code>

Erstellen Sie dann einen funktionalen GIN-Index für das extrahierte Array:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));</code>

Verwenden Sie in Abfragen Array-Operatoren, um Indexausdrücke abzugleichen:

<code class="language-sql">SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));</code>

Bitte beachten Sie, dass die Funktionsindizierung nur mit IMMUTABLE-Funktionen verwendet werden kann. Überprüfen Sie die Veränderlichkeit der Funktion json_array_elements() und aller anderen verwendeten JSON-Funktionen.

Das obige ist der detaillierte Inhalt vonWie kann ich in PostgreSQL effizient nach Elementen in JSON-Arrays suchen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!