JavaScript Object Notation mit SQL

#json#json_contains#json_extract#mysql#sql#structured-data#strukturierte-daten

Datenbanken eignen si wunderbar zum strukturierten Speichern von Daten. Manchmoi kann's aber vorkemman, dass des aus diversen Gründen ned reicht und ma stattdessen komplexere Datenstrukturen via SQL speichern wü. Ma mecht zum Beispiel Metainformationen zu am Bild (sogenannte EXIF-Daten) in da Datenbank speichern, und der Einfachheit halber nehmen mia an, es handelt si dabei um folgende Werte:

Brennweite
Belichtungszeit
Urheberinformationen

Mia kinnan jetzt entweder Tabellenfelder für jeden Wert erstöin

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

oda die Daten im JSON-Format in a Tabellenspoitn schreibn

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

Der wirkliche Vorteil vom JSON-Format liegt drin, dass si Objektinstanzen schnö zu/von JSON (de)serialisiern lassn. Hot ma zum Beispiel in PHP a Objekt

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

kinnan mia des fix via

json_encode($foto)

serialisiern und hom die Instanz ois JSON-String in da Hand und kinnan sie in da Datenbank speichern. Jedes Moi, sobois si an da Struktur von da Klass Foto wos ändert, a die Datenbank nachzuziehn, is ziemlich mühsam und bietet Spielraum für Fehler. Drum wär's in dem Foi sinnvoll, die Daten direkt im JSON-Format in da Datenbank zu speichern. Wia kinnan mia dann aber später wieda auf die Daten zugreifn?

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

Mit JSON_EXTRACT besteht die Möglichkeit, Daten aus dem JSON-String zu extrahiern. Dazu gebn mia ois ersten Parameter zuerst den JSON-String o, der unsere Daten liefert. In unserem Foi wär des da Nama von da Spoitn, de die Metadaten enthoitet. Unter Pfad lasst si da Pfad zum Attribut ongebn, des ausgebn wern soi. Angnommen, unser JSON-String schaut folgendermaßen aus:

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

dann kinnan mia folgendermaßen auf den Wert "Urheber" zugreifn:

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

entsprechend kinnan mia a auf tiefer verschachtelte Ebenen zugreifn. Liegt des Feld "Urheber" also unter "Personen", dann kunntn mia wia folgt drauf zugreifn:

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

Auf Wertelisten lasst si via Index zugreifn. Hom mia also mehrere Urheber, kinnan mia folgendermaßen auf den ersten (= "devdave") zugreifn:

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 zruck, wia oft a Wert unter am Schlüssel zu finden is. Wia imma gebn mia do zuerst den JSON-String o, danoch des Element, des mia suachn (im Folgenden 120 bzw. Max), und danoch den (hypothetischen) Pfad zum Element.

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

gibt 1 zruck.

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

gibt 0 zruck.

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