Comment récupérer efficacement plusieurs colonnes sous forme de tableau d'objets JSON dans PostgreSQL ?

PostgreSQL : Générer des tableaux JSON d'objets à partir de plusieurs colonnes

Ce guide montre comment regrouper efficacement les lignes d'une table PostgreSQL par une seule colonne et agréger les colonnes restantes dans un tableau d'objets JSON. Considérons l'exemple MyTable :

<code>| 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 |</code>

L'objectif est de produire un résultat comme celui-ci :

<code>| 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"}]</code>

Notez que les méthodes d'agrégation plus simples incluent souvent le id et la colonne de regroupement (value_two) dans les objets JSON, ce que nous voulons éviter.

Voici des solutions optimisées pour différentes versions de PostgreSQL :

PostgreSQL 10 et versions ultérieures :

Exploitez l'opérateur - pour exclure les clés indésirables :

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

PostgreSQL 9.4 et versions ultérieures :

Utilisez jsonb_build_object() pour un contrôle précis des paires clé-valeur :

<code class="language-sql">SELECT value_two, jsonb_agg(jsonb_build_object('value_three', value_three, 'value_four', value_four)) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

PostgreSQL 9.3 et versions ultérieures :

Employez to_jsonb() avec une expression de ligne :

<code class="language-sql">SELECT value_two, jsonb_agg(to_jsonb(row(value_three, value_four))) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

Ces méthodes fournissent des moyens efficaces et propres de générer la sortie JSON souhaitée, en évitant les clés inutiles et en s'adaptant aux différentes versions de PostgreSQL. Choisissez la méthode compatible avec la version de votre base de données pour des performances optimales.

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!