12c / 18c / 19c / 23ai
Neben der Eigenschaft als Beschreibungssprache für Datenstrukturen zu dienen, setzt sich json auch immer mehr bei der Datenspeicherung und -Übertragung durch.
Mit der Oracle Datenbank 12c hat auch Oracle einen JSON Parser in SQL integriert.
Ein eigener Datentyp existiert dazu erst ab 21c bzw. 23ai , die Daten können in VARCHAR2,CLOB oder BLOB gespeichert werden.
Es kann ein Constraint auf die Spalte in der Tabelle mit den JSON Daten gelegt werden der überprüft ob das Dokument auch wirklich wohl geformt ist.
Um den Unterschied zwischen traditionellen Datenhandling mit SQL und dem mit JSON aufzuzeigen, legen wir uns zuvor die Inhalte der EMP Tabelle als JSON Strukturen ab.
Eine Tabelle mit einem CLOB Feld für die JSON Daten, diese werden beim Einfügen über eine Constraint auf Gültigkeit geprüft:
CREATE TABLE JSON_EMP ( empno NUMBER(11) , emp_value CLOB CONSTRAINT emp_valid_json CHECK (emp_value IS JSON));
Soll ein BLOB Feld zur Datenspeicherung verwendet werden muss bei den Abfragen mit SQL ein Cast auf Json mit „FORMAT JSON“ erfolgen!
Aus dem Emp Daten das Json Dokument pro Eintrag in der Tabelle erzeugen und dann einfügen:
INSERT INTO JSON_EMP (empno,emp_value) SELECT empno ,'{' ||' "EMPNO":"'||EMPNO||'"' ||' ,"ENAME":"'||ENAME||'"' ||' ,"JOB":"'||JOB||'"' ||' ,"MGR":"'||MGR||'"' ||' ,"SAL":"'||SAL||'"' ||' ,"COMM":"'||COMM||'"' ||' ,"DEPTNO":"'||DEPTNO||'"' ||'}' FROM emp / commit;
Im einfachsten Fall können die Json Elemente direkt in SQL referenziert werden:
SELECT j.emp_value.ENAME, j.emp_value.DEPTNO FROM JSON_EMP j;
Auf die GROSS/klein Schreibung achten! Muss genau zu dem JSON Dokument passen!
ab 12c Release 1 (12.1.0.2)
Abfragen:
Leider steckt aber auch hier der Teufel im Detail …
Wird ein JSON Objekte „zusammen gebaut“ mit mehr als 4000 Zeichen muss der Datentyp CLOB verwendet werden.
.. , KEY 'key1' IS json_serialize(ob.json_rec returning CLOB) .. , KEY 'details' IS json_arrayagg ( ed.details_json returning CLOB).. ...
D.h. in den Oracle JSON Formaten kann der Rückgabe Datenwert angeben werden
Wird ein Wert in json_object wieder um ein JSON Record übergeben, wird dieser JSON Record als Text betrachtet und excaped! Mit der Zusäzlichen Angabe „format json“ kann das Escapen verhindert werden.
SELECT json_object ( .... , KEY 'Contact' IS ob.json_rec format json .. returning CLOB ) FROM table_with_json_records;
Allgemein
Oracle:
Veranstaltungen:
Demo: