MySQL 5.7 : le type de donnée JSON

Depuis MySQL 5.7, il devient simple de stocker des données en JSON.
C’est très pratique quand les enregistrements n’ont pas tous les mêmes attribus.

Documentation officielle

Le mieux est de commencer par la doc :
https://dev.mysql.com/doc/refman/5.7/en/json.html

Requêtes JSON en MySQL

Création d’une table avec un champ « donnees » en JSON et ajout de données d’exemple.

CREATE TABLE equipe (id INTEGER NOT NULL AUTO_INCREMENT, prenom VARCHAR(200), couleur VARCHAR(20),  donnees JSON, PRIMARY KEY (id));


INSERT INTO equipe VALUES(NULL, 'julien', 'bleu', '{"twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985, "poste": "tech"}');

INSERT INTO equipe VALUES(NULL, 'thomas', 'bleu', '{"pays": "france", "telephone": "0722222222", "poste": "president"}');

INSERT INTO equipe VALUES(NULL, 'benjamin', 'rouge', '{"pays": "suisse", "telephone": "0833333333", "poste": "tech"}');

Nous remarquons que le champ « donnees » ne renseigne pas forcément les mêmes informations pour l’ensemble des 3 enregistrements : julien a précisé son twitter mais pas son pays par exemple.

SELECT * FROM equipe;


1     julien     bleu     {"poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985}
2     thomas     bleu     {"pays": "france", "poste": "president", "telephone": "0722222222"}
3     benjamin     rouge     {"pays": "suisse", "poste": "tech", "telephone": "0833333333"}

SELECT : Requêtes en lecture sur du JSON

SELECT * FROM equipe WHERE couleur = 'bleu' AND json_extract (donnees,"$.annee_naissance") = 1985;


1     julien     bleu     {"poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985}

UPDATE : Requêtes en mise à jour sur du JSON

UPDATE equipe SET donnees = JSON_SET(donnees, "$.pays", "fra") WHERE id = 1;

Vérifions le résultat pour julien :

SELECT * FROM equipe WHERE json_extract (donnees,"$.pays") LIKE '%fra%';


1     julien     bleu     {"pays": "fra", "poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985}
2     thomas     bleu     {"pays": "france", "poste": "president", "telephone": "0722222222"}

Complément

Attention au comportement étrange sur le LIKE bordé d’un seul coté :

SELECT * FROM equipe WHERE json_extract (donnees,"$.pays") LIKE 'fra%';


Query OK, 1 row affected (0,00 sec)

Nous devrions avoir les 2 mêmes enregistrements que le résultat précédent. Je n’ai pas encore cherché l’origine de ce comportement.

 

L’utilisation de json_extract est un peu lourde, une notation plus simple existe  :

SELECT * FROM equipe WHERE donnees->"$.pays" = 'france';

Cette notation bien plus agréable a cependant quelques limitations à voir ici : http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/