Comment récupérer efficacement une valeur texte à partir d'un objet JSON dans MySQL en fonction d'une valeur clé ?

Récupération d'objets à partir de clés JSON dans MySQL

Problème :
Lorsque vous travaillez avec des données JSON dans une base de données MySQL, comment pouvez-vous efficacement rechercher un objet spécifique en fonction d'une valeur de clé tout en interrogeant à l'aide d'une clé différente ?

Schéma :

Considérez l'exemple de 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>

<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, ...]');</code>

Objectif :

Étant donné l'ID d'un champ, récupérer la valeur texte correspondante d'une option spécifique en fonction de sa valeur.

Précédent Solutions :

Tentatives initiales impliquaient l'utilisation d'une combinaison de fonctions JSON. Cependant, ces approches étaient lourdes et nécessitaient des expressions complexes.

Fonction JSON_TABLE() (MySQL 8.0) :

MySQL 8.0 a introduit la fonction JSON_TABLE(), qui simplifie la processus :

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

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+</code>

Alternative non-JSON :

Une solution plus simple consiste à convertir les données JSON en une table normalisée :

<code class="sql">CREATE TABLE `field_options` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field_id` int(10) unsigned NOT NULL,
  `text` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`field_id`) REFERENCES `fields`(`id`)
);</code>

<code class="sql">INSERT INTO `field_options` (field_id, text, value) VALUES
(
  1, 'Grass', '1'),
  (1, 'Synthetic (New Type - Soft)', '2'),
  ...
);</code>

Cette approche permet des recherches efficaces à l'aide de techniques SQL traditionnelles :

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