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:

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:

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:

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):

Eine Alternative zu JSON_VALUE und JSON_QUERY ist noch die Punktnotation: 

Die funktioniert aber nur, wenn die Tabelle wie oben mit einem Check-Constraint angelegt wurde, der JSON erzwingt. Wenn nicht, bekommt man die Fehlermeldung:

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:

Ü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:

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:

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: