Comment récupérer un objet spécifique d'une colonne JSON dans MySQL en fonction d'une paire clé-valeur ?

MySQL JSON : recherche d'objets basée sur des valeurs clés

Les tables MySQL utilisent souvent des colonnes JSON pour stocker des structures de données complexes. Lorsque vous travaillez avec JSON, une tâche courante consiste à récupérer un objet spécifique basé sur une paire clé-valeur. Cependant, trouver l'objet directement n'est pas toujours simple.

Exemple de schéma 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 exemple data:

<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>

Problème :

Supposons que nous voulions récupérer la chaîne "Grass" du tableau d'options JSON, sachant que sa "valeur" correspondante est " 1." L'utilisation de JSON_EXTRACT() seule nécessite l'index du tableau, qui peut ne pas être facilement disponible.

Solution 1 : Utilisation de JSON_SEARCH() et d'une horrible manipulation de chaînes

Cette approche combine JSON_SEARCH() pour trouver partiellement l'index, puis supprime les parties indésirables de la chaîne :

<code class="sql">SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;</code>

Solution 2 : Utilisation de JSON_TABLE()

MySQL 8.0 introduit JSON_TABLE () pour simplifier cette tâche :

<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>

Approche alternative : modèle de données relationnelles

Pour des structures de données plus simples comme cet exemple, envisagez de stocker les données dans une table relationnelle avec des colonnes pour les paires clé et valeur, permettant une recherche directe :

<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!