Comment utiliser JSON dans MariaDB ?

Depuis la version 10.2, vous pouvez utiliser JSON dans MariaDB en tant que type de données (data type) pour stocker et traiter directement des données semi-structurées de façon flexible dans des tables relationnelles. Découvrez ici les fonctions et les étapes essentielles.

MariaDB : vue d’ensemble des fonctions JSON

MariaDB vous propose une série de fonctions utiles qui vous permettent de travailler efficacement avec des données JSON directement dans la base de données. Vous n’avez pas besoin de base de données NoSQL supplémentaire. Avec les outils suivants, vous accédez précisément aux informations, modifiez les contenus ou vérifiez les données structurées :

• JSON_VALUE(json_doc, json_path) : cette fonction vous permet d’extraire une valeur précise au sein d’un champ JSON. Vous spécifiez le document JSON et le chemin vers l’élément désiré. MariaDB vous retourne exactement cette valeur. Ainsi, vous évitez de devoir analyser ou parser le document entier.
• JSON_SET(json_doc, path, value) : avec cette fonction, vous modifiez une entrée existante ou ajoutez un nouveau champ directement dans la base de données. Si le chemin spécifié existe, MariaDB remplace la valeur. S’il manque, MariaDB le crée.
• JSON_REMOVE(json_doc, path) : cette fonction MariaDB JSON vous aide à nettoyer un document JSON. Ainsi, si vous souhaitez supprimer un attribut obsolète, il vous suffit d’en indiquer le chemin.
• JSON_CONTAINS(json_doc, value, path) : cette fonction est utile pour les vérifications ou les requêtes de filtrage. Vous pouvez déterminer si une certaine valeur est présente dans le JSON.
• JSON_VALID(json_doc) : avant de traiter du JSON, assurez-vous qu’il est valide. Avec JSON_VALID, MariaDB détecte si le texte JSON est correctement structuré. Cela vous permet d’identifier les entrées incorrectes dès le début.

Guide étape par étape : utiliser JSON dans MariaDB

Dans cette section, nous vous montrons comment stocker, interroger et manipuler des données JSON dans MariaDB.

Étape 1 : créer une table avec un champ JSON

Tout d’abord, créez une table avec une colonne de type LONGTEXT ou JSON. MariaDB stocke JSON en interne comme du texte, mais vérifie automatiquement la syntaxe lorsque vous utilisez des fonctions JSON.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    properties JSON
);

Dans la colonne properties, vous pouvez stocker n’importe quel objet JSON.

Étape 2 : insérer des données JSON

Vous pouvez maintenant insérer des enregistrements avec un JSON valide en utilisant MariaDB INSERT INTO :

INSERT INTO products (name, properties) VALUES (
    'desk',
    '{"color": "brown", "material": "timber", "weight": 12.5}'
);

Important : vous devez formater correctement la chaîne JSON et la mettre entre guillemets. À l’intérieur de la chaîne, les guillemets doubles doivent être précédés d’un backslash (\").

Étape 3 : lire des données d’une colonne JSON

Pour extraire des valeurs spécifiques d’un objet JSON, utilisez JSON_VALUE() :

SELECT name, JSON_VALUE(properties, '$.material') AS material
FROM products;

Le résultat montre le nom du produit ainsi que le matériau stocké dans le champ JSON.

Étape 4 : mettre à jour les données JSON de MariaDB

Pour modifier une valeur à l’intérieur d’un objet JSON dans MariaDB, utilisez JSON_SET() :

UPDATE products
SET properties = JSON_SET(properties, '$.color', 'white')
WHERE name = 'desk';

Vous pouvez également ajouter de nouvelles paires clé-valeur qui n’existaient pas auparavant.

Étape 5 : supprimer un élément de JSON

Avec JSON_REMOVE(), vous supprimez spécifiquement des valeurs d’un objet :

UPDATE products
SET properties = JSON_REMOVE(properties, '$.weight')
WHERE name = 'desk';

Étape 6 : rechercher des valeurs dans JSON

Vous pouvez filtrer les entrées avec un certain contenu JSON dans MariaDB :

SELECT * FROM products
WHERE JSON_VALUE(properties, '$.material') = 'timber';

C’est particulièrement utile pour des filtres complexes, par exemple lors de recherches de produits ou de contenus personnalisés.

Étape 7 : validation et débogage

Si vous voulez vérifier si une colonne contient un JSON valide, JSON_VALID() vous y aide :

SELECT name, JSON_VALID(properties) FROM products;

Une valeur de retour de « 1 » signifie un JSON valide, « 0 » indique des erreurs de syntaxe.

Étape 8 : combiner les colonnes JSON avec des colonnes générées et des index

Étant donné que MariaDB stocke les contenus JSON en interne en tant que texte, les données doivent être analysées à chaque requête. Cela peut entraîner des problèmes de performance, surtout avec de grandes tables ou des accès fréquents à certains champs JSON. Une solution efficace consiste à utiliser des colonnes générées, qui extraient automatiquement certaines valeurs du contenu JSON et les stockent séparément.

Vous pouvez par exemple extraire la valeur de la clé material de la colonne JSON properties et la stocker dans une nouvelle colonne indexable :

ALTER TABLE products
ADD COLUMN material VARCHAR(100) AS (JSON_VALUE(properties, '$.material')) STORED,
ADD INDEX idx_material (material);

Cette commande crée la colonne material, qui adopte la valeur JSON de $.material. Le mot clé STORED garantit que la valeur est stockée dans la base de données et non calculée uniquement à l’exécution. De plus, nous créons l’index (idx_material) pour optimiser les requêtes de recherche précises.

Vous exécutez ensuite des requêtes nettement plus rapides comme celle-ci :

SELECT name FROM products WHERE material = 'timber';

Surtout dans les environnements de production avec de nombreux accès en lecture, cette technique est avantageuse. Elle réduit la charge de calcul lors des requêtes et assure des temps de réponse plus courts même avec de grandes quantités de données.

Pourquoi utiliser JSON en combinaison avec MariaDB ?

L’utilisation de JSON dans MariaDB est particulièrement utile lorsque les applications doivent stocker des données dans une structure variable. Cela peut inclure des configurations, des réponses d’API, des profils utilisateur ou des journaux. JSON conserve des contenus imbriqués et flexibles directement dans une colonne. Vous ne créez pas de colonnes supplémentaires pour chaque champ possible. Au lieu de cela, vous ajoutez de nouvelles valeurs directement dans la structure JSON. Ainsi, vous adaptez vos données sans modifier le schéma de la base de données.

Surtout dans les systèmes qui nécessitent à la fois des structures de table fixes et un stockage de données flexible, JSON dans MariaDB offre une solution rapide et simple. Vous travaillez avec des contenus dynamiques sans renoncer aux avantages des requêtes SQL classiques, et combinez ainsi la stabilité des données relationnelles avec l’adaptabilité du NoSQL.