Das JSON-Format (Java-Script Object Notation) erfreut sich zunehmender Beliebtheit, da es im Vergleich zu XML wesentlich weniger Overhead hat. Ausserdem ist es einfacher; es kennt im wesentlichen nur Wertepaare und Arrays. Diese können hierarchisch aufgebaut sein und entsprechen der JS-Notation.
Ein Beispiel:
{"sessions":
[
{"sessionId":"44949505148386823",
"subject":"JSON in der Praxis",
"startTime":"2015-11-02T09:30:00.001Z",
"endTime":"2015-11-02T10:30:00.000Z",
"attendees": ["Rudi Ratlos","Maria Stuart"],
"organizer":{"firstName":"Max","lastName":"Mustermann"}
},
{"sessionId":"115191805493052423",
"subject":"Die Zukunft der Hexerei",
"startTime":"2015-11-11T14:00:00.000Z",
"endTime":"2015-11-11T15:30:00.000Z",
"attendees":["Harry Potter","Ron Weasley"],
"organizer":{"firstName":"Hermine","lastName":"Granger"}
}
]
}
Der "Wert" zu "sessions" ist hier ein Array von 2 Objekten. Jedes dieser Objekte hat die skalaren Werte sessionId, subject, startTime und endTime sowie das Array attendees. organizer wiederum stellt ein untergeordnetes Objekt dar.
Seit Version 12.1.0.2 unterstützt nun auch Oracle dieses Format nativ mit speziellen Operatoren und Funktionen. In früheren Versionen gibt es zumindest die Möglichkeit, das Package APEX_JSON zu nutzen, das Bestandteil von APEX 5 ist.
Für die Speicherung in der Datenbank gibt es keinen eigenen Datentyp, je nach Größe bieten sich VARCHAR2 oder CLOB an, aber mit Hilfe des Operators IS JSON kann die Einhaltung des Formats über einen Constraint erzwungen werden:
CREATE TABLE JSON_TEST
(ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
JSON_DATA CLOB,
CONSTRAINT JSON_TEST_CHK CHECK (JSON_DATA IS JSON)
)
/
-- Eintragen der beiden Datensätze aus obigem JSON-Dokument:
insert into JSON_TEST
(JSON_DATA)
values
('{"sessionId":"44949505148386823",
"subject":"JSON in der Praxis",
"startTime":"2015-11-02T09:30:00.001Z",
"endTime":"2015-11-02T10:30:00.000Z",
"attendees": ["Rudi Ratlos","Maria Stuart"],
"organizer":{"firstName":"Max","lastName":"Mustermann"}
}')
/
insert into JSON_TEST
(JSON_DATA)
values
('{"sessionId":"115191805493052423",
"subject":"Die Zukunft der Hexerei",
"startTime":"2015-11-11T14:00:00.000Z",
"endTime":"2015-11-11T15:30:00.000Z",
"attendees":["Harry Potter","Ron Weasley"],
"organizer":{"firstName":"Hermine","lastName":"Granger"}
}')
/
insert into JSON_TEST
(JSON_DATA)
values
('"sessionId":"115191805493052423"')
/
ORA-02290: CHECK-Constraint (DUMMY.JSON_TEST_CHK) verletzt
Das Gegenstück zu IS JSON ist IS NOT JSON. Beide Operatoren kann man z.B. auch in der WHERE-Klausel nutzen. Das macht natürlich nur dann Sinn, wenn eine Spalte auch normale Strings enthalten kann. (Auf bestimmte Pfade kann man übrigens über json_exists abprüfen.)
JSON_VALUE UND JSON_QUERY
Um JSON auszuwerten, muss man in aller Regel mit Pfadangaben arbeiten, ähnlich wie bei XML. Für skalare Werte verwendet man JSON_VALUE, für nicht skalare JSON_QUERY. Dabei durchwandert man die Hierarchie über Punktnotation. Ausgangspunkt ist immer das JSON-Dokument selber, das durch $ gekennzeichnet wird. Standardmäßig wird der Wert als String zurückgegeben; über RETURNIN kann man aber NUMBER als Datentyp angeben:
SELECT JSON_value (JSON_DATA, '$.subject') subject FROM JSON_TEST
/
SUBJECT
------------------------------
JSON in der Praxis
Die Zukunft der Hexerei
2 rows selected.
SELECT JSON_value (JSON_DATA, '$.sessionId' RETURNING NUMBER) sessionId FROM JSON_TEST
/
SESSIONID
---------------------
44949505148386823
115191805493052423
SELECT JSON_QUERY (JSON_DATA, '$.attendees') attendees
FROM JSON_TEST
/
ATTENDEES
----------------------------------------
["Rudi Ratlos","Maria Stuart"]
["Harry Potter","Ron Weasley"]
Das Standardverhalten von JSON ist sehr fehlertolerant; deshalb kann ein leerer Wert auch bedeuten, dass die Abfrage fehlerhaft ist. Will man bei falschen Pfadangaben z. B. einen Fehler zurückbekommen, muss man das explizit mit ERROR ON ERROR anfordern (der Default ist NULL ON ERROR):
SELECT JSON_value (JSON_DATA, '$.attendees') attendees FROM JSON_TEST
/
ATTENDEES
---------------------------------------------------------------------------
2 rows selected.
SELECT JSON_value (JSON_DATA, '$.attendees' ERROR ON ERROR) attendees
FROM JSON_TEST
/
Error at line 1
ORA-40456: JSON_VALUE wurde als nicht-skalarer Wert ausgewertet
Eine Alternative zu JSON_VALUE und JSON_QUERY ist noch die Punktnotation:
SELECT t.json_data.subject , t.json_data.attendees
FROM JSON_TEST t
/
SUBJECT ATTENDEES
------------------------------ ----------------------------------------
JSON in der Praxis ["Rudi Ratlos","Maria Stuart"]
Die Zukunft der Hexerei ["Harry Potter","Ron Weasley"]
Die funktioniert aber nur, wenn die Tabelle wie oben mit einem Check-Constraint angelegt wurde, der JSON erzwingt. Wenn nicht, bekommt man die Fehlermeldung:
ORA-00904: "T"."JSON_DATA"."ATTENDEES": ungültiger Bezeichner
JSON_TABLE
JSON_TABLE ist für die Verarbeitung von JSON die umfassendste und interessanteste Funktion. Analog zu XMLTABLE für XML ermöglicht JSON_TABLE die relationale Darstellung der Inhalte:
SELECT sessionid, thema, vorname, nachname
FROM JSON_TEST ,
json_table(json_data, '$'
columns (sessionid NUMBER path '$.sessionId',
thema VARCHAR2(30) path '$.subject',
vorname VARCHAR2(10) path '$.organizer.firstName',
nachname VARCHAR2(10) path '$.organizer.lastName'
)
)
/
SESSIONID THEMA VORNAME NACHNAME
------------------ ------------------------- ---------- ----------
44949505148386823 JSON in der Praxis Max Mustermann
115191805493052423 Die Zukunft der Hexerei Hermine Granger
Übergeben werden dabei
- Das JSON-Dokument (bzw. die Spalte mit dem Inhalt)
- Der Ausgangspunkt der Pfadangaben in der columns-Klausel
- Die Liste der zu extrahierenden Werte über die columns-Klausel
Auch auf Inhalte eines Arrays kann zugegriffen werden, indem man den Index mitgibt. Die Zählung beginnt bei 0. Ein nicht vorhandener Index führt auch hier standardmäßig nicht zu einem Fehler:
SELECT thema, teilnehmer_1, teilnehmer_2
FROM JSON_TEST ,
json_table(json_data, '$'
columns (thema VARCHAR2(30) path '$.subject',
teilnehmer_1 VARCHAR2(30) path '$.attendees[0]',
teilnehmer_2 VARCHAR2(30) path '$.attendees[1]'
)
)
/
THEMA TEILNEHMER_1 TEILNEHMER_2
------------------------ --------------- ---------------
JSON in der Praxis Rudi Ratlos Maria Stuart
Die Zukunft der Hexerei Harry Potter Ron Weasley
SELECT thema, teilnehmer_1, teilnehmer_3
FROM JSON_TEST ,
json_table(json_data, '$'
columns (thema VARCHAR2(30) path '$.subject',
teilnehmer_1 VARCHAR2(30) path '$.attendees[0]',
teilnehmer_3 VARCHAR2(30) path '$.attendees[2]'
)
)
/
THEMA TEILNEHMER_1 TEILNEHMER_3
------------------------- --------------- ---------------
JSON in der Praxis Rudi Ratlos
Die Zukunft der Hexerei Harry Potter
Auch hier kann nach der Pfadangabe ergänzt werden ERROR ON ERROR. Dann wird die zweite Abfrage mit einem Fehler quittiert.
JSON IN PL/SQL
Wesentlich interessanter als in Tabellen ist JSON jedoch als Austauschformat, z. B. bei Webservices (application/json). Auch hier kann man sehr gut mit JSON_TABLE arbeiten.
Angenommen, Sie rufen einen entsprechenden Webservice über PL/SQL auf, der die Daten nicht in Form einzelner Datensätze liefert sondern im folgenden Format und Sie wollen die Datensätze in normale relationale Tabellen eintragen:
{"sessions":
[
{"sessionId":"44949505148386823",
"subject":"JSON in der Praxis",
"startTime":"2015-11-02T09:30:00.001Z",
"endTime":"2015-11-02T10:30:00.000Z",
"attendees":
[
{"firstName":"Rudi","lastName":"Ratlos","email":"rudi.ratlos@firma.de"},
{"firstName":"Maria","lastName":"Stuart","email":"maria.stuart@firma.de'"}
],
"organizer":"firstName":"Max","lastName":"Mustermann"}
},
{"sessionId":"115191805493052423",
"subject":"JSON in der Praxis",
"startTime":"2015-11-02T09:30:00.001Z",
"endTime":"2015-11-02T10:30:00.000Z",
"attendees":
[
{"firstName":"Harry","lastName":"Potter","email":"harry.potter@hogwarts.uk
{"firstName":"Ron","lastName":"Weasley","email":"ron.easley@ogwarts.k"}
],
"organizer":{"firstName":"Hermine","lastName":"Granger"}
}
....
]
}
Zunächst einmal können Sie den http-Response in eine VARCHAR2- oder CLOB-Variable einlesen (normalerweise werden mehr als zwei Datensätze übertragen), die dann Ihr JSON-Dokument darstellt. Dann brauchen Sie nur noch den richtigen Ausgangspunkt für den Pfad, um zunächst einmal die Daten der Sessions auszulesen. Da Sie alle Sessions haben wollen, ist das $.sessions[*] . Der Stern (*) bedeutet "alle".
Und natürlich wollen Sie auch die Teilnehmerdaten auslesen. Hier kommt dann nested path ins Spiel. Damit können Sie 1:n-Beziehungen auflösen. Eine Prozedur zur Verarbeitung könnte dann so aussehen:
PROCEDURE process_data_json(p_data IN CLOB)
IS
CURSOR c_cur IS
SELECT jt.* FROM
json_table(p_data,
'$.sessions[*]'
COLUMNS (sessionId VARCHAR2(50) path '$.sessionId',
subject VARCHAR2(50) path '$.subject',
startTime VARCHAR2(50) path '$.startTime',
endTime VARCHAR2(50) path '$.endTime',
nachname VARCHAR2(50) path '$.organizer.lastName',
vorname VARCHAR2(50) path '$.organizer.firstName'
)
) jt;
CURSOR c_att IS
SELECT jt.* FROM
json_table (p_data,
'$.sessions[*]'
COLUMNS (sessionId VARCH