Comment récupérer efficacement des objets JSON dans MySQL en fonction des valeurs clés ?

Recherche d'objets basés sur des valeurs clés dans MySQL JSON

Lorsque vous travaillez avec des données JSON dans MySQL, localiser un objet basé sur une valeur clé spécifique peut être difficile. En effet, les méthodes traditionnelles nécessitent une connaissance de l'index du tableau ou des techniques d'extraction complexes.

JSON_TABLE() à la rescousse

Heureusement, MySQL 8.0 a introduit la fonction JSON_TABLE() , ce qui simplifie ce processus. Considérons le schéma suivant :

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Avec les données insérées comme :

INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);

Pour rechercher la chaîne "Grass" en fournissant la clé "1", vous pouvez utiliser la requête suivante :

select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;

Cette requête renverra l'objet souhaité :

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

Alternative : structure de table traditionnelle

Alors que JSON_TABLE() fournit un solution, il peut être fastidieux de l’utiliser à plusieurs reprises. Une approche alternative consiste à stocker les données dans une structure de table traditionnelle avec des colonnes séparées pour le texte et la valeur, comme suit :

CREATE TABLE field_options (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  text varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  value varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Avec les données insérées comme :

INSERT INTO field_options (text, value) VALUES
('Grass', '1'),
('Synthetic (New Type - Soft)', '2'),
('Synthetic  (Old Type - Hard)', '3'),
('Gravel', '5'),
('Clay', '6'),
('Sand', '7'),
('Grass/Synthetic Mix', '8');

En utilisant cette structure, la recherche devient plus simple :

SELECT * FROM field_options WHERE value = '1';

Cette approche supprime le besoin de requêtes JSON complexes, ce qui rend la récupération des données plus simple.

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!