Blog Name
  • Freitag, 16. September 2016

JSON und die Oracle Datenbank

JSON Daten werden häufig in NoSQL oder anderen speziellen Datenbanken gespeichert. Diese erlauben zwar die Speicherung und den Zugriff der Daten, aber weisen kein vergleichbares Konsistenzmodell, Transaktionsmodell und andere Standardfunktionalitäten von relationalen Datenbanken auf. Neu in Oracle Database 12c (12.1.0.2) ist die Möglichkeit auf JSON Daten mit Standard Datenbankmitteln zuzugreifen. Die Idee dahinter ist, nicht nur einen einfachen Textstring zu speichern und auf diesen zuzugreifen, was schon immer in jedem Release möglich war, sondern auch spezielle JSON Pfad Zugriffe oder JSON Validierungen zu ermöglichen, um nur einige Features zu nennen.

Zusätzlich stehen alle relationalen Datenbank Features bei der Nutzung von JSON zur Verfügung, wie z.B. Indizes, Transaktionshandling, gemischte Abfragen, relationale Views, External Tables usw. Auf JSON Daten kann man somit wie auf alle Daten in der Datenbank zugreifen auch über OCI, .NET und JDBC. Im folgenden Blogeintrag werden grundlegende Eigenschaften kurz aufgezeigt und an Beispielen demonstriert. Die Grundlagen zu JSON werden dabei allerdings vorausgesetzt.

JSON in der Datenbank: Generelles

Bevor man über den Zugriff von JSON Daten in der Datenbank nachdenkt, stellt man sich die Frage, wie kann man überhaupt JSON in der Datenbank zur Verfügung stellen. Wie werden die Daten gespeichert? Eines vorweg: Es gibt keinen speziellen JSON Datentyp - ganz im Unterschied zu XML in der Datenbank (auch XMLDB). JSON kann also einfach in Spalten vom Datentyp VARCHAR2 oder LOB gespeichert werden. Mit der Bedingung IS JSON kann man die Daten dann zusätzlich validieren - auf Wohlgeformtheit oder auf die Art der Syntax Verwendung (STRICT oder LAX).

Starten wir mit einem einfachen Beispiel: Die JSON Daten liegen in einem DMP Format vor und können über die External Table Schnittstelle der Datenbank zur Verfügung gestellt werden. Folgender Code demonstriert den Zugriff über die External Table JSON_DUMP_FILE_CONTENTS.

Hinweis: Diese Beispieldaten stehen übrigens in $ORACLE_HOME/demo/schema/order_entry zur Verfügung.

SQL> connect scott@pdb1
Enter password:
Connected.
SQL> select * from all_directories;

OWNER     DIRECTORY_NAME DIRECTORY_PATH                                                   ORIGIN_CON_ID
--------- -------------- ---------------------------------------------------------------- -------------
SYS       ORDER_DIR      /u01/app/oracle/product/12.1.0/dbhome_1/demo/schema/order_entry              3

SQL> create table json_dump_file_contents (json_document CLOB)
     ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_dir
                            ACCESS PARAMETERS (RECORDS DELIMITED BY 0x'0A'
                            BADFILE 'JSONDumpFile.bad'
                            LOGFILE 'JSONDumpFile.log'
                            FIELDS (json_document CHAR(5000)))
                         LOCATION ('PurchaseOrders.dmp'))
     REJECT LIMIT UNLIMITED; 
Table created.

Nun sind die üblichen Zugriffe über die External Table JSON_DUMP_FILE_CONTENTS möglich. Beispielsweise könnte man mit einem MERGE Kommando oder mit JOIN Operationen (auf bestehende relationale Tabellen) die Daten weiterverarbeiten, laden etc.

SQL> select count(*) from json_dump_file_contents;

  COUNT(*)
----------
     10000

SQL> set long 10000 pagesize 1000
SQL> select * from json_dump_file_contents where rownum=1;

JSON_DOCUMENT
--------------------------------------------------------------------------------
{"PONumber":1,"Reference":"MSULLIVA-20141102","Requestor":"Martha Sullivan","Use
r":"MSULLIVA","CostCenter":"A50","ShippingInstructions":{"name":"Martha Sullivan
","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":
"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Off
ice","number":"979-555-6598"}]},"Special Instructions":"Surface Mail","LineItems
":[{"ItemNumber":1,"Part":{"Description":"Run Lola Run","UnitPrice":19.95,"UPCCo
de":43396040144},"Quantity":7.0},{"ItemNumber":2,"Part":{"Description":"Felicia'
s Journey","UnitPrice":19.95,"UPCCode":12236101345},"Quantity":1.0},{"ItemNumber
":3,"Part":{"Description":"Lost and Found","UnitPrice":19.95,"UPCCode":853917563
23},"Quantity":8.0},{"ItemNumber":4,"Part":{"Description":"Karaoke: Rock & Roll
Hits of 80's & 90's 8","UnitPrice":19.95,"UPCCode":13023009592},"Quantity":8.0},
{"ItemNumber":5,"Part":{"Description":"Theremin: An Electronic Odyssey","UnitPri
ce":19.95,"UPCCode":27616864451},"Quantity":8.0}]}

Zugriff auf JSON Daten: Funktionen, Bedingungen, Punktnotation

Um eine einfache Verwendung von JSON Informationen in der Datenbank zu gewährleisten, sind spezielle SQL Funktionen und Operatoren eingeführt worden. Möchte man beispielsweise mit einer einfachen Punktnotation (englisch dot notation) auf die Daten zugreifen, ist die Verwendung des Check Constraints IS JSON und die Verwendung eines Tabellen Alias in der Abfrage erforderlich. Demonstrieren wir dies an einem einfachen Beispiel.

SQL> create table json_tab (id number generated as identity, 
                            json_document clob constraint ensure_json CHECK (json_document IS JSON));
Table created.

SQL> insert into json_tab (json_document) select json_document from json_dump_file_contents;
9999 rows created.

SQL> select min(j.json_document."PONumber") min_po, 
            max(j.json_document."PONumber") max_po 
     from json_tab j;

MIN_PO     MAX_PO
---------- ----------
1          9999

Folgende Beispiele demonstrieren einer weitere Verwendung der Punktnotation.

  • j.json_document.LineItems[1] addressiert das zweite Element aus dem Feld LineItems (Start ist bei 0)
  • j.json_document.LineItems[*] steht für alle Elemente aus dem Feld LineItems

Tabellen mit dem Check Constraint IS JSON werden übrigens im Data Dictionary in der neuen View *_JSON_COLUMNS gelistet.

SQL> select * from user_json_columns;

TABLE_NAME           COLUMN_NAME          FORMAT    DATA_TYPE
-------------------- -------------------- --------- -------------
JSON_TAB             JSON_DOCUMENT        TEXT      CLOB

SQL> select index_name from user_indexes where table_name='JSON_TAB';

INDEX_NAME
--------------------------------------------------------------------------------
SYS_IL0000097563C00002$$

Eine genaue Erläuterung findet sich im Database PL/SQL Packages and Types Reference im Kapitel JSON Data Structures. Weitere Beispiele sind auf Github zu finden.

View Erzeugen und Textsuche ganz einfach mit der neuen Struktur Data Guide

Ähnlich wie bei der Verwendung von XML Dokumenten stehen neue Funktionen zur speziellen Nutzung von JSON Zugriffe zur Verfügung. Folgende Funktionen und Bedingungen können bei Abfragen auf JSON Dokumente verwendet werden:

  • IS JSON/IS NOT JSON: Testet auf Wohlgeformtheit bzw. auf die Art der Nutzung (meist als Check Constraint)
  • JSON_EXISTS: Testet in einer Bedingung auf Existenz eines speziellen Wertes
  • JSON_VALUE: Selektiert einen skalaren Wert
  • JSON_QUERY: Selektiert ein JSON Fragment - normalerweise ein Objekt oder Feld
  • JSON_TABLE: Projiziert JSON Daten in ein relationales Format über eine virtuelle Tabelle, ähnlich einer relationalen Inline View.
  • JSON_TEXTCONTAINS: Testet ob ein bestimmter Characterstring in den JSON Eigenschaftswerten (property value) existiert.

Um ein Beispiel für die Verwendung von JSON_VALUE zu geben, wandeln wir das obige Beispiel um.

SQL> select min(json_value (json_document, '$."PONumber"' Returning number)) min_po, 
     max(json_value (json_document, '$."PONumber"' Returning number)) max_po
     from json_tab;

Hinweis: Einen Überblick über die JSON Ausdrücke können Sie in im Abschnitt "Oracle JSON Basic Path Expression Syntax" bekommen.

Arbeitet man lieber mit JSON Ausdrücken oder sind die Abfragen komplexer, dann kann man zur Funktion JSON_VALUE greifen. Die Ausführungspläne sind dabei gleich. Weitere Beispiele zeigen die einfache Verwendung von JSON_VALUE.

SQL> set linesize 80 long 1000 pages 100
SQL> select json_document
     from json_tab
     where json_value(json_document,'$.PONumber' returning number)=1000;

JSON_DOCUMENT
--------------------------------------------------------------------------------
{"PONumber":1000,"Reference":"CJOHNSON-20141117","Requestor":"Charles Johnson","
User":"CJOHNSON","CostCenter":"A80","ShippingInstructions":{"name":"Charles John
son","Address":{"street":"Magdalen Centre, The Isis Science Park","city":"Oxford
","county":"Oxon.","postcode":"OX9 9ZB","country":"United Kingdom"},"Phone":[{"t
ype":"Office","number":"66-555-3120"}]},"Special Instructions":"Priority Overnig
ht","LineItems":[{"ItemNumber":1,"Part":{"Description":"Cyndi Lauper: Twelve Dea
dly Cyns...and Then Some","UnitPrice":19.95,"UPCCode":74644919691},"Quantity":2.
0},{"ItemNumber":2,"Part":{"Description":"Deep Red","UnitPrice":19.95,"UPCCode":
13131106992},"Quantity":3.0},{"ItemNumber":3,"Part":{"Description":"War Files","
UnitPrice":27.95,"UPCCode":56775061296},"Quantity":8.0},{"ItemNumber":4,"Part":{
"Description":"An Elephant Called Slowly","UnitPrice":19.95,"UPCCode":1313113719
4},"Quantity":7.0}]}

SQL> select json_value(json_document,'$.ShippingInstructions.Phone[0].type')
     from json_tab
     where json_value(json_document,'$.PONumber' returning number)=1000;

JSON_VALUE(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE[0].TYPE')
--------------------------------------------------------------------------------
Office

Muss man Fragmente selektieren - wie im Falle von Arrays, wird man auf JSON_QUERY zurückgreifen.

SQL> select json_query(json_document,'$.ShippingInstructions')
     from json_tab
     where json_value(json_document,'$.PONumber' returning number)=1000;

JSON_QUERY(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS')
--------------------------------------------------------------------------------
{"name":"Charles Johnson","Address":{"street":"Magdalen Centre, The Isis Science
 Park","city":"Oxford","county":"Oxon.","postcode":"OX9 9ZB","country":"United K
ingdom"},"Phone":[{"type":"Office","number":"66-555-3120"}]}

Relationale Sichtweise: Projektionen

Wie kann man nun JSON Daten in eine relationale Form projizieren? Die SQL Funktion JSON_TABLE überführt die JSON Daten in relationale Zeilen und Spalten einer virtuellen Tabelle. Folgendes Beispiel zeigt die Funktionsweise. Verwendet wird die Tabelle JSON_TAB mit der Spalte JSON_DOCUMENT von oben. Als Ergebnis sollen 3 relationale Spalten zur Verfügung stehen - nämlich REQUESTOR (mit VARCHAR2 (32)), ADRESSE (im JSON Format) und die Spalte SPECIAL (mit VARCHAR2(20)). In der FROM Klausel wird dazu die Tabelle JSON_TAB und die SQL Funktion JSON_TABLE verwendet. Die Funktion JSON_TABLE benötigt als erstes Argument die Spalte mit den JSON Daten, einen JSON Ausdruck (hier $) und das Schlüsselwort COLUMNS, das das Mapping auf die relationalen Spalten und die entsprechenden Datentypen vornimmt.

SQL> create or replace view json_view as 
           select jt. ponumber, jt.requestor, jt.adresse, jt.special 
           from json_tab,
           json_table (json_document, '$'
                              COLUMNS (
          ponumber number PATH '$.PONumber',
          requestor varchar2(32 CHAR) PATH '$.Requestor',
          special varchar2(10) PATH '$."Special Instructions"',
          adresse varchar2(400) FORMAT JSON PATH '$.ShippingInstructions.Address')) jt  
     where json_value(json_document,'$.PONumber' returning number error on error) < 5000;

View created.

SQL> desc json_view
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PONUMBER                                           NUMBER
 REQUESTOR                                          VARCHAR2(128)
 ADRESSE                                            VARCHAR2(400)
 SPECIAL                                            VARCHAR2(10)

Die Funktion JSON_TABLE generiert nun für jeden JSON Wert, auf den das JSON Pattern zutrifft, eine Zeile. Die relationale View JSON_VIEW projiziert die gewünschte Sichtweise. Die Filterbedingung reduziert dabei die Zeilenzahl auf Dokumente mit "PONumber" kleiner als 5000.

Nun fragen wir die View JSON_VIEW mit Standard Datenbankmitteln ab.

SQL> select * from json_view where special='Air Mail' and requestor='Karen Partners';

R PONUMBER REQUESTOR
---------- ------------------
ADRESSE
--------------------------------------------------------------------------------
SPECIAL
----------
      3475 Karen Partners
{"street":"Magdalen Centre, The Isis Science Park","city":"Oxford","county":"Oxo
n.","postcode":"OX9 9ZB","country":"United Kingdom"}
Air Mail
...

Sogar Felder (Nested Tables) können mit JSON_TABLE angesprochen werden. Dies zu zeigen würde aber zu weit führen. Im SQL Handbuch (siehe Linke unten) findet man gute Beispiele dazu.

JSON Daten indizieren?

Wie man sich vorstellen kann, eignen sich die neuen Konstrukte JSON_VALUE und JSON_EXISTS gut dazu, um einen Function Based Index auf einer Spalte mit JSON Inhalten zu erzeugen. Man verwendet die entsprechenden JSON Pfade um damit einen skalaren Werten zu erzeugen. Dabei können auch Bitmap Indizes angelegt werden. Folgende einfache Beispiele demonstrieren die Verwendung.

SQL> create index ponumber_idx on json_tab 
     (json_value(json_document, '$."PONumber"' returning number error on error));
Index created

SQL> col index_name format a30
SQL> select index_name, index_type, distinct_keys from user_indexes where table_name='JSON_TAB';

INDEX_NAME                     INDEX_TYPE                  DISTINCT_KEYS
------------------------------ --------------------------- -------------
SYS_IL0000282340C00001$$       LOB
PONUMBER_IDX                   FUNCTION-BASED NORMAL                9999
...

Die Funktion JSON_VALUE erzeugt mormalerweise einen VARCHAR2 Wert. Mit einer RETURNING Klausel stellen wir in unserem Beispiel sicher, dass der Datentyp NUMBER verwendet wird. Die "ERROR ON ERROR" Klausel bricht die Index Erstellung mit einem Fehler ab, wenn kein numerischer Wert oder überhaupt kein Wert in "PONumber" verzeichnet ist.

Nun führen wir einige Abfragen durch um die Verwendung des Index zu demonstrieren. Mit der Überprüfung des Ausführungsplans können wir die Verwendung verifizieren. Im ersten Beispiel werden Informationen der Tabelle JSON_TAB abgefragt.

SQL> set linesize 250
SQL> set autotrace traceonly explain
SQl> select * from json_tab 
     where json_value(json_document,'$.PONumber' returning number) = 1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3409213313
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |   100 |   193K|    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| JSON_TAB     |   100 |   193K|    14   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PONUMBER_IDX |    40 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(JSON_VALUE("JSON_DOCUMENT" FORMAT JSON , '$."PONumber"' RETURNING NUMBER
              ERROR ON ERROR)=1000)
Im nächsten Beispiel wird die vorher angelegte View JSON_VIEW benutzt. Auch hier findet der Index PONUMBER_IDX eine Verwendung.
SQL> select count(*) from json_view where special='Air Mail';

Execution Plan
----------------------------------------------------------
Plan hash value: 1805114248

------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |     1 |  1981 | 16895   (6)| 00:00:04 |
|   1 |  SORT AGGREGATE                       |              |     1 |  1981 |            |          |
|   2 |   NESTED LOOPS                        |              | 40840 |    77M| 16895   (6)| 00:00:04 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| JSON_TAB     |   500 |   966K|    30   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | PONUMBER_IDX |    90 |       |     2   (0)| 00:00:01 |
|*  5 |    JSONTABLE EVALUATION               |              |       |       |            |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(JSON_VALUE("JSON_DOCUMENT" FORMAT JSON , '$."PONumber"' RETURNING NUMBER ERROR
              ON ERROR)<5000)
   5 - filter("P"."SPECIAL"='Air Mail')

Noch einfacher funktioniert die Indizierung mit der speziellen Bedingung JSON_TEXTCONTAINS, die im obigen Abschnitt kurz beschrieben wurde. Kennt man zum Beispiel nur den Ausdruck und nicht den dazugehörigen Pfad, kann man trotzdem über eine recht allgemeine Abfrage zum Ziel kommen. Im Datenbank Release 12.1.0.2 wird dies mit einem speziellen Oracle Text Index gelöst, der auf die Spalte mit den JSON Inhalten angelegt wird. Die Spalte muss dabei vom Datentyp VARCHAR2, CLOB oder BLOB sein. Dazu ist folgende Syntax erforderlich.

SQL> create index ix on json_tab(json_document)
     indextype is CTXSYS.CONTEXT
     parameters ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Nun starten wir zwei Suchabfragen, die sich im Filter unterscheiden. In den Ausführungsplänen ist gleich zu erkennen, dass der Text Index (siehe Operation Domain Index) verwendet wird. Im ersten Fall wird die Anzahl der Einträge abgefragt, die mit einem "zipCode" versehen waren.

SQL> set autotrace on explain
SQL> select count(*) from json_tab 
     where json_exists(json_document, '$.ShippingInstructions.Address.zipCode');

  COUNT(*)
----------
      6369


Execution Plan
-------------------------------------------------------------------------
Plan hash value: 2114225437
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |  1978 |    38   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |  1978 |            |          |
|*  2 |   DOMAIN INDEX   | IX   |   166 |   320K|    38   (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'HASPATH(/S
              hippingInstructions/Address/zipCode)')>0)

Im zweiten Beispiel wird die größte "PONumber" von "CostCenter" A40 gesucht - auch hier wird automatisch der Text Index verwendet.

SQL> select max(json_value (json_document, '$.PONumber')) max_po 
     from json_tab where json_textcontains(json_document, '$.CostCenter', 'A40');
MAX_PO
--------------------------------------------------------------------------------
9211

Execution Plan
-----------------------------------------------------------------------------------------
Plan hash value: 2238551180
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |  1978 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |          |     1 |  1978 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| JSON_TAB |    34 | 67252 |    36   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | IX       |       |       |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'A40
              INPATH(/CostCenter)')>0)


Hinweis: Oracle Text ist eine in die Datenbank integrierte Volltextrecherche, die in allen Datenbankeditionen enthalten ist und normalerweise ohne weitere Installation direkt zur Verfügung steht. Man kann also in einem "normalen" Datenbankschema sofort den Oracle Text ohne weitere Konfiguration verwenden. Mehr Informationen zu JSON und Oracle Text oder Oracle Text Informationen im Allgemeinen finden Sie auf unserem Oracle Text Blog (siehe unten).

Schlußbemerkungen

JSON in der Datenbank ist ein integraler Bestandteil der Oracle Datenbank und in allen Ausprägungen der Datenbank wie zum Beispiel RAC, Single Instanz, Non CDB, CDB usw. nutzbar. Die Schnittstellen sind dabei ganz einfach zu bedienen; keine besonderen Voraussetzungen oder Privilegien um JSON Funktionen zu verwenden sind erforderlich. Wenn man schon mit XML in der Datenbank gearbeitet hat, wird man feststellen, dass einige neue Operatoren und Funktionen eingeführt worden sind, die eine gewisse Ähnlichkeit zu den Funktionen der XMLDB aufweisen.

Auch wenn es nicht explizit erwähnt wurde: es gibt keine Einschränkungen bei Abfragen auf gemischten Daten - also JSON und relationale Daten. Man kann also beispielsweise Join Operationen auf strukturierte Daten und JSON Daten durchführen. Einzige Voraussetzung ist der Zugriff auf eine Oracle Datenbank ab der Version 12.1.0.2. Um sicherzustellen, dass alle Funktionen und Performance Features im ersten Release zur Verfügung stehen, wird die Implementierung von JSON Patches (Link siehe unten) empfohlen.

Es ist zu erwarten, dass es einige interessante Erweiterungen im JSON Umfeld in zukünftigen Releases geben wird. Beispielsweise ist mit einer Erweiterung im PL/SQL und DWH Umfeld oder mit einer Vereinfachung der Suchmöglichkeiten zu rechnen. Auch Möglichkeiten der Generierung von JSON Daten aus der Datenbank werden sicherlich angedacht, um nur einige Beispiele zu nennen. Sobald das neue Datenbankrelease zur Verfügung steht, werden wir in unserem Blog darüber ausführlich berichten.

Hinweis zur Lizenzierung

Die JSON Funktionen in der Datenbank sind in allen Editionen (Standard und Enterprise) und Cloudangeboten der Datenbank ab Oracle Database 12c verfügbar. Keine zusätzliche Lizenzierung ist erforderlich.

Weitere Informationen

 

Zurück zum Anfang des Artikels

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services