Travailler avec JSON dans MySQL

Dans le développement d'applications modernes, la gestion des données semi-structurées est un défi courant. La prise en charge de JSON par MySQL, introduite dans la version 5.7, fournit un moyen puissant de stocker, interroger et manipuler ces données dans une base de données relationnelle. Cet article explore les fonctions JSON essentielles proposées par MySQL avec des exemples pratiques pour vous aider à démarrer.

Pourquoi JSON dans MySQL ?

Le stockage des données JSON dans des bases de données relationnelles peut simplifier la gestion des données semi-structurées ou hiérarchiques. Il permet :

• Flexibilité : JSON permet un stockage de données dynamique et hiérarchique.
• Fonctions intégrées : Interrogez, mettez à jour et validez efficacement les données JSON.
• Intégration : Combinez des données relationnelles avec des objets JSON pour des cas d'utilisation hybrides.

1. Création de données JSON

Vous pouvez construire des objets ou des tableaux JSON à l'aide des fonctions JSON_OBJECT() et JSON_ARRAY().

Exemple :

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

Sortie :

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. Stockage des données JSON

Pour stocker des données JSON, utilisez le type de données JSON pour les colonnes.

Exemple :

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. Extraire des données de JSON

MySQL propose plusieurs fonctions pour extraire des données de documents JSON :

• JSON_EXTRACT() : Récupère les valeurs à l'aide des chemins JSON.
• -> Opérateur : raccourci pour JSON_EXTRACT().

Exemple :

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

Sortie :

4. Modification des données JSON

Vous pouvez mettre à jour ou ajouter des éléments aux données JSON avec :

• JSON_SET() : Insérer ou mettre à jour les clés.
• JSON_INSERT() : Insérer si la clé n'existe pas.
• JSON_REPLACE() : mettre à jour uniquement les clés existantes.

Exemple :

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

Sortie :

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. Suppression de clés ou de valeurs

Utilisez JSON_REMOVE() pour supprimer des éléments d'un document JSON.

Exemple :

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

Sortie :

{"name": "Bob", "age": 30, "city": "New York"}

6. Recherche en JSON

La fonction JSON_CONTAINS() vérifie si un document JSON contient une valeur spécifique.

Exemple :

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

Sortie :

7. Agrégation des données JSON

Les fonctions JSON_ARRAYAGG() et JSON_OBJECTAGG() permettent d'agréger les résultats des requêtes dans des structures JSON.

Exemple :

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

Sortie :

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

8. Validation des données JSON

La fonction JSON_VALID() vérifie si une chaîne est un JSON valide.

Exemple :

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

Sortie :

9. Jolie impression JSON

Utilisez JSON_PRETTY() pour un formatage JSON lisible par l'homme.

Exemple :

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

Sortie :

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

Fonctions JSON supplémentaires

MySQL propose une large gamme de fonctions JSON au-delà de celles couvertes en détail. Vous trouverez ci-dessous d'autres fonctions JSON :

• JSON_ARRAY_APPEND() : ajoute des valeurs à la fin d'un tableau JSON.
• JSON_ARRAY_INSERT() : insère des valeurs dans un tableau JSON aux positions spécifiées.
• JSON_CONTAINS_PATH() : vérifie si un chemin spécifié existe dans un document JSON.
• JSON_DEPTH() : renvoie la profondeur maximale d'un document JSON.
• JSON_KEYS() : Extrait les clés d'un objet JSON.
• JSON_LENGTH() : calcule le nombre d'éléments dans un tableau JSON ou le nombre de clés dans un objet JSON.
• JSON_MERGE_PATCH() : combine plusieurs documents JSON, en écrasant les clés existantes par les dernières valeurs.
• JSON_MERGE_PRESERVE() : combine plusieurs documents JSON, en préservant toutes les valeurs en créant des tableaux pour les doublons.
• JSON_OVERLAPS() : Détermine si deux documents JSON comportent des éléments qui se chevauchent.
• JSON_QUOTE() : échappe aux caractères spéciaux dans une chaîne et les renvoie sous la forme d'un littéral de chaîne JSON valide.
• JSON_SEARCH() : renvoie le chemin d'accès à une valeur spécifiée dans un document JSON.
• JSON_STORAGE_FREE() : renvoie la quantité d'espace libéré après modification d'une colonne JSON.
• JSON_STORAGE_SIZE() : Fournit la taille de stockage d'un document JSON en octets.
• JSON_TABLE() : convertit les données JSON dans un format de table relationnelle pour un traitement ultérieur.
• JSON_TYPE() : renvoie le type d'une valeur JSON (par exemple, objet, tableau, chaîne, etc.).
• JSON_UNQUOTE() : supprime les guillemets d'une chaîne JSON.

Les fonctions JSON de MySQL fournissent des outils puissants pour gérer les données semi-structurées dans des bases de données relationnelles. Ils facilitent le stockage, l'interrogation et la manipulation de JSON directement dans SQL. Comprendre ces fonctions peut aider à simplifier les flux de travail et à ouvrir de nouvelles approches en matière de conception de bases de données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!