Stocker et manipuler des fichiers JSON directement en MySQL

L’utilisation des fichiers JSON en MySQL est devenue essentielle pour les développeurs et administrateurs de bases de données cherchant à manipuler des données semi-structurées sans utiliser de bases NoSQL comme MongoDB.

Avec l’introduction du type de données JSON à partir de MySQL 5.7, il est désormais possible de stocker et interagir efficacement avec ces fichiers directement depuis une base relationnelle.

Dans cet article, nous allons explorer en détail :

Qu’est ce que le format JSON ?

Le JSON (JavaScript Object Notation) est un format simple qui permet de stocker et d’échanger des données entre différentes applications. Il est très utilisé sur le web, notamment pour envoyer des informations entre un site internet et un serveur. JSON est facile à lire pour les humains et compréhensible par les ordinateurs. Il fonctionne avec un système de paires clé-valeur, où chaque donnée est associée à un nom.

Pour tout comprendre, consultez : Notre guide complet sur le format JSON.

Exemple :

Imaginons que vous avez un site qui affiche des informations sur un utilisateur. Voici comment ses données pourraient être stockées en JSON :

{
  "nom": "Alban",
  "age": 43,
  "ville": "Troyes",
  "hobbies": ["informatique", "musique"]
}

Dans cet exemple :

• « nom » est une clé, et « Alban » est sa valeur.
• « age » est une clé, avec la valeur 43.
• « ville » est une clé, avec la valeur « Troyes ».
• « hobbies » est une clé qui contient une liste (ou tableau) de plusieurs valeurs : « informatique » et « musique ».

JSON est très utilisé pour envoyer et recevoir des informations sur les sites web, par exemple lorsqu’un utilisateur remplit un formulaire ou lorsqu’une application récupère des données depuis une API.

1. Pourquoi stocker du JSON en MySQL ?

L’utilisation de JSON en MySQL offre plusieurs avantages :

• Flexibilité des données : contrairement aux schémas stricts des bases relationnelles, JSON permet de stocker des structures variées et évolutives sans altérer le schéma de la table.
• Optimisation de l’espace : MySQL compresse et indexe les documents JSON de manière optimisée, réduisant l’encombrement mémoire.
• Compatibilité avec les applications modernes : de nombreuses API utilisent JSON comme format d’échange de données, ce qui facilite l’intégration entre MySQL et d’autres systèmes.
• Manipulation native dans SQL : MySQL propose des fonctions dédiées pour extraire, modifier et rechercher des valeurs au sein d’un document JSON stocké.

2. Comment stocker du JSON en MySQL ?

Création d’une table avec une colonne JSON

Depuis MySQL 5.7, le type de colonne JSON permet de stocker directement des documents JSON :

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100),
    informations JSON
);

Dans cet exemple, la colonne informations permettra de stocker des objets JSON contenant divers attributs comme l’âge, l’adresse ou les préférences.

Insérer des données JSON

L’insertion de données JSON peut se faire directement en passant un objet au format JSON :

INSERT INTO utilisateurs (nom, informations) 
VALUES ('Alban', '{"age": 43, "ville": "Troyes", "hobbies": ["informatique", "musique"]}');

Il est aussi possible d’utiliser la fonction JSON_OBJECT() pour générer du JSON en SQL :

INSERT INTO utilisateurs (nom, informations) 
VALUES ('Sophie', JSON_OBJECT('age', 29, 'ville', 'Paris', 'hobbies', JSON_ARRAY('lecture', 'voyages')));

3. Interroger des données JSON en MySQL

Sélectionner un champ JSON entier

Pour récupérer l’ensemble du JSON stocké dans une colonne, il suffit d’effectuer une requête SQL classique :

SELECT informations FROM utilisateurs;

Cela renverra un résultat sous forme de chaîne JSON :

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?

{"age": 43, "ville": "Troyes", "hobbies": ["informatique", "musique"]}

Extraire une valeur spécifique

MySQL propose l’opérateur ->> pour extraire une valeur sous forme de texte et -> pour récupérer un objet JSON :

SELECT informations->>'$.ville' AS ville FROM utilisateurs;

Résultat :

+--------+
| ville  |
+--------+
| Troyes |
| Paris  |
+--------+

Filtrer sur des valeurs JSON

On peut rechercher des utilisateurs habitant dans une ville spécifique en utilisant JSON_EXTRACT() :

SELECT * FROM utilisateurs WHERE JSON_EXTRACT(informations, '$.ville') = 'Troyes';

ou encore plus simplement avec l’opérateur ->> :

SELECT * FROM utilisateurs WHERE informations->>'$.ville' = 'Troyes';

d) Vérifier l’existence d’une clé

Si nous voulons sélectionner les utilisateurs ayant un champ hobbies défini, nous utilisons JSON_CONTAINS_PATH() :

SELECT * FROM utilisateurs WHERE JSON_CONTAINS_PATH(informations, 'one', '$.hobbies');

4. Modifier des données JSON en MySQL

Mettre à jour une valeur JSON

On peut modifier un champ JSON sans réécrire tout l’objet grâce à la fonction JSON_SET() :

UPDATE utilisateurs 
SET informations = JSON_SET(informations, '$.ville', 'Reims') 
WHERE nom = 'Alban';

Ajouter un nouvel élément dans un tableau JSON

Si l’on souhaite ajouter un nouveau hobby, on utilise JSON_ARRAY_APPEND() :

UPDATE utilisateurs 
SET informations = JSON_ARRAY_APPEND(informations, '$.hobbies', 'photographie') 
WHERE nom = 'Alban';

Supprimer un champ JSON

Pour supprimer une clé, on utilise JSON_REMOVE() :

UPDATE utilisateurs 
SET informations = JSON_REMOVE(informations, '$.age') 
WHERE nom = 'Alban';

5. Performances et bonnes pratiques

Même si MySQL optimise le stockage JSON, il est important de respecter quelques bonnes pratiques :

• Indexer des colonnes JSON : MySQL ne permet pas d’indexer directement les champs JSON, mais on peut créer un index sur une valeur extraite :sqlCopierModifierALTER TABLE utilisateurs ADD INDEX idx_ville ((informations->>'$.ville'));
• Utiliser les types natifs quand c’est possible : si une colonne contient toujours une valeur unique (ex : un identifiant), il vaut mieux la stocker en INT plutôt qu’en JSON.
• Éviter de stocker de trop gros documents : même si JSON est pratique, des objets trop volumineux peuvent ralentir les requêtes.

6. Exemple concret d’application

Imaginons une application de gestion d’utilisateurs où les préférences des clients sont stockées en JSON. Un utilisateur peut avoir des préférences de notifications (email, SMS, push) et des thèmes favoris.

a) Création de la table

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100),
    preferences JSON
);

b) Insertion d’un client

INSERT INTO clients (nom, preferences) 
VALUES ('Julien', '{"notifications": ["email", "sms"], "themes": ["dark", "blue"]}');

c) Sélection des clients recevant des notifications par email

SELECT * FROM clients WHERE JSON_CONTAINS(preferences->'$.notifications', '"email"');

Conclusion

Le stockage et la manipulation des fichiers JSON dans MySQL permettent une flexibilité accrue dans la gestion des données semi-structurées. Grâce aux fonctions natives, il est possible d’insérer, interroger et modifier des données JSON de manière efficace, sans compromettre les performances. En combinant les bonnes pratiques et l’optimisation des requêtes, MySQL devient une alternative puissante aux bases NoSQL pour gérer du JSON tout en conservant les avantages d’une base relationnelle.

En intégrant ces techniques dans vos projets, vous pouvez améliorer la gestion des données dynamiques tout en optimisant le stockage et les requêtes.

Alban

Fondateur de l’agence Créa-troyes.
Intervenant en Freelance.
Contactez-moi

crea-troyes.fr