Als PDF Downloaden!

Tipps & Tricks: Februar 2017
Bereich:
SQL, PL/SQL
Erstellung:
02/2017 HA
Versionsinfo:
12.1.0.2
Letzte Überarbeitung:
02/2017 HA
Oracle und JSON
Das JSON-Format (JavaScript 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 Javascript-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)
)
/
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 7
-- 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 RETURNING 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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 7
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"]
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 7
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 7
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":"[email protected]"},
{"firstName":"Maria","lastName":"Stuart","email":"[email protected]"}
],
"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":"[email protected]"},
{"firstName":"Ron","lastName":"Weasley","email":"[email protected]"}
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 7
],
"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
VARCHAR2(50) path '$.sessionId',
nested path '$.attendees[*]'
COLUMNS (vorname
VARCHAR2(100) path '$.firstName',
nachname VARCHAR2(100) path '$.lastName',
email
VARCHAR2(50) path
'$.email'
)
)
) jt;
BEGIN
FOR r IN c_cur LOOP
Verarbeitung der Daten
END LOOP;
FOR r IN c_att LOOP
Verarbeitung der Daten
END LOOP;
END;
/
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 6 von 7
Wir haben Sie neugierig gemacht, und Sie möchten wissen, was alles neu ist in 12c?
Dann buchen Sie am besten gleichen einen Platz in einer unserer Neuerungen 12c-Schulungen. Hier erhalten Sie
einen Überblick über die wichtigsten Neuerungen und können diese an einer Übungsdatenbank gleich selbst
ausprobieren. Wir freuen uns auf Sie :)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 7 von 7