Comment renvoyer plusieurs colonnes sous forme de tableau d'objets JSON dans PostgreSQL ?

Génération de tableaux JSON d'objets à partir de plusieurs colonnes 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"}]

Solutions PostgreSQL (spécifiques à la version)

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 :

SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;

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 :

SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;

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 :

1. to_jsonb((val3, val4)) : Cela perd les noms de colonnes dans le JSON résultant.

2. (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.

3. Approche par sous-requête : une méthode plus détaillée utilisant une sous-requête mais évite le transtypage de type.

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;

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!