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 di­rec­te­ment des données semi-struc­tu­rées de façon flexible dans des tables re­la­tion­nelles. Découvrez ici les fonctions et les étapes es­sen­tielles.

MariaDB : vue d’ensemble des fonctions JSON

MariaDB vous propose une série de fonctions utiles qui vous per­met­tent de tra­vail­ler ef­fi­ca­ce­ment avec des données JSON di­rec­te­ment dans la base de données. Vous n’avez pas besoin de base de données NoSQL sup­plé­men­taire. Avec les outils suivants, vous accédez pré­ci­sé­ment aux in­for­ma­tions, modifiez les contenus ou vérifiez les données struc­tu­ré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 exac­te­ment 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 di­rec­te­ment 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é­ri­fi­ca­tions ou les requêtes de filtrage. Vous pouvez dé­ter­mi­ner 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 cor­rec­te­ment structuré. Cela vous permet d’iden­ti­fier les entrées in­cor­rectes dès le début.

Guide étape par étape : utiliser JSON dans MariaDB

Dans cette section, nous vous montrons comment stocker, in­ter­ro­ger 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 au­to­ma­ti­que­ment 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 main­te­nant insérer des en­re­gis­tre­ments 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 cor­rec­te­ment la chaîne JSON et la mettre entre guil­le­mets. À l’intérieur de la chaîne, les guil­le­mets 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é­ci­fiques 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’exis­taient pas au­pa­ra­vant.

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

Avec JSON_REMOVE(), vous supprimez spé­ci­fi­que­ment des valeurs d’un objet :

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

Étape 6 : re­cher­cher 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 par­ti­cu­liè­re­ment utile pour des filtres complexes, par exemple lors de re­cherches de produits ou de contenus per­son­na­li­sés.

Étape 7 : va­li­da­tion 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 per­for­mance, 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 au­to­ma­ti­que­ment certaines valeurs du contenu JSON et les stockent sé­pa­ré­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 uni­que­ment à 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 en­vi­ron­ne­ments de pro­duc­tion avec de nombreux accès en lecture, cette technique est avan­ta­geuse. 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 com­bi­nai­son avec MariaDB ?

L’uti­li­sa­tion de JSON dans MariaDB est par­ti­cu­liè­re­ment utile lorsque les ap­pli­ca­tions doivent stocker des données dans une structure variable. Cela peut inclure des con­fi­gu­ra­tions, des réponses d’API, des profils uti­li­sa­teur ou des journaux. JSON conserve des contenus imbriqués et flexibles di­rec­te­ment dans une colonne. Vous ne créez pas de colonnes sup­plé­men­taires pour chaque champ possible. Au lieu de cela, vous ajoutez de nouvelles valeurs di­rec­te­ment 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é­ces­si­tent à la fois des struc­tures de table fixes et un stockage de données flexible, JSON dans MariaDB offre une solution rapide et simple. Vous tra­vail­lez avec des contenus dy­na­miques sans renoncer aux avantages des requêtes SQL clas­siques, et combinez ainsi la stabilité des données re­la­tion­nelles avec l’adap­ta­bi­lité du NoSQL.