Rechercher l'objet correspondant à la valeur clé dans MySQL JSON : comment l'implémenter

Question

J'essaie de trouver un moyen de rechercher un objet JSON et d'obtenir une clé spécifique, mais je recherche une autre clé.

Voici un exemple d'architecture :

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;

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"}]'
);

Base de données Fiddle : https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

Donc ce que je veux, c'est sélectionner la chaîne "Grass" en fournissant l'ID 1 从 options.

Mais il ne semble y avoir aucun moyen de faire cela. Je peux obtenir de l'herbe en faisant ceci :

select JSON_EXTRACT(`options`, '$[0].text') from `fields`;
// "Grass"

Mais cela nécessite de connaître l'index du tableau

Je peux obtenir partiellement l'index du tableau comme ceci :

select JSON_SEARCH(`options`, 'one', '1') from `fields`;
// "$[0].value"

Et obtenez l'index lui-même en faisant quelque chose de vraiment effrayant comme ceci :

select
REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', '')
from `fields`;
// 0

Même réaliser ce que je veux en faisant des choses vraiment effrayantes comme celle-ci :

select
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;
// "Grass"

Mais il doit y avoir une meilleure façon, non ?

Base de données Fiddle : https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

Answer 1

MySQL 8.0 fournit JSON_TABLE() pour vous aider à gérer de tels cas.

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;
+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

Mais vous devez exécuter cette expression complexe JSON_TABLE() à chaque fois vous souhaitez écrire une requête comme celle-ci.

Il serait plus simple de ne pas utiliser JSON et de stocker les données dans un tableau avec des colonnes normales (une ligne par paire texte/valeur). Vous pouvez ensuite rechercher la valeur souhaitée dans n'importe quelle colonne.

SELECT * FROM field_options WHERE value = '1';

99% des utilisations de JSON dans MySQL que je vois sur Stack Overflow peuvent être facilement résolues en n'utilisant pas JSON.