Comment récupérer un objet spécifique d'un champ JSON dans une table MySQL par valeur clé ?

Recherche d'objets dans MySQL JSON par valeur de clé

Scénario :

Vous travaillez avec un champ JSON dans un Table MySQL et que vous souhaitez récupérer un objet spécifique à partir de celle-ci tout en recherchant une valeur de clé particulière.

Structure du document JSON :

Considérez le schéma suivant :

<code class="sql">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;</code>

Avec des exemples de données :

<code class="sql">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"}]'
);</code>

Défi :

Vous devez sélectionner la valeur du texte "Grass" dans le champ d'options en fonction d'un recherchez la valeur "1".

Solution MySQL 8.0 :

MySQL 8.0 a introduit la fonction JSON_TABLE(), offrant une solution plus efficace :

<code class="sql">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;</code>

Cette requête utilise la jointure croisée et JSON_TABLE() pour récupérer les colonnes de texte et de valeur pour chaque objet dans le champ d'options.

Préoccupations avec JSON_TABLE :

Cependant, l'utilisation de JSON_TABLE() peut être complexe et doit être répétée à chaque fois qu'une requête comme celle-ci est nécessaire.

Recommandation :

Au lieu d'utiliser JSON, il peut être plus simple de stocker les données dans un tableau avec des colonnes dédiées pour les paires texte et valeur. Cela permettrait des recherches simples en utilisant la syntaxe SQL normale :

<code class="sql">SELECT * FROM field_options WHERE value = '1';</code>

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!