Maison >base de données >tutoriel mysql >Comment renvoyer plusieurs colonnes sous forme de tableau d'objets JSON dans PostgreSQL ?

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

Linda Hamilton
Linda Hamiltonoriginal
2025-01-14 21:42:43989parcourir

How to Return Multiple Columns as a JSON Array of Objects in 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 :

<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 :

  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.

<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!

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn