Wie können JSON-Arrays in PostgreSQL effizient indiziert werden, um den Elementabruf zu beschleunigen?

Indizieren Sie JSON-Arrays in PostgreSQL effizient für einen schnelleren Elementabruf

Das schnelle Identifizieren und Abrufen von Elementen innerhalb eines JSON-Arrays in einer PostgreSQL-Tabelle kann eine Herausforderung sein. Für eine optimale Leistung ist es wichtig, die Indizierungstechnologie zu verstehen, die für Ihre spezifischen Daten- und Abfrageanforderungen geeignet ist.

Verwendung von JSONB in ​​​​PostgreSQL 9.4

PostgreSQL 9.4 führt den binären JSON-Datentyp jsonb ein, der die Indizierungsmöglichkeiten erheblich erweitert. Verwenden Sie jsonb, um GIN-Indizes direkt auf JSON-Arrays zu erstellen.

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

Dies ermöglicht Abfragen, die GIN-Indizes verwenden können, zum Beispiel:

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

wobei @> den „contains“-Operator von jsonb darstellt.

Alternativ können Sie die GIN-Operatorklasse jsonb_path_ops verwenden (nicht standardmäßig, normalerweise kleiner und schneller):

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

Für eindeutige Namenswerte optimieren

Wenn Ihre artists-Spalte nur ein Array eindeutig benannter Werte enthält, ist es effizienter, die Werte als JSON-Textprimitive statt als Objekte zu speichern. Dadurch entfällt die Notwendigkeit redundanter Schlüssel.

<code class="language-sql">CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);</code>

Abfragebeispiel:

<code class="language-sql">SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;</code>

Umgang mit JSON in PostgreSQL 9.3

Für PostgreSQL-Versionen vor 9.4 können invariante Funktionen und GIN-Indizes verwendet werden.

Funktion erstellen:

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

Dann erstellen Sie den Funktionsindex:

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

Verwenden Sie eine Abfrage, die dem Indexausdruck entspricht:

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

Stellen Sie immer sicher, dass die im Index verwendeten Funktionen unveränderlich sind, um die Funktionsindizierung zu ermöglichen. Hauptbetreiber...

Der Artikel hat den Originaltext neu geschrieben, eine prägnantere Sprache verwendet und einige Sätze angepasst, behält aber den Kerninhalt des Originaltextes und die Position der Bilder bei.

Das obige ist der detaillierte Inhalt vonWie können JSON-Arrays in PostgreSQL effizient indiziert werden, um den Elementabruf zu beschleunigen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!