JSON_TABLE

Syntaxe

Paramètres

Description

Cette fonction permet de transformer un document JSON en une table relationnelle. Elle extrait les données du document JSON selon une structure définie, et les expose sous forme de colonnes SQL.

Remarques

• JSON_TABLE facilite l'intégration entre le monde relationnel et les données semi-structurées : Cette fonction permet aux développeurs SQL de manipuler des documents JSON comme s'ils étaient des tables SQL classiques. Cela simplifie grandement l'accès à des données souvent stockées sous forme hiérarchique ou imbriquée dans les applications modernes.
• Elle permet de projeter dynamiquement des éléments JSON sous forme de colonnes SQL : Grâce à la clause COLUMNS, on peut extraire des champs spécifiques du JSON et leur assigner des types SQL comme VARCHAR2, NUMBER, ou DATE. Cela permet une transformation directe et typée de la structure JSON en une table exploitable.
• La notation de chemin JSON (exemple : $.items[*]) est essentielle pour naviguer dans le document : Elle permet de désigner précisément les objets ou tableaux que l'on souhaite transformer en lignes. Cette précision dans le ciblage est indispensable pour éviter des erreurs de projection ou de structure.
• JSON_TABLE permet également de gérer des structures imbriquées grâce à la clause NESTED : Cela permet de gérer des niveaux multiples de profondeur JSON et de produire des relations en cascade ou des jointures simulées directement depuis une seule expression SQL. C'est très utile pour modéliser des données complexes.
• La clause FOR ORDINALITY est précieuse pour conserver l'ordre ou l'index des éléments extraits : Elle génère une colonne contenant la position de chaque élément dans le tableau JSON d'origine, ce qui peut être utile pour les rapports, le tri ou des correspondances d'index.
• En cas d'erreur dans la structure JSON, la clause JSON_table_on_error_clause peut éviter l'échec total : On peut choisir d'ignorer l'erreur (NULL ON ERROR) ou de faire échouer explicitement la requête (ERROR ON ERROR). Cela permet de rendre les extractions plus robustes dans un contexte de données incertaines ou mal formées.
• La clause FORMAT JSON optimise le traitement si la chaîne est déjà un JSON valide : Elle évite une étape de conversion ou de validation, ce qui peut améliorer la performance et la fiabilité, en particulier quand on travaille avec de très gros documents ou des champs CLOB.
• Les colonnes générées peuvent inclure des types SQL précis pour des usages analytiques ou transactionnels : Par exemple, un champ de date JSON peut être directement converti en DATE SQL dans la projection, rendant possible l'utilisation immédiate de fonctions temporelles comme SYSDATE, ADD_MONTHS, ou TRUNC.
• JSON_TABLE fonctionne aussi bien avec des colonnes de tables qu'avec des expressions littérales : Cela offre une flexibilité importante : on peut tester une extraction à partir d'un simple JSON inline ou l'appliquer sur un lot de documents entreposés dans la base.
• Cette fonction rend les documents JSON accessibles aux outils BI ou aux vues matérialisées : Une fois projetées en table, les données JSON deviennent compatibles avec les moteurs analytiques, les rapports SQL, les agrégats et les vues relationnelles, rendant l'architecture des données plus cohérente.
• Elle est particulièrement utile dans les projets de modernisation des bases Oracle vers des architectures hybrides : De nombreuses applications modernes utilisent JSON comme format de communication. JSON_TABLE permet de consommer ces données sans avoir à transformer manuellement les structures ou à recourir à du code PL/SQL lourd.
• Le support de JSON_TABLE s'est enrichi dans Oracle 18c et ultérieur avec des expressions plus complexes : De nouvelles fonctionnalités comme les opérateurs EXISTS, les chemins conditionnels ou l'intégration avec LATERAL et OUTER APPLY permettent de manipuler des documents encore plus complexes avec finesse et contrôle.