Wie rufe ich ein bestimmtes Objekt aus einer JSON-Spalte in MySQL basierend auf einem Schlüssel-Wert-Paar ab?

MySQL JSON: Suche nach Objekten basierend auf Schlüsselwerten

MySQL-Tabellen verwenden häufig JSON-Spalten, um komplexe Datenstrukturen zu speichern. Bei der Arbeit mit JSON besteht eine häufige Aufgabe darin, ein bestimmtes Objekt basierend auf einem Schlüssel-Wert-Paar abzurufen. Es ist jedoch möglicherweise nicht immer einfach, das Objekt direkt zu finden.

JSON-Schema-Beispiel:

Betrachten Sie das folgende Schema:

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

Mit Beispiel Daten:

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

Problem:

Angenommen, wir möchten die Zeichenfolge „Grass“ aus dem Options-JSON-Array abrufen und wissen, dass der entsprechende „Wert“ „ 1." Die alleinige Verwendung von JSON_EXTRACT() erfordert den Index des Arrays, der möglicherweise nicht ohne weiteres verfügbar ist.

Lösung 1: Verwendung von JSON_SEARCH() und Horrible String Manipulation

Dieser Ansatz kombiniert JSON_SEARCH(), um den Index teilweise zu finden, und entfernt dann unerwünschte Teile aus der Zeichenfolge:

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

Lösung 2: Verwendung von JSON_TABLE()

MySQL 8.0 führt JSON_TABLE ein () um diese Aufgabe zu vereinfachen:

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

Alternativer Ansatz: Relationales Datenmodell

Für einfachere Datenstrukturen wie dieses Beispiel sollten Sie die Speicherung der Daten in einer relationalen Tabelle in Betracht ziehen mit Spalten für Schlüssel-Wert-Paare, die eine direkte Suche ermöglichen:

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

Das obige ist der detaillierte Inhalt vonWie rufe ich ein bestimmtes Objekt aus einer JSON-Spalte in MySQL basierend auf einem Schlüssel-Wert-Paar ab?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!