Comment utiliser les champs de données JSON dans les bases de données MySQL —

Mon article «SQL vs NoSQL : les différences» a noté que la frontière entre les bases de données SQL et NoSQL est devenue de plus en plus floue, chaque camp adoptant les fonctionnalités de l’autre. Les bases de données MySQL 5.7+ InnoDB et PostgreSQL 9.2+ prennent toutes deux directement en charge les types de documents JSON dans un seul champ. Cet article examinera plus en détail l’implémentation de MySQL 9.1 JSON.

Notez que toute base de données acceptera les documents JSON en tant que blob à chaîne unique. Cependant, MySQL et PostgreSQL prennent en charge les données JSON validées dans de vraies paires clé/valeur plutôt que dans une chaîne de base.

Points clés à retenir

• Les types de documents JSON sont directement pris en charge dans les bases de données MySQL 5.7+ InnoDB et PostgreSQL 9.2+, mais ils doivent être utilisés judicieusement en raison des limitations de l’indexation directe.
• JSON est mieux adapté aux données peu peuplées, aux attributs personnalisés, aux structures hiérarchiques et aux cas nécessitant de la flexibilité. Il ne doit pas remplacer les colonnes normalisées pour les données fréquemment interrogées ou indexées.
• MySQL offre une variété de fonctions pour créer, valider, rechercher et modifier des objets JSON. Ceux-ci incluent JSON_ARRAY(), JSON_OBJECT(), JSON_QUOTE(), JSON_TYPE(), JSON_VALID(), JSON_CONTAINS(), JSON_SEARCH(), et functions like JSON_SET() and JSON_MERGE_PATCH() pour mettre à jour les documents JSON à l’aide de la notation de chemin.
• MySQL 9.1 prend en charge l’indexation fonctionnelle sur les colonnes générées dérivées des données JSON, permettant une interrogation efficace d’éléments JSON spécifiques.
• Bien que MySQL prenne en charge JSON, il reste une base de données relationnelle. Une utilisation excessive de JSON pourrait annuler les avantages de SQL.

Tout simplement parce que vous pouvez stocker des documents JSON dans des colonnes MySQL JSON…

… cela ne veut pas dire que vous devriez le faire.

La normalisation est une technique utilisée pour optimiser la structure de la base de données. La règle de la première forme normale (1NF) stipule que chaque colonne doit contenir une valeur unique, ce qui est clairement rompu par le stockage de documents JSON à valeurs multiples.

Si vous avez des exigences claires en matière de données relationnelles, utilisez les champs à valeur unique appropriés. JSON doit être utilisé avec parcimonie en dernier recours. Les champs de valeur JSON ne peuvent pas être indexés directement, évitez donc de les utiliser sur des colonnes mises à jour ou recherchées régulièrement.

L’indexation fonctionnelle sur les colonnes générées dérivées de JSON vous permet d’indexer des parties de votre objet JSON, améliorant ainsi les performances des requêtes.

Cela dit, il existe de bons cas d’utilisation de JSON pour les données peu peuplées ou les attributs personnalisés.

Créer une table avec une colonne de type de données JSON

Prenons l’exemple d’un magasin vendant des livres. Tous les livres ont un identifiant, un ISBN, un titre, un éditeur, un nombre de pages et d’autres données relationnelles claires.

Maintenant, si vous souhaitez ajouter un nombre quelconque de balises de catégorie à chaque livre. Vous pouvez y parvenir en SQL en utilisant :

1. UN étiqueter table qui stockait chaque nom de balise avec un identifiant unique et
2. UN carte de balises table avec plusieurs à plusieurs enregistrements mappant les ID de livre aux ID de balise

Cela fonctionnera, mais c’est un effort fastidieux et considérable pour une fonctionnalité mineure. Par conséquent, vous pouvez définir un champ MySQL JSON pour les balises dans le fichier MySQL de votre base de données. livre tableau:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Les colonnes MySQL JSON ne peuvent pas avoir de valeur par défaut, ne peuvent pas être utilisées comme clé primaire, ne peuvent pas être utilisées comme clé étrangère ou avoir des index directs.

Mais avec MySQL 9.1, vous pouvez créer index fonctionnels sur colonnes générées dérivé de données JSON, ce qui permet d’indexer des éléments spécifiques dans un document JSON. Ces colonnes générées peuvent être virtuelles ou stockées et indexées comme index secondaires.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Ajout de données JSON

Des documents JSON entiers peuvent être transmis INSÉRER ou MISE À JOUR déclarations, facilitant le déplacement de JSON vers MySQL pour le stockage et la manipulation.

Par exemple, nos balises de livre peuvent être transmises sous forme de tableau (à l’intérieur d’une chaîne) :

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON peut également être créé avec ceux-ci :

• Fonction JSON_ARRAY()qui crée des tableaux. Par exemple:-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');
• Fonction JSON_OBJECT()qui crée des objets. Par exemple:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
• Fonction JSON_QUOTE()qui cite une chaîne comme valeur JSON. Par exemple:-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE('[1, 2, "abc"]');
• Fonction CAST (anyValue AS JSON)qui convertit une valeur en type JSON pour en assurer la validité :SELECT CAST('{"a": 1, "b": 2}' AS JSON);

Le Fonction JSON_TYPE() vous permet de vérifier les types de valeurs JSON. Il doit renvoyer OBJECT, ARRAY, un type scalaire (INTEGER, BOOLEAN, etc.), NULL ou une erreur. Par exemple:

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Le Fonction JSON_VALID() renvoie 1 si le JSON est valide ou 0 sinon :

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Toute tentative d’insertion d’un document JSON non valide générera une erreur et l’intégralité de l’enregistrement ne sera pas insérée/mise à jour.

Recherche de documents JSON dans la colonne MySQL JSON

Avec des fonctions JSON MySQL comme Fonction JSON_CONTAINS()vous pouvez vérifier si un document JSON contient une valeur spécifique. Il renvoie 1 lorsqu’une correspondance est trouvée. Par exemple:

SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

Le Fonction JSON_SEARCH() renvoie le chemin d’accès à une valeur dans un document JSON. Il renvoie NULL lorsqu’il n’y a pas de correspondance.

Vous pouvez également spécifier si vous devez rechercher toutes les correspondances ou des correspondances uniques en passant les indicateurs « une » et « toutes » à côté de la chaîne de recherche (où % correspond à n’importe quel nombre de caractères et _ correspond à un caractère de la même manière que COMME). Par exemple:

SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

Le Fonction JSON_TABLE() transforme les données JSON dans un format relationnel pour faciliter les requêtes :

SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

Chemins JSON

Une requête MySQL JSON utilisant le Fonction JSON_EXTRACT() peut récupérer des valeurs spécifiques d’un document JSON en fonction d’un chemin spécifié.

SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Toutes les définitions de chemin commencent par un $ suivi d’autres sélecteurs :

• Un point suivi d’un nom, tel que $.website
• [N] où N est la position dans un tableau indexé zéro
• Le .[*] le caractère générique évalue tous les membres d’un objet
• Le [*] le caractère générique évalue tous les membres d’un tableau
• Le caractère générique préfixe**suffixe prend en compte tous les chemins qui commencent par le préfixe nommé et se terminent par le suffixe nommé.

Les exemples suivants font référence au document JSON suivant :

{
  "a": ,
  "b": 2,
  "c": [, ],
  "d": {
    "e": ,
    "f": 6
  }
}

Exemples de chemins :

• $.a renvoie 1
• $.c renvoie [3, 4]
• $.c[1] renvoie 4
• $.de renvoie 5
• $**.e renvoie [5]

Vous pouvez utiliser la fonction MySQL d’extraction JSON pour extraire efficacement le nom et la première balise de votre table de livre :

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

Pour un exemple plus complexe, supposons que vous ayez un utilisateur table avec les données de profil JSON. Par exemple:

Vous pouvez extraire le nom Twitter à l’aide d’un chemin JSON. Par exemple:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

Vous pouvez utiliser un chemin JSON dans la clause WHERE pour renvoyer uniquement les utilisateurs disposant d’un compte Twitter :

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Modifier une partie d’un document JSON

Il existe plusieurs fonctions MySQL qui modifient des parties d’un document JSON à l’aide de la notation de chemin. Ceux-ci incluent :

• JSON_SET(doc, chemin, val[, path, val]…): Insère ou met à jour des données dans le document.UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');
• JSON_INSERT(doc, chemin, val[, path, val]…): Insère des données dans le document sans écraser les valeurs existantes.UPDATE book SET tags = JSON_INSERT(tags, '$[0]', 'New Tag');
• JSON_REPLACE(doc, chemin, val[, path, val]…): Remplace les données dans le document.UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');
• JSON_MERGE_PATCH(doc,doc[, doc]…): fusionne deux documents JSON ou plus, en remplaçant les clés existantes par les valeurs des documents suivants.UPDATE book SET tags = JSON_MERGE_PATCH(tags, '["technical"]') WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;
• JSON_ARRAY_APPEND(doc, chemin, val[, path, val]…): Ajoute des valeurs à la fin d’un tableau.UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_INSERT(doc, chemin, val[, path, val]…): Insère des valeurs à une position spécifique dans un tableau JSON.UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');
• JSON_REMOVE(doc, chemin[, path]…): Supprime les données du document.UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');
• JSON_PRETTY(val): Imprime joliment les documents JSON pour une meilleure lisibilité.SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ["MySQL", "JSON"]}');

Par exemple, si vous souhaitez ajouter une balise « technique » à n’importe quel livre qui possède déjà une balise « JavaScript », vous pouvez utiliser la fonction JSON_MERGE_PATCH() :

UPDATE book
SET tags = JSON_MERGE_PATCH(tags, '["technical"]')
WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Informations complémentaires

La documentation MySQL fournit des informations détaillées sur MySQL Type de données JSON et le fonctions JSON associées.

Encore une fois, je vous conseille de ne pas utiliser JSON sauf si cela est absolument nécessaire. Vous pourriez émuler toute une base de données NoSQL orientée document dans MySQL, mais cela annulerait de nombreuses avantages de SQLet autant passer à un vrai système NoSQL !

Cela dit, les types de données JSON peuvent permettre d’économiser des efforts pour répondre à des exigences de données plus obscures au sein d’une application SQL.

FAQ sur l’utilisation des données JSON dans MySQL

Pouvez-vous utiliser JSON dans MySQL ?

MySQL prend en charge JSON en proposant un type de données JSON pour stocker les données au format JSON dans des colonnes. À partir de MySQL 5.7.8, vous pouvez créer des tables avec des colonnes JSON, vous permettant d’insérer, de mettre à jour et d’interroger des données JSON à l’aide de SQL. MySQL fournit une gamme de fonctions JSON pour travailler avec les données JSON dans ces colonnes, permettant l’extraction, la modification et la manipulation.

De plus, vous pouvez utiliser des données JSON dans des requêtes SQL, en les convertissant en données relationnelles si nécessaire à l’aide de fonctions telles que JSON_TABLE. Cependant, il est important de comprendre que MySQL est fondamentalement une base de données relationnelle et que sa prise en charge du type de données JSON est destinée à faciliter l’utilisation des données JSON dans un contexte relationnel plutôt que d’être une base de données NoSQL JSON à part entière.

Comme indiqué dans l’article ci-dessus, ce n’est pas parce que vous pouvez stocker du JSON que vous devriez le faire : la normalisation est une technique utilisée pour optimiser la structure de la base de données. La règle First Normal Form (1NF) stipule que chaque colonne doit contenir une seule valeur, ce qui est rompu par le stockage de documents JSON à valeurs multiples.

Est-il acceptable de stocker JSON dans MySQL ?

Il est possible de stocker JSON dans MySQL pour des scénarios tels que :

• Données semi-structurées ou dynamiques cela ne s’intègre pas bien dans un schéma rigide.
• Attributs personnalisés où la conception relationnelle serait inefficace.
• Intégration avec les API basées sur JSON pour stocker des charges utiles ou des journaux.

Cependant, JSON devrait pas remplacer le stockage relationnel normalisé pour les données structurées et fréquemment interrogées. Bien que MySQL 9.1 améliore la fonctionnalité JSON avec des fonctionnalités telles que les index fonctionnels et JSON_TABLE, les opérations JSON peuvent toujours introduire une surcharge pour les grands ensembles de données ou les requêtes complexes.

Comment utiliser JSON dans une requête MySQL ?

Vous pouvez utiliser JSON dans les requêtes MySQL en employant Fonctions JSON de MySQL. Ces fonctions vous permettent d’extraire, de manipuler et d’interroger les données JSON stockées dans des colonnes JSON ou des chaînes au format JSON dans votre base de données. Pour accéder aux données JSON dans une colonne JSON, utilisez l’opérateur -> suivi du chemin d’accès à l’élément JSON souhaité.

Les fonctions JSON telles que JSON_EXTRACT, JSON_SET et JSON_OBJECTAGG vous permettent de filtrer, modifier, agréger et utiliser des données JSON. Vous pouvez également filtrer les lignes en fonction des valeurs JSON à l’aide de la clause WHERE. Les fonctionnalités JSON de MySQL offrent un moyen polyvalent d’interagir et de manipuler un objet JSON directement dans vos requêtes de base de données.

Quand utiliser JSON dans MySQL ?

Vous devez utiliser JSON dans MySQL pour les scénarios suivants :

1. Données semi-structurées: utilisez JSON lorsque vous traitez des champs imprévisibles ou clairsemés (par exemple, des attributs personnalisés).
2. Schémas dynamiques: JSON offre de la flexibilité lorsque les exigences en matière de données changent fréquemment.
3. Données hiérarchiques ou imbriquées: JSON prend en charge les données avec des relations parent-enfant ou des tableaux.
4. Intégration API: stockez les charges utiles, les réponses ou les journaux sous forme de documents JSON.

Cependant, évitez JSON pour :

• Champs fréquemment interrogés qui nécessitent une indexation (les index fonctionnels peuvent aider, mais la conception relationnelle est souvent plus rapide).
• Données strictement relationnelles nécessitant une normalisation.
• Situations dans lesquelles des requêtes complexes sur les chemins JSON dégraderaient les performances.

Comment stocker des données JSON dans MySQL ?

Pour stocker des données JSON dans MySQL, vous disposez de deux options principales. Tout d’abord, vous pouvez utiliser le type de données JSON introduit dans MySQL pour créer une table avec une colonne JSON. Cette méthode fournit un stockage structuré et de meilleures performances de requête pour les données JSON.

Vous pouvez également stocker les données JSON sous forme de texte dans une colonne VARCHAR ou TEXT standard. Cette approche convient lorsque vous avez principalement besoin de stocker et de récupérer des données JSON sans opérations de base de données complexes.

Comment indexer les données JSON dans MySQL ?

Bien que vous ne puissiez pas indexer directement une colonne JSON, MySQL vous permet de créer index fonctionnels sur les colonnes générées dérivées des valeurs JSON.

Par exemple, pour indexer le premier élément d’un tableau JSON :

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Cette approche améliore les performances des requêtes pour les chemins JSON fréquemment consultés.

Devriez-vous utiliser MySQL ou une base de données NoSQL pour les données JSON ?

Cela dépend des exigences de votre projet :

• Choisissez MySQL si vous avez besoin d’un stockage relationnel avec une gestion JSON occasionnelle pour des données semi-structurées, des attributs personnalisés ou des données hiérarchiques au sein d’un modèle relationnel.
• Choisissez une base de données NoSQL (comme MongoDB) si votre projet implique un stockage JSON étendu, des schémas flexibles et des opérations basées sur des documents comme cas d’utilisation principal.

La prise en charge JSON de MySQL est excellente pour les charges de travail hybrides, mais ne peut pas remplacer entièrement une base de données NoSQL spécialement conçue pour le stockage de documents.

Comment extraire des valeurs spécifiques d’un champ MySQL JSON ?

Pour extraire des valeurs spécifiques d’un Champ JSON MySQLutilisez la fonction JSON_EXTRACT() ou l’opérateur abrégé ->.

SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag FROM book;


SELECT tags->'$[0]' AS first_tag FROM book;

Comment interroger et filtrer des données dans un champ MySQL JSON ?

Pour interroger et filtrer les données stockées dans un Champ JSON MySQLvous pouvez utiliser des fonctions comme JSON_CONTAINS() et JSON_SEARCH(). Vous pouvez également utiliser JSON_EXTRACT() pour récupérer des valeurs spécifiques pour un filtrage plus poussé.

SELECT * FROM book 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');


SELECT * FROM book 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;


SELECT * FROM book 
WHERE JSON_EXTRACT(tags, '$[0]') = 'JavaScript';