JavaScript Object Notation Support in Oracle 12.1.0.2
Mit der Oracle 12c Release 12.1.0.2 hat die Oracle Datenbank JSON Unterstützung erhalten. JSON kann nun direkt gespeichert, abgefragt und auch indiziert werden. Im Folgenden soll dazu ein kurzer Überblick gegeben werden.
JSON steht für JavaScript Object Notation und beschreibt wie der Name schon ausdrückt eine Möglichkeit komplexe Objekte zu notieren und zu verarbeiten. Die Datenbank verwendet intern grundsätzlich immer UTF-8, entsprechend notwendige Konvertierungen bei der Ein- und Ausgabe werden automatisch durchgeführt.
Folgendes Beispiel eines Studenten soll dies verdeutlichen:
{ „Matrikelnummer“ : 123564869,
„Name“ : „Otto Mayer“,
„Studiengang“ : „Informationstechnik“,
„aktuelles Semester“ : „WS2014/15“,
„Fächer“ : [ „Business Englisch“,
„Objektorientiertes Programmieren“,
„Mathematik 3“]
}
Um nun ein JSON speichern zu können wird eine simple VARCHAR2 oder bei größeren Objekten eine CLOB Spalte verwendet. Neu dazukommen ist der Check-Constraint IS JSON um sicherzustellen, dass die Spalte auch wirklich immer valide JSON-Syntax enthält.
Eine Tabelle mit einer JSON Spalte sieht dann z.B. folgendermaßen aus:
CREATE TABLE students (id RAW(16) NOT NULL, date_loaded DATE, student_info CLOB CONSTRAINT ensure_json CHECK (student_info IS JSON));
In diese Tabelle kann nun in die Spalte STUDENT_INFO ein Text im JSON-Format eingefügt ([…] wurde hier als Platzhalter verwendet) werden.
INSERT INTO students
VALUES ( sys_guid(),
sysdate,
‘{ „Matrikelnummer“ : 123564869,
„Name“ : „Otto Mayer“,
[...] }’);
Die Daten können nun über eine ganz einfache Punkt-getrennte-Notation wie man sie von JavaScript kennt ausgelesen werden. Folgende SQL-Query ergibt z.B. das Ergebnis 123564869.
select st.student_info.Matrikelnummer from students st
Es muss folgendes beachtet werden:
Die Schlüsselwerte sind case-sensitive – das gleiche muss in SQL berücksichtigt werden – das Statement oben würde kein Ergebnis liefern wenn statt „Matrikelnummer“ „matrikelnummer“ oder „MATRIKELNUMMER“ geschrieben wird. Sollten Leerzeichen oder Sonderzeichen wir Umlaute im Key sein – was grundsätzlich zu vermeiden ist – dann muss die entsprechende Angabe unter Anführungszeichen gesetzt werden also z.B.:
select st.student_info.”aktuelles Semester” from students st
Sollte es sich um ein verschachteltes Objekt handeln so wird die Punkt-getrennte-Notation einfach auf allen Ebenen angewandt bis man bei dem gewünschten Wert angelangt ist.
Oracle bietet noch drei Funktionen die in SQL oder PL/SQL verwendet werden können die ich hier noch kurz beschreiben will: JSON_VALUE, JSON_QUERY, JSON_TABLE und JSON_EXISTS.
Allgemein nutzen alle JSON-Pfad-Notationen. Diese entspricht der Punkt-getrennten Notation die bereits besprochen wurde, und kann auch Elemente aus Arrays nutzen.
JSON_VALUE
Selektiert einen Wert wie zum Beispiel mit dem Ergebnis „Business English“ bei folgendem Query:
select json_value(st.student_info, '$.Faecher[0]') from students st
JSON_EXISTS
Prüft ob ein bestimmer Schlüssel im JSON existiert. Das erste Query gibt ein Datum zurück bei allen Zeilen, denn das Array hat ein zweites Element (0 ist der Start-Index). Das zweite Query gibt nichts zurück da das Array nur 3 Elemente hat (und [3] das vierte Element abfragt).
select date_loaded from students st where json_exists(student_info, '$.Faecher[1]') select date_loaded from students st where json_exists(student_info, '$.Faecher[3]')
JSON_QUERY
Im Gegensatz zu JSON_VALUE wird hier ein Teilstück des JSON selektiert und nicht nur ein Wert, folgendes Query gibt die Liste der Fächer zurück:
select json_query(student_info, '$[*].Faecher') from students
JSON_TABLE
Diese Funktion dient dazu JSON-Daten in eine virtuelle Tabellenform zu überführen. Sie bietet damit die Möglichkeit im FROM-Teil der Query eingesetzt zu werden und dadurch mehrere Werte auf einmal ohne mehrmaliges Aufrufen von JSON_VALUE oder JSON_QUERY zu selektieren. Das bringt einen Geschwindigkeitsvorteil da die Daten so nur einmal geparst werden und nicht für jeden Funktionsaufruf immer wieder.
select jt.matrikelnummer, jt.fach1, jt.fach2, jt.fach3 from students st, json_table(st.student_info, '$' COLUMNS(matrikelnummer NUMBER PATH '$.Matrikelnummer', fach1 VARCHAR2(240) PATH '$.Faecher[0]', fach2 VARCHAR2(240) PATH '$.Faecher[1]', fach3 VARCHAR2(240) PATH '$.Faecher[2]')) jt
Die neuen JSON Funktionen bieten nützliche Werkzeuge, gerade im Zusammenhang mit APEX, dass durch seine Web-Browser Basis JavaScript massiv nutzt können sich dadurch hilfreiche Vereinfachungen implementieren lassen.
Weitere Infos zu JSON in der Oracle Datenbank finden sie hier:
