Recherche avec JSon

Par jouvrard, il y a 2 ans

Base de données MySQL

Bonjour,

J'ai un champ contenant des données au format JSON et j'aimerais pouvoir interroger ces données un peu comme si elles étaient dans des champs distincts... Ce que vous vous en doutez, je n'arrive pas à faire. ;)

Voici 2 tables exemples :

Financeurs : ID (int), name (varchar) Formations : ID (int), data (JSon)

Voici un exemple de ce que je peux avoir dans le champ "data" la table "Formations" :

{"formations":{"1":{"formations_id":"64","financings_id":"34","duration":"35h"},"2":{"formations_id":"41","financings_id":"22","duration":"10h"}}},"missions" => "Mes missions et compétences"

Ce que je veux

J'aimerais avoir une requête me ressortant la liste des formations par financeur, en sélectionnant les formations pour le financeurs d'ID "34" par exemple, avec le nom de celui-ci en supplément.

Ce que j'obtiens

Ben... Rien, je n'y arrive pas malgré de nombreuses documentations lues. :)

Par avance, merci !

2 réponses

quenti77, il y a 2 ans

Hello,

Je vois que tu parle de requête, cela veut dire que tu travailles avec une base de données. Je vais donc partir sur le faite que tu utilises MySQL 8 mais bien sur en fonction de ce que tu utilises il faudra adapter. Et si tu n'utilise rien alors mes explications ne te serviront pas mais c'est toujours bon à savoir ^^

Pour faire une requête SQL il faut s'assurer que le champs est bien de type json (ou jsonb). Ensuite tu peux utiliser plusieurs solution.

Soit le faire avec la fonction JSON_EXTRACT comme ceci :

SELECT JSON_EXTRACT(ta_colonne, "$.formations.1.formations_id") as first_formation_id

Ou tu peux passer par la notation -> comme ceci :

SELECT ta_colonne->"$.formations.1.formations_id") as first_formation_id

Du coup tu peux faire une condition where qui te permet de chercher en fonction de ce que tu as dans le json. Par contre avec le json que tu as il faudra faire des manip pour récupérer la liste des formations de façons plus propre.

jouvrard, il y a 2 ans

Bonjour,

Il me semblait avoir placé mon sujet dans le forum "bases de données" et j'ai oublié de préciser ensuite en effet désolé.
Je suis bien sur MySQL 8, tu as tout bon. :)

Quand je teste la seconde possibilité avec la notation "->" j'ai droit au message d'erreur "Invalid JSON path expression, The error is around character position 14.". (J'ai bien enlevé la parenthèse de top à la fin).

J'ai testé en mettant :

SELECT data->"$**.formations_id" as first_formation_id FROM interviews;

Il me ressort bien tous les "formations_id" de mon JSon, mais je souhaiterais bien limiter ça pour le niveau "formations", et dans l'idée au lieu d'obtenir un résultat du type ["64", "41"], avoir ça sur des lignes différentes avec pour chaque ligne le nom de la formation correspondante.

Je continue mes recherches pendant ce temps, je n'attends pas que la réponse arrive toute cuite quand même, mais je débute avec tout ce qui est JSon et MySQL et je galère un peu... Et ça se voit. ;)

Merci en tout cas !

EDIT> Je viens de tester avec cette requête :

SELECT data->"$.formations.*.formations_id" as formation_id FROM interviews

Je ne récupère que les "formations_id" du niveau "formations" donc nickel.
Ce qu'il me manque maintenant c'est de récupérer les résultats sous forme de lignes différentes, comme ce qu'on obtiendrait avec des enregistrements "classiques" on va dire, et donc avec le nom de la formation qui va bien pour chaque.
Ca avance... :D

EDIT 2
La preuve que je cherche... :D
Je viens de trouver un truc qui me paraît pas mal du tout, voici la requête :

SELECT formations.formations_id AS ID FROM interviews, JSON_TABLE(data, "$.formations.*" COLUMNS (formations_id INT PATH '$.formations_id')) formations

Avec ça j'ai un ID par ligne, et je suppose qu'ensuite je vais pouvoir faire les relations facilement avec mes enregistrements de ma table "formation"