Wie kann ich bestimmte Elemente in JSON-Arrays in PostgreSQL abfragen?

JSON-Array-Elemente in PostgreSQL abfragen

PostgreSQL 9.3 und höher unterstützt den JSON-Datentyp, sodass JSON-Daten in der Datenbank gespeichert und bearbeitet werden können. Mit dieser Funktion können komplexe Datenstrukturen (z. B. Arrays von Objekten) in einer einzigen Spalte gespeichert werden. Allerdings kann die Abfrage nach bestimmten Elementen oder Werten innerhalb dieser komplexen Strukturen eine Herausforderung darstellen.

Angenommen, die Spalte mit dem Namen „data“ in der Tabelle „reports“ speichert die folgenden JSON-Daten:

<code class="language-json">{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}</code>

Eine häufige Aufgabe besteht darin, alle Berichte abzurufen, die einen bestimmten Wert aus dem Feld „src“ des Arrays „objects“ enthalten. Das Abfragen eines flachen JSON-Felds wie „Hintergrund“ ist einfach, aber das Extrahieren von Elementen in einem Array erfordert einen anderen Ansatz.

Die folgende Abfrage ruft erfolgreich alle Berichte ab, die dem angegebenen „Hintergrund“-Wert entsprechen:

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'background' = 'background.png'</code>

Versuchen Sie jedoch, Werte im Array „objects“ mit einer ähnlichen Syntax abzufragen (zum Beispiel):

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'objects' = 'foo.png'</code>

führt nicht zu den erwarteten Ergebnissen, da es sich bei „Objekten“ um Arrays und nicht um einfache Felder handelt.

Um Elemente in einem JSON-Array erfolgreich abzufragen, können Sie die Funktion json_array_elements() in einem horizontalen Join verwenden. Diese Funktion extrahiert ein einzelnes Element aus einem Array, sodass Sie jedes Element einzeln testen können.

Die folgende Abfrage verwendet einen lateralen Join, um das Array „objects“ zu erweitern und das Vorhandensein eines bestimmten „src“-Werts zu testen:

<code class="language-sql">SELECT data AS data
FROM reports r, json_array_elements(r.data#>'{objects}') obj
WHERE obj->>'src' = 'foo.png';</code>

Alternativ können Sie für eine einzelne Verschachtelungsebene eine vereinfachte Version der Abfrage verwenden:

<code class="language-sql">SELECT *
FROM reports r, json_array_elements(r.data->>'objects') obj
WHERE obj->>'src' = 'foo.png';</code>

Diese Abfragen verwenden die Operatoren #>, -> und ->>, um die JSON-Struktur zu durchlaufen. Einzelheiten zu diesen Operatoren finden Sie in der PostgreSQL-Dokumentation.

Um die Leistung komplexer JSON-Abfragen mit Arrays zu optimieren, sollten Sie darüber hinaus das Hinzufügen eines GIN-Index für den relevanten JSON-Ausdruck in Betracht ziehen. Dies kann insbesondere bei großen Datenmengen die Suchgeschwindigkeit deutlich verbessern.

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