L’opération SQL JSON_TABLE sert à extraire les données d’un flux JSON pour les intégrer en base de données.
Pour rappel, les services web REST renvoient la plupart du temps des réponses en JSON.
L’opération fonctionne bien si le premier niveau hiérarchique du flux est un objet JSON, pouvant lui-même contenir un tableau.
Prenons par exemple un flux JSON contenant l’objet « qteage » étant lui-même un tableau :
{
« qteage »: [
{
« agence »: « LY »,
« qte »: 567
},
{
« agence »: « PA »,
« qte »: 879
}
],
« code »: « OK »,
« message »: « »
}
Nous pouvons récupérer les éléments du tableau sans problème par la requête :
select u.* from json_table( ‘{
« qteage »: [
{
« agence »: « LY »,
« qte »: 567
},
{
« agence »: « PA »,
« qte »: 879
}
],
« code »: « OK »,
« message »: « »
}’, ‘strict $.qteage[*]’
COLUMNS( « CodAge » varchar(50) PATH ‘strict $.agence’ ,
« Quantite » decimal PATH ‘strict $.qte’
) ) AS U;
Par contre si le flux JSON renvoie directement un tableau, l’opération JSON_TABLE n’arrive plus à extraire les données :
Prenons par exemple, le flux suivant, qui est reconnu par les outils de validation en ligne :
Lorsque nous essayons de travailler avec ce flux, l’opération JSON_TABLE, ne fonctionne plus :
select u.* from json_table( ‘[
{
« agence »: « LY »,
« qte »: 567
},
{
« agence »: « PA »,
« qte »: 879
}
]’, ‘strict $.[*]’
COLUMNS( « CodAge » varchar(50) PATH ‘strict $.agence’ ,
« Quantite » decimal PATH ‘strict $.qte’
) ) AS U;
L’instruction est bien traitée, mais le résultat est vide :
IBM confirme que l’instruction attend un objet JSON et non un tableau sur le premier niveau hiérarchique.
Si vous êtes confronté à ce problème, vous n’aurez pas d’autre solution que de modifier le JSON reçu, pour ajouter un niveau hiérarchique qui englobera votre flux initial.
Par exemple par l’ajout de l’objet « data ». Votre flux deviendra :
{ « data » :
[
{
« agence »: « LY »,
« qte »: 567
},
{
« agence »: « PA »,
« qte »: 879
}
] }
Vous pouvez le faire, entre autre, par SQL :
SET :donnees = ‘{« data » : ‘ || trim(:donnees) || ‘}’ ;
Attention toutefois à l’encodage de votre résultat, le flux JSON d’origine est encodé en UTF8. Si le CCSID de votre machine est 65535, vous devrez modifier le CCSID de travail pour que l’instruction SQL s’exécute sans erreur, soit au niveau du job, de l’utilisateur, de variables de travail…. Mais là c’est une autre histoire !