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

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

Patricia Arquette
Patricia ArquetteOriginal
2024-11-05 00:19:01801Durchsuche

How to Efficiently Retrieve a Text Value from a JSON Object in MySQL Based on a Key Value?

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!

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn