Maîtriser le Type JSON dans MySQL : Requêtes, Mutations et Indexation

Évolution du Stockage JSON dans les Bases Relationnelles

Le stockage de données semi-structurées ou non structurées, telles que les configurations dynamiques, les métadonnées ou les logs, a longtemps représenté un compromis dans les bases de données relationnelles. Avant la version 5.7.8, MySQL imposait l'utilisation de types textuels (comme VARCHAR ou TEXT) pour sauvegarder du JSON. Cette approche empêchait toute validation syntaxique à l'insertion et rendait l'exploitation des données extrêmement coûteuse en nécessitant un parsing applicatif.

L'introduction du type natif JSON a résolu ces problèmes en offrant une validation automatique du format lors de l'insertion et un stockage binaire interne optimisé, permettant un parsing et une extraction rapides via des fonctions SQL dédiées.

Formats et Validation des Données

Le moteur accepte deux structures fondamentales qui peuvent être imbriquées :

  • Les tableaux : Une liste ordonnée de valeurs encadrées par des crochets (ex: [10, "config", true]).
  • Les objets : Des paires clé-valeur encadrées par des accolades. Les clés doivent impérativement être des chaînes de caractères (ex: {"timeout": 30, "retries": 3}).

Note technique : Dans un objet JSON, les clés dupliquées ne sont pas autorisées. Lors de la normalisation, MySQL conserve la dernière valeur rencontrée pour une clé donnée (ce comportement a été inversé avant la version 8.0.3, où c'était la première valeur qui primait).

Initialisation et Fonctions de Construction

Pour illustrer les opérations, initialisons une table de registre de configurations :

CREATE TABLE config_registry (
    record_id INT PRIMARY KEY,
    payload JSON
);

INSERT INTO config_registry VALUES 
(1, '[10, 20, "alpha", "beta"]'),
(2, '{"timeout": 30, "retries": 3, "timeout": 60}');

MySQL fournit des fonctions pour construire dynamiquement des documents JSON :

-- Création d'un tableau (les booléens sont normalisés en minuscules)
SELECT JSON_ARRAY(1, NULL, 'TRUE', FALSE);

-- Création d'un objet (les clés sont triées alphabétiquement, les doublons écrasés)
SELECT JSON_OBJECT('env', 'prod', 'port', 8080, 'port', 9090);

-- Conversion explicite d'une chaîne en type JSON natif
SELECT CAST('{"level": "debug"}' AS JSON);

Extraction et Interrogation des Données

L'extraction ciblée évite de devoir parser l'intégralité de la chaîne de caractères.

Utilisation de JSON_EXTRACT et des opérateurs de chemin

La fonction JSON_EXTRACT() utilise la syntaxe de chemin (path) pour localiser les données. Les opérateurs -> et ->> sont des raccourcis syntaxiques. Le premier retourne le JSON brut (avec guillemets pour les chaînes), tandis que le second retourne la valeur textuelle désérialisée.

-- Extraction d'un élément de tableau par index
SELECT record_id, payload->'$[1]' FROM config_registry WHERE record_id = 1;

-- Extraction d'une plage d'éléments
SELECT record_id, payload->'$[1 to 2]' FROM config_registry WHERE record_id = 1;

-- Extraction d'une valeur d'objet (désérialisée)
SELECT record_id, payload->>'$.timeout' FROM config_registry WHERE record_id = 2;

Extraction des clés avec JSON_KEYS

Pour obtenir la structure d'un objet sans ses valeurs, JSON_KEYS() retourne un tableau des clés de premier niveau, ou d'un niveau spécifique si un chemin est fourni.

SELECT JSON_KEYS('{"db": {"host": "localhost", "port": 3306}}', '$.db');
-- Résultat : ["host", "port"]

Mutations : Insertino, Mise à jour et Suppression

La modification de documents JSON peut se faire de manière granulaire sans réécrire l'intégralité du document.

Ajout d'éléments

  • JSON_ARRAY_APPEND() : Ajoute une valeur à un tableau existant à un index donné, transformant l'élément cible en sous-tableau.
  • JSON_ARRAY_INSERT() : Insère un nouvel élément à un index spécifique, décalant les éléments suivants.
  • JSON_INSERT() : Ajoute des paires clé-valeur uniquement si les chemins spécifiés n'existent pas encore (pas de mise à jour).
-- Ajout d'un élément à la fin d'un tableau existant à l'index 0 (devient [10, "gamma"])
UPDATE config_registry SET payload = JSON_ARRAY_APPEND(payload, '$[0]', 'gamma') WHERE record_id = 1;

-- Insertion conditionnelle dans un objet
UPDATE config_registry SET payload = JSON_INSERT(payload, '$.new_key', 'new_value', '$.timeout', 999) WHERE record_id = 2;

Mise à jour et Remplacement

  • JSON_REPLACE() : Modifie les valeurs existantes uniquement. Ignore les chemins inexistants.
  • JSON_SET() : Fonction "upsert". Met à jour les chemins existants et crée ceux qui n'existent pas.
-- Remplacement strict
UPDATE config_registry SET payload = JSON_REPLACE(payload, '$[0]', 'updated_alpha') WHERE record_id = 1;

-- Mise à jour et ajout simultané
UPDATE config_registry SET payload = JSON_SET(payload, '$.timeout', 120, '$.max_connections', 500) WHERE record_id = 2;

Suppression

JSON_REMOVE() supprime les éléments aux chemins indiqués. Attention, la suppression d'un élément dans un tableau décale les index des éléments suivants.

SELECT JSON_REMOVE(payload, '$[1]', '$.retries') FROM config_registry;

Vérification d'Existence et Recherche

Pour les conditions booléennes ou la recherche de valeurs spécifiques :

-- Vérifie si au moins un des chemins existe ('one' ou 'all')
SELECT JSON_CONTAINS_PATH(payload, 'one', '$.timeout', '$.retries') FROM config_registry WHERE record_id = 2;

-- Recherche une valeur et retourne son chemin (supporte les wildcards % et _)
SELECT JSON_SEARCH(payload, 'all', 'alpha') FROM config_registry WHERE record_id = 1;

-- Vérifie si une valeur spécifique est présente dans un tableau (MySQL 8.0.17+)
SELECT record_id, 'beta' MEMBER OF(payload) FROM config_registry WHERE record_id = 1;

Stratégies d'Indexation pour JSON

Par défaut, les colonnes JSON ne peuvent pas être indexées directement. Deux approches permettent de contourner cette limitation pour accélérer les requêtes.

Index sur Colonnes Générées (Generated Columns)

Vous pouvez extraire une valeur spécifique dans une colonne virtuelle et indexer cette colonne. La colonne n'est pas stockée physiquement (mode VIRTUAL) mais calculée à la volée, ou stockée (mode STORED).

CREATE TABLE indexed_configs (
    id INT PRIMARY KEY,
    data JSON,
    timeout_val INT GENERATED ALWAYS AS (data->>'$.timeout') VIRTUAL,
    INDEX idx_timeout (timeout_val)
);

-- L'index est utilisé pour la recherche
EXPLAIN SELECT * FROM indexed_configs WHERE timeout_val = 60;

Index Multi-Valeurs (Multi-Valued Indexes)

Introduit dans MySQL 8.0.17, cet index permet d'indexer directement les éléments d'un tableau JSON. Un seul enregistrement de la table peut générer plusieurs entrées dans l'index. L'indexation nécessite une conversion explicite du tableau JSON en tableau SQL typé.

CREATE TABLE multi_val_demo (
    id INT PRIMARY KEY,
    tags JSON,
    INDEX idx_tags ((CAST(tags AS UNSIGNED ARRAY)))
);

INSERT INTO multi_val_demo VALUES 
(1, '[10, 20, 30]'), 
(2, '[40, 50]');

-- La clause MEMBER OF exploite l'index multi-valeurs
EXPLAIN SELECT * FROM multi_val_demo WHERE 20 MEMBER OF(tags);

Note technique : Le type spécifié dans le CAST (ici UNSIGNED) impose une contrainte stricte sur les données du tableau JSON. Toute valeur non convertible entraînera une erreur lors de l'insertion.

Étiquettes: MySQL JSON Multi-Valued-Index Generated-Column sql-optimization

Publié le 29 juin à 23h37

Étiquettes Populaires

©2026 L'Atelier Monstre