Wie gebe ich in PostgreSQL mehrere Spalten als JSON-Array von Objekten zurück?

JSON-Arrays von Objekten aus mehreren PostgreSQL-Spalten generieren

Diese Anleitung zeigt, wie Sie Daten aus einer PostgreSQL-Tabelle als JSON-Array von Objekten abrufen und nach einer bestimmten Spalte gruppieren. Die Beispiele verwenden die Tabelle MyTable als Referenz:

id	value_two	value_three	value_four
1	a	A	AA
2	a	A2	AA2
3	b	A3	AA3
4	a	A4	AA4
5	b	A5	AA5

Das Ziel besteht darin, ein JSON-Array zu erstellen, in dem jedes Objekt value_three und value_four enthält, gruppiert nach value_two. Das gewünschte Ausgabeformat ist:

value_two	value_four
a	[{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
b	[{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

PostgreSQL-Lösungen (versionsspezifisch)

Die optimale Lösung hängt von Ihrer PostgreSQL-Version ab:

PostgreSQL 10 und höher:

Die Nutzung des --Operators für den Schlüsselausschluss vereinfacht die Abfrage:

<code class="language-sql">SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;</code>

Dadurch werden unnötige Spalten (id und val2) vor der Aggregation effizient entfernt.

PostgreSQL 9.4 und höher:

jsonb_build_object() bietet einen kontrollierteren Ansatz:

<code class="language-sql">SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;</code>

Dies gibt explizit die Schlüssel-Wert-Paare für jedes JSON-Objekt an.

PostgreSQL 9.3 und höher:

Es gibt mehrere Optionen, jede mit Kompromissen:

1. to_jsonb((val3, val4)): Dadurch gehen Spaltennamen im resultierenden JSON verloren.

2. (val3, val4)::foo: Dadurch bleiben Spaltennamen erhalten, indem sie in einen benutzerdefinierten Zeilentyp umgewandelt werden (foo). Erfordert die vorherige Erstellung des Typs CREATE TYPE foo AS (val3 text, val4 text);.

3. Unterabfrage-Ansatz: Eine ausführlichere Methode, die eine Unterabfrage verwendet, aber Typumwandlung vermeidet.

<code class="language-sql">SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34  -- Loses column names
FROM tbl
GROUP BY val2;

-- Requires: CREATE TYPE foo AS (val3 text, val4 text);
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34  -- Preserves column names
FROM tbl
GROUP BY val2;

SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34 -- Subquery approach
FROM tbl
GROUP BY val2;</code>

Wählen Sie die Methode, die für Ihre PostgreSQL-Version und den gewünschten Grad an Prägnanz am besten geeignet ist. Für Versionen 10 und höher bietet der --Operator die sauberste und effizienteste Lösung.

Das obige ist der detaillierte Inhalt vonWie gebe ich in PostgreSQL mehrere Spalten als JSON-Array von Objekten zurück?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!