Maison >base de données >tutoriel mysql >Comment renvoyer plusieurs colonnes sous forme de tableau d'objets JSON dans PostgreSQL ?
Ce guide montre comment récupérer des données d'une table PostgreSQL sous forme de tableau d'objets JSON, regroupés par une colonne spécifique. Les exemples utilisent le tableau MyTable
comme référence :
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 |
L'objectif est de créer un tableau JSON où chaque objet contient value_three
et value_four
, regroupés par value_two
. Le format de sortie souhaité est :
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"}] |
La solution optimale dépend de votre version de PostgreSQL :
PostgreSQL 10 et supérieur :
L'exploitation de l'opérateur -
pour l'exclusion de clé simplifie la requête :
<code class="language-sql">SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34 FROM tbl t GROUP BY val2;</code>
Cela supprime efficacement les colonnes inutiles (id
et val2
) avant l'agrégation.
PostgreSQL 9.4 et supérieur :
jsonb_build_object()
offre une approche plus contrôlée :
<code class="language-sql">SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34 FROM tbl GROUP BY val2;</code>
Ceci spécifie explicitement les paires clé-valeur pour chaque objet JSON.
PostgreSQL 9.3 et supérieur :
Plusieurs options existent, chacune avec des compromis :
to_jsonb((val3, val4))
: Cela perd les noms de colonnes dans le JSON résultant.
(val3, val4)::foo
: ceci préserve les noms de colonnes en les convertissant en un type de ligne personnalisé (foo
). Nécessite de créer le type CREATE TYPE foo AS (val3 text, val4 text);
au préalable.
Approche par sous-requête : une méthode plus détaillée utilisant une sous-requête mais évite le transtypage de type.
<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>
Choisissez la méthode la plus adaptée à votre version PostgreSQL et au niveau de concision souhaité. Pour les versions 10 et supérieures, l'opérateur -
propose la solution la plus propre et la plus efficace.
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!