JSON_OBJECTAGG

Syntaxe

Paramètres

Description

Cette fonction permet de regrouper des lignes de données SQL sous forme d'objet JSON, où chaque clef est une valeur issue d'une colonne, et chaque valeur est l'entrée associée.

Remarques

• Agrégation structurée et dynamique : JSON_OBJECTAGG est idéale pour convertir un ensemble de lignes en une structure JSON hiérarchique. Chaque ligne devient une paire clef/valeur dans l'objet final, ce qui permet de transformer un jeu de résultats plat en un format exploitable par des systèmes NoSQL ou des APIs REST. Cela réduit le besoin de traitement côté application.
• Remplace des constructions manuelles complexes : Avant son introduction, l'agrégation de données en JSON nécessitait des boucles PL/SQL ou une concaténation manuelle de chaînes. JSON_OBJECTAGG offre une solution native, beaucoup plus lisible, performante et compatible avec les standards JSON, en intégrant directement cette logique au sein des requêtes SQL.
• Association explicite de clefs et valeurs : Le mot-clef KEY permet d'indiquer clairement que l'expression suivante représente la clef de l'objet JSON. Cette explicitation améliore la lisibilité du code SQL, notamment dans des cas où les expressions sont complexes ou utilisent des alias ou des sous-requêtes.
• Préserve la structure JSON des fragments : Le paramètre FORMAT JSON est crucial lorsque la valeur fournie (VALUE expr) est déjà du JSON valide (par exemple un tableau ou un objet JSON en texte). Ce paramètre empêche que ce contenu soit à nouveau converti ou échappé, assurant l'intégrité et la validité de l'objet JSON final.
• Contrôle du traitement des valeurs NULL : Le paramètre JSON_on_null_clause permet une gestion fine des valeurs NULL. Le mode NULL ON NULL inclut explicitement les clés avec des valeurs nulles, alors que ABSENT ON NULL les exclut. Cela permet d'adapter la sortie JSON à des exigences fonctionnelles précises, notamment en API ou en échange de données.
• Définition du type de retour : Grâce à JSON_agg_returning_clause, on peut choisir si le résultat doit être retourné sous forme de VARCHAR2, CLOB,... Cela permet de maîtriser la taille de sortie et d'optimiser les performances selon l'utilisation cible (affichage, stockage ou transmission réseau).
• Validation stricte du JSON : Le mot-clef STRICT est utile dans des contextes sensibles où la validité du JSON est cruciale. En l'activant, Oracle lèvera une erreur si la conversion JSON échoue pour une paire clé/valeur, garantissant ainsi que la sortie respecte les règles JSON strictes.
• Prévention des doublons avec WITH UNIQUE KEYS : Lorsque plusieurs lignes ont des clés identiques, la dernière écrase les précédentes par défaut. En utilisant WITH UNIQUE KEYS, Oracle force une erreur si des clés en double sont détectées, ce qui protège l'intégrité du format JSON et évite des comportements inattendus.
• Compatible avec GROUP BY et sous-requêtes : JSON_OBJECTAGG peut être utilisée dans des requêtes groupées (GROUP BY) pour générer un objet JSON par groupe logique (par exemple, un objet JSON par client). Elle fonctionne aussi dans des sous-requêtes corrélées, permettant la génération d'objets JSON imbriqués.
• Limites potentielles sur les tailles de sortie : Lorsque le volume de données agrégé est important, la taille du JSON retourné peut dépasser les limites du type VARCHAR2. Il faut alors utiliser RETURNING CLOB pour éviter les erreurs de dépassement, surtout dans des bases Oracle avec un ensemble de caractères multioctets.
• Support étendu pour les APIs REST ou JSON_TABLE : Le résultat de JSON_OBJECTAGG peut être directement utilisé dans des appels d'API REST via Oracle REST Data Services (ORDS), ou intégré à des requêtes utilisant JSON_TABLE pour extraire dynamiquement des champs. Cela favorise l'interopérabilité avec des systèmes externes.
• Lisibilité et maintenance améliorées : Le style déclaratif de JSON_OBJECTAGG rend le SQL plus expressif et moins sujet à erreur qu'une concaténation manuelle. Cette lisibilité facilite la maintenance, la révision de code et la collaboration entre équipes, notamment dans des environnements d'entrepôts de données ou d'intégration.