JavaScript Object Notation mit SQL

Datenbanken eignen sich wunderbar zum strukturierten Speichern von Daten. Manchmal kann es jedoch vorkommen, dass dies aus diversen Gründen nicht ausreicht und man stattdessen komplexere Datenstrukturen via SQL speichern möchte. Man möchte Beispielsweise Metainformationen zu einem Bild (sogenannte EXIF-Daten) in der Datenbank speichern und der Einfachheit nehmen wir mal an, es handelt sich dabei um folgende Werte:

• Brennweite
• Belichtungszeit
• Urheberinformationen

Wir könnten jetzt entweder Tabellenfelder für jeden Wert erstellen

CREATE TABLE `fotos` (
  `brennweite` INT,
  `belichtungszeit` INT,
  `urheber` VARCHAR
);

oder die Daten im JSON Format in eine Tabellenspalte schreiben

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

Der wirkliche Vorteil des JSON Formats liegt darin, dass sich Objektinstanzen schnell zu/von JSON (de)serialisieren lassen. Hat man beispielsweise in PHP ein Objekt

class Foto {
  public $brennweite = 55;
  public $belichtungszeit = 120;
  public $urheber = "devdave";
}
$foto = new Foto();

können wir das fix via

json_encode($foto)

serialisieren und haben die Instanz als JSON String in der Hand und können Sie in der Datenbank speichern. Jedes mal, sobald sich an der Struktur der Klasse Foto etwas ändert auch die Datenbank nachzuziehen ist ziemlich mühselig und bietet Spielraum für Fehler. Daher wäre es in diesem Fall sinnvoll, die Daten direkt im JSON Format in der Datenbank zu speichern. Wie können wir dann aber auf die Daten später wieder zugreifen?

1. JSON_EXTRACT(%DATEN%, %PFAD%)

Mit JSON_EXTRACT besteht die Möglichkeit, Daten aus dem JSON String zu extrahieren. Dazu geben wir als ersten Parameter zunächst den JSON String an, welcher unsere Daten liefert. In unserem Fall wäre das der Name der Spalte, welche die Metadaten enthält. Unter Pfad lässt sich der Pfad zum Attribut angeben, welches ausgegeben werden soll. Angenommen, unsere JSON String sieht folgendermaßen aus:

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

dann können wir folgendermaßen auf den Wert "Urheber" zugreifen:

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Urheber": "devdave"
}', '$.Urheber');

entsprechend können wir auch auf tiefer verschachtelte Ebenen zugreifen. Liegt das Feld "Urheber" also unter "Personen", dann könnten wir wie folgt darauf zugreifen:

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": ["Urheber": "devdave", "Model": "Hannah"]
}', '$.Personen.Urheber');

auf Wertelisten lässt sich via Index zugreifen. Haben wir also mehrere Urheber, können wir folgendermaßen auf den ersten (= "devdave") zugreifen:

SELECT JSON_EXTRACT(
  '{"Brennweite": 55, "Belichtungszeit": 120,
  "Personen": ["Urheber": {"devdave", "john wayne"},
  "Model": "Hannah"]}',
  '$.Personen.Urheber[0]');

2. JSON_CONTAINS(%HAYSTACK%, %NEEDLE%, '%PATH%')

JSON_CONTAINS gibt Informationen darüber zurück, wie oft ein Wert unter einem Schlüssel zu finden ist. Wir immer geben wir hier zunächst den JSON String an, danach das Element welches wir suchen (im Folgenden 120 bzw. Max) und danach den (hypothetischen) Pfad zum Element.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
120,
'$.Belichtungszeit');

gibt 1 zurück.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
"Max",
'$.Personen.Urheber[0]');

gibt 0 zurück.

Quellen: dev.mysql.com/doc/refman/8.0/en/json-search-functions.html