Oracle und JSON

05.
Februar
2017
Veröffentlicht von: Hildegard Asenbauer

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.

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 Opens external link in current windowAPEX_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      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;
/

 

21c Update

Mit Version 21c hat Oracle mittlerweile einen eigenen Datentyp namens JSON eingeführt.

 

SQL SQL*Plus JSON

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.