Wie kann ich basierend auf einem Schlüsselwert effizient einen Textwert aus einem JSON-Objekt in MySQL abrufen?

Objekte aus JSON-Schlüsseln in MySQL abrufen

Problem:
Wie können Sie effizient mit JSON-Daten in einer MySQL-Datenbank arbeiten? Nach einem bestimmten Objekt basierend auf einem Schlüsselwert suchen, während die Abfrage mit einem anderen Schlüssel durchgeführt wird?

Schema:

Betrachten Sie das folgende Beispielschema:

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

Ziel:

Anhand der ID eines Felds den entsprechenden Textwert einer bestimmten Option basierend auf ihrem Wert abrufen.

Zurück Lösungen:

Erste Versuche beinhalteten die Verwendung einer Kombination von JSON-Funktionen. Diese Ansätze waren jedoch umständlich und erforderten komplexe Ausdrücke.

JSON_TABLE()-Funktion (MySQL 8.0):

MySQL 8.0 führte die JSON_TABLE()-Funktion ein, die die vereinfacht Prozess:

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

Nicht-JSON-Alternative:

Eine einfachere Lösung besteht darin, die JSON-Daten in eine normalisierte Tabelle umzuwandeln:

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

Dieser Ansatz ermöglicht effiziente Suchvorgänge mit herkömmlichen SQL-Techniken:

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

Das obige ist der detaillierte Inhalt vonWie kann ich basierend auf einem Schlüsselwert effizient einen Textwert aus einem JSON-Objekt in MySQL abrufen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!