Wie frage ich Elemente in JSON-Arrays in PostgreSQL ab?

Elemente im JSON-Array in PostgreSQL abfragen

Einführung

SQL kann die Speicherung von JSON-Daten effektiv verarbeiten. Dieser Artikel konzentriert sich auf die Abfrage von Arrays, die in JSON-Objekten in PostgreSQL gespeichert sind.

JSON-verschachteltes Array abfragen

Angenommen, eine JSON-Spalte mit dem Namen „data“ enthält ein Array von Objekten. Die Aufgabe besteht darin, alle Datensätze, die einem bestimmten „src“-Wert entsprechen, aus dem „objects“-Array abzurufen.

Abfrage mit Lateral Join und Unnesting

Postgres bietet die Funktion json_array_elements() zum Erweitern von JSON-Arrays. Durch die Kombination mit der seitlichen Verbindung können Sie das gewünschte Ergebnis erzielen:

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

Diese Abfrage erstellt einen lateralen Join für jedes JSON-Array-Element, sodass die Spalte obj jedes Objekt im Array darstellen kann.

Alternativen zu PostgreSQL 9.4

Für PostgreSQL 9.4 und höher können Sie die verbesserte JSON-Verarbeitung nutzen:

<code class="language-sql">SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';</code>

In dieser Abfrage prüft der „@>“-Operator, ob das Array das angegebene Objekt enthält. Denken Sie daran, die Suchbegriffe in eckige Klammern zu setzen, damit sie mit der JSON-Array-Struktur übereinstimmen.

Zusammenfassung

Um ein Array in einem JSON-Typ abzufragen, können Sie json_array_elements() verwenden, um das Array zu erweitern und einen lateralen Join durchzuführen. Für PostgreSQL 9.4 und höher können Sie den erweiterten „@>“-Operator zusammen mit passenden GIN-Indizes für einen effizienten Abgleich verwenden. Mit beiden Methoden können Sie bestimmte Datensätze basierend auf Array-Elementwerten abrufen.

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