JSON-Daten in der Oracle-Datenbank
Trotz der Diskussion um NoSQL- und Big Data-Alternativen zu den klassischen relationalen Datenbanksystemen gilt das Prinzip "All your Data" in der Oracle-Datenbank immer noch und speziell auch für die Datenbankversion 12.2. Besonders interessant ist dabei das kompakte und einfach zu lesende JSON-Format, das immer häufiger Anwendung im Datenaustausch findet.
Aber wo genau liegen die Vorteile bei der Speicherung in der Oracle-Datenbank? Die kurze Antwort darauf lautet: Man erhält eine große Anwendungsvielfalt durch mitgelieferte Funktionen und Operatoren und hohe Performance durch Einsatz von Standardmitteln wie Indizes oder Parallelisierung. Vorgefertigte Strukturen wie spezielle Indizes, Operatoren und Funktionen erleichtern dabei nicht nur das performante und schnelle Extrahieren von Informationen aus einem JSON-Dokument, sondern auch die Suchanfragen in JSON-Daten. Besonders in Kombination mit der Abfrage von relationalen Stammdaten ist die Speicherung von JSON-Daten innerhalb der Datenbank alternativlos.
Es ist keine zusätzliche Installation von Software erforderlich. Ab der Datenbankversion 12.1 (genauer 12.1.0.2) gibt es eine JSON-Unterstützung in allen Cloud- und Softwareinstallationen der Datenbank. Mit der Datenbankversion 12.2 sind dabei interessante Erweiterungen eingeführt worden.
Speicherung von JSON-Daten in der Oracle-Datenbank
Möchte man JSON-Daten innerhalb der Datenbank speichern, definiert man einfach eine Datenbankspalte mit einem beliebigen Datentyp für Textstrings wie zum Beispiel der Datentyp VARCHAR2 oder einen Large Objects-Datentyp. Es gibt keinen speziellen JSON-Datentyp. Mit der Bedingung IS JSON kann zusätzlich der Inhalt – auf Wohlgeformtheit oder auf die Art der Syntaxverwendung (STRICT oder LAX) – geprüft werden.
SQL> create table json_tab (id number generated as identity,
json_document clob constraint ensure_json check (json_document IS JSON));
Table created.Im Beispiel wird eine Tabelle JSON_TAB erzeugt, die aus zwei Spalten besteht – ID, die eine eindeutige Sequenznummer enthält und die Spalte JSON_DOCUMENT für den Inhalt von JSON-Daten.
Liegen die Daten außerhalb der Datenbank in Dateien vor und möchte man diese mit Informationen aus relationalen Tabellenspalten der Datenbank kombinieren, bieten sich externe Tabellen als Schnittstelle an. Mithilfe einer Loader-Spezifikation wird das Dateiformat beschrieben, wodurch die Datenbank die Inhalte wie eine Tabelle verfügbar macht. Natürlich können solche Tabellen nur gelesen und nicht verändert werden. Eine Standardarchivierung von JSON-Daten ist dabei für die Verwendung ausreichend. JSON_DIR ist ein logisches Directory; in PurchaseOrders.dmp sind archivierte JSON-Daten gespeichert. Jede Zeile der Datei enthält dabei ein einzelnes JSON-Dokument, das als JSON-Objekt dargestellt wird. Möchte man das Ganze selbst ausprobieren, kann man die Datei im Oracle-Software-Verzeichnis $ORACLE_HOME/demo/schema/order_entry finden.
SQL> create table json_dump_file_contents (json_document CLOB)
organization external (type oracle_loader default directory json_dir
access parameters (RECORDS DELIMITED BY 0x'0A'
fields (json_document CHAR(5000)))
location ('PurchaseOrders.dmp'))
reject limit unlimited;
Table created.Übrigens kann man die Angabe des logischen Verzeichnisses (hier "DEFAULT DIRECTORY json_dir") in 12.2 auch weglassen und erst später beim SELECT dynamisch mitangeben.
select count(*) from json_dump_file_contents EXTERNAL MODIFY (location(json_dir:'PurchaseOrders.dmp'));
Generieren von JSON-Daten aus der Datenbank
Umgekehrt lassen sich JSON-Daten auch aus dem Inhalt von relationalen Spalten generieren. Arbeitet man beispielsweise mit Oracle Application Express (APEX), kann man JSON-Dokumente mit der Funktion APEX_JSON erzeugen. Eine andere Möglichkeit bietet das Line Mode-Werkzeug SQLcl. Mit dem Format-Kommando "SET SQLFORMAT JSON" wird beispielsweise automatisch eine Ausgabe im JSON-Format angezeigt. Mit Oracle-Datenbank Version 12.2 gibt es eine dritte Möglichkeit – sogar unter Verwendung reiner SQL-Mittel. Mit neuen SQL/JSON-Funktionen wie JSON_OBJECT, JSON_ARRAY oder JSON_ARRAYAGG können im Quellcode aus SQL-Abfragen oder in PL/SQL-Prozeduren JSON-Dokumente erzeugt werden. Beispielsweise kann man dann mit einem einzigen SQL-Statement die Kunden pro Land und ihre zugehörigen Informationen aus den Spalten NAME, STATUS, CITY und STREET in einem JSON Array ausgegeben. Dazu verwendet werden die Tabellen CUSTOMERS und COUNTRIES aus dem Demo-Beispielschema SH.
select json_object('country_name' is c.country_name,
'ctinfo' is (select json_arrayagg(
json_object('name' is cust_last_name,
'status' is cust_marital_status,
'city' is cust_city,
'street' is cust_street_address absent on null)
absent on null)
from customers co
where co.country_id=c.country_id and cust_year_of_birth>1988)
absent on null) as ausgabe
from countries c;Ein Ausschnitt des Ergebnisses sieht dann folgendermaßen aus.
Einfache Zugriffe auf JSON-Daten
Um eine leichte Verwendung von JSON Informationen in der Datenbank zu gewährleisten, sind spezielle SQL-Funktionen und Operatoren eingeführt worden. So lässt sich im einfachsten Fall mit Punktnotation (englisch "dot notation") auf die Daten zugreifen. Voraussetzung ist dabei die Verwendung des Check Constraints IS JSON und die Verwendung eines Tabellen Alias (hier j) in der Abfrage selbst.
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
Ähnlich wie beim Zugriff auf XML-Dokumente stehen aber auch neue Funktionen zur speziellen Nutzung von JSON-Zugriffen zur Verfügung. Arbeitet man lieber mit JSON-Ausdrücken statt mit der Punktnotation oder sind die Abfragen komplexer, dann kann man beispielsweise die Funktion JSON_VALUE verwenden. Im folgenden Beispiel wird das erste Element aus dem Feld Phone von ShippingInstructions (hier $.ShippingInstructions.Phone[0].type) ausgegeben, das der PONumber 1000 entspricht.
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; JSON_EXISTS testet in einer Bedingung auf Existenz eines speziellen Wertes. Folgendes Beispiel zeigt eine Anwendungsmöglichkeit.
SQL> select json_query(json_document,'$.ShippingInstructions')
from json_tab
where json_exists(json_document, '$.ShippingInstructions.Address.zipCode');
JSON_QUERY(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS')
--------------------------------------------------------------------------------
{"name":"Alexander Hunold","Address":{"street":"2014 Oxford Rd","city":"Southlake,","state":"TX","zipCode":26192,"country":"United States of America"},"Phone":[ {"type":"Office","number":"784-555-7990"}]}
{"name":"Sigal Tobias","Address":{"street":"2004 Blacksmiths Court","city":"Seattle","state":"WA","zipCode":98199,"country":"United States of America"},"Phone": [{"type":"Office","number":"710-555-7994"}]}
…Um zusätzlich die Performance dieser Zugriffe zu erhöhen, lassen sich Indizes anlegen. Die neuen Funktionen JSON_VALUE und JSON_EXISTS eignen sich beispielsweise gut dazu, einen Function Based Index auf einer Spalte mit JSON-Inhalten zu erzeugen. Man verwendet die entsprechenden JSON-Pfade um damit einen skalaren Wert zu erzeugen. Dabei können auch Bitmap-Indizes angelegt werden.
SQL> create index ponumber_idx on json_tab (json_value(json_document, '$."PONumber"' returning number error on error)); Index created
Häufig ist auch eine Projektion von JSON-Daten in ein relationales Format erforderlich. Dazu eignet sich die Funktion JSON_TABLE. Sie überführt die JSON-Daten in relationale Zeilen und Spalten einer virtuellen Tabelle, ähnlich einer relationalen Inline View. Folgendes Beispiel zeigt die Funktionsweise. Verwendet wird die Tabelle JSON_TAB mit der Spalte JSON_DOCUMENT. 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)
Eine weitere Möglichkeit im Umgang mit JSON-Dokumenten bietet die erweiterte PL/SQL API in 12.2. Damit ist es möglich, "Key:Value"-Paare, die in einem gespeicherten JSON-Dokument vorliegen, hinzuzufügen, zu verändern oder auch zu löschen. Dazu wurden neue PL/SQL-Datentypen wie JSON_OBJECT_T, JSON_ELEMENT_T und JSON_ARRAY_T eingeführt. JSON_OBJECT_T.PARSE parst ein JSON-Dokument und stellt es anschließend im Hauptspeicher zur Verfügung. Dabei kann man durch ein JSON-Dokument navigieren – vergleichbar mit dem XML Document Object Model (kurz DOM). So können beispielsweise dedizierte Key-Werte eines gespeicherten JSON-Dokuments mit PL/SQL geändert werden.
Interessante Erweiterung durch die neue Data Guide-Struktur
Ein neues Konstrukt, der sogenannte Data Guide kann die JSON-Struktur, die Namen und Datentypen der Attribute auslesen. Ein solcher Data Guide kann persistent in der Datenbank abgelegt werden oder einfach nur on-the-fly generiert werden. Folgendes Beispiel demonstriert die Verwendung ohne Speicherung. Verwendet wird die Tabelle JSON_TAB mit der Spalte JSON_DOCUMENT.
SQL> select JSON_DATAGUIDE(json_document) "DATA GUIDE"
from json_tab;
DATA GUIDE
--------------------------------------------------------------------------------
[{"o:path":"$.User","type":"string","o:length":8},{"o:path":"$.PONumber","type":
"number","o:length":8},{"o:path":"$.LineItems","type":"array","o:length":1024},{
"o:path":"$.LineItems.Part","type":"object","o:length":256},{"o:path":"$.LineIte
ms.Part.UPCCode","type":"number","o:length":16},{"o:path":"$.LineItems.Part.Unit
Price","type":"number","o:length":8},{"o:path":"$.LineItems.Part.Description","t
ype":"string","o:length":128},{"o:path":"$.LineItems.Quantity","type":"number","
o:length":4},{"o:path":"$.LineItems.ItemNumber","type":"number","o:length":1},{"
o:path":"$.Reference","type":"string","o:length":32},{"o:path":"$.Requestor","ty
pe":"string","o:length":32},{"o:path":"$.CostCenter","type":"string","o:length":
4},{"o:path":"$.AllowPartialShipment","type":"boolean","o:length":4},{"o:path":"
$.ShippingInstructions","type":"object","o:length":256},{"o:path":"$.ShippingIns
tructions.name","type":"string","o:length":32},{"o:path":"$.ShippingInstructions
.Phone","type":"array","o:length":128},{"o:path":"$.ShippingInstructions.Phone.t
ype","type":"string","o:length":8},{"o:path":"$.ShippingInstructions.Phone.numbe
r","type":"string","o:length":16},{"o:path":"$.ShippingInstructions.Address","ty
pe":"string","o:length":1},{"o:path":"$.ShippingInstructions.Address","type":"ob
ject","o:length":256},{"o:path":"$.ShippingInstructions.Address.city","type":"st
ring","o:length":32},{"o:path":"$.ShippingInstructions.Address.state","type":"st
ring","o:length":2},{"o:path":"$.ShippingInstructions.Address.county","type":"st
ring","o:length":8},{"o:path":"$.ShippingInstructions.Address.street","type":"st
ring","o:length":64},{"o:path":"$.ShippingInstructions.Address.country","type":"
string","o:length":32},{"o:path":"$.ShippingInstructions.Address.zipCode","type"
:"number","o:length":8},{"o:path":"$.ShippingInstructions.Address.postcode","typ
e":"string","o:length":8},{"o:path":"$.ShippingInstructions.Address.province","t
ype":"string","o:length":2},{"o:path":"$.\"Special Instructions\"","type":"strin
g","o:length":32}]Diese Funktion kann natürlich auch in Verbindung mit der Funktion HTTPURITYPE verwendet werden und auf diese Weise JSON-Informationen über eine URL auslesen. Das folgende SQL-Statement liest beispielsweise GeoJSON-Daten aus dem Erdbeben-JSON-Feed der U.S. Geological Survey (USGS) aus.
select JSON_DATAGUIDE(
httpuritype('https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_day.geojson'
).getclob() )
from dual;Entscheidet man sich für das Persistieren, bringt dies weitere Vorteile mit sich: So können schnell und einfach mit nur einem Befehl relationale Views erzeugt werden, eine performante Suche im Text gestaltet werden und sogar automatisch virtuelle Spalten hinzugefügt werden, sobald sich der Inhalt des JSON-Dokuments erweitert. Je nach Verwendung der Syntax gibt es unterschiedliche Varianten, ein Data Guide-Konstrukt zu erzeugen – beispielsweise mit oder ohne Text-Index. Wir entscheiden uns hier für die einfachste Form der Syntax, die einige Default-Einstellungen verwendet – wie zum Beispiel das automatische Erzeugen eines Textindex.
create search index json_tab_guide on json_tab (json_document) for json;
Damit werden dann die Strukturen analysiert und die zugehörigen neuen Objekte wie Indizes, Tabellen und LOBs im Data Dictionary angelegt. Die Strukturen sind in USER_JSON_DATAGUIDES zu finden oder lassen sich über die neue Funktion DBMS_JSON.GET_INDEX_DATAGUIDE ausgeben. Mit Hilfe dieser hierarchischen Data Guides kann man Datenbank-Views anlegen, deren Spalten JSON-Felder aus den Dokumenten projizieren. Man kann den Data Guide auch so bearbeiten, dass er nur die Felder enthält, die man projizieren möchte, die häufig verwendet werden oder mit user definierte Spaltennamen versehen sind. Das Package DBMS_JSON kann im nächsten Schritt zur Erzeugung einer Standard-View verwendet werden. Im Beispiel benennen wir die Spalte "JSON_DOCUMENT$User" in "User" um. Danach wird die View JSON_PO_VIEW mit einem einzigen Aufruf erzeugt.
dbms_json.rename_column( tablename => 'JSON_TAB', jcolname => 'JSON_DOCUMENT', path => '$."User"', type => dbms_json.type_string, preferred_name => 'USER'); dbms_json.create_view_on_path ( viewName => 'JSON_PO_VIEW', tableName => 'JSON_TAB', jcolname => 'JSON_DOCUMENT', path => '$'); SQL> desc JSON_PO_VIEW Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER USER VARCHAR2(8) JSON_DOCUMENT$PONumber NUMBER JSON_DOCUMENT$Reference VARCHAR2(32) JSON_DOCUMENT$Requestor VARCHAR2(32) JSON_DOCUMENT$CostCenter VARCHAR2(4) JSON_DOCUMENT$AllowPartialShipment VARCHAR2(4) JSON_DOCUMENT$name VARCHAR2(32) JSON_DOCUMENT$Address VARCHAR2(1) …
Als nächstes wird eine einfache Ad-hoc-Abfrage mit der Funktion JSON_EXISTS formuliert. Die Ausgabe des Ausführungsplans beweist, dass der Index Verwendung findet.
SQL> select count(*) from json_tab
where json_exists(json_document, '$.ShippingInstructions.Address.country');
COUNT(*)
----------
9899
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
-SQL_ID 98w38wbmvsndq, child number 0
-------------------------------------
select count(*) from json_tab where
json_exists(json_document,'$.ShippingInstructions.Address.country')
Plan hash value: 1988477038
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1997 | | |
|* 2 | DOMAIN INDEX | JSON_TAB_GUIDE | 5 | 9985 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'HASPATH(/Ship
pingInstructions/Address/country)')>0)Die nächste Abfrage zeigt, dass auch ungenaue Suchanfragen möglich sind. Im Folgenden ist offensichtlich die Schreibweise für die Stadt "San Francisco" nicht korrekt. Mit einer Oracle-Textsuche und der Verwendung des Operators FUZZY kann die Abfrage trotzdem erfolgreich durchgeführt werden.
SQL> select json_value(json_document, '$.ShippingInstructions.Address.city') from json_tab where json_textcontains(json_document,'$.ShippingInstructions','fuzzy(fransesco)'); JSON_VALUE(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.ADDRESS.CITY') --------------------------------------------------------------------------------------------- South San Francisco South San Francisco South San Francisco
Auch andere Oracle-Textoperatoren können in diesen Abfragen Verwendung finden, wie zum Beispiel SOUNDEX, NEAR, Stemming (deutsch abstammend) usw. Weitere Informationen hierzu finden sich auch im deutschsprachigen Oracle-Text-Blog [1].
Schlussbemerkung
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 und es sind keine besonderen Voraussetzungen oder Privilegien, um JSON-Funktionen zu verwenden 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 gezeigt wurde: es gibt keine Einschränkungen bei Abfragen auf gemischte 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. Außerdem stehen alle Performance-Features der Datenbank auch für die Speicherung der JSON-Daten zur Verfügung. Es gibt sogar weitere Performance-Features in Verbindung mit der In-Memory Columnstore, die beispielsweise Performancegewinne für die Verwendung von Funktionen wie JSON_TABLE, JSON_VALUE oder JSON_EXISTS durch Einsatz eines speziellen Binary-Formats versprechen. Steht keine 12.2-Installation zur Verfügung, kann man auch mit LiveSQL starten, einem kostenlosen Online-Tool, um SQL mit einer Oracle-Datenbank auszuprobieren. Man benötigt nur einen Webbrowser und einen kostenlosen Oracle Technology Network (OTN)-Account [2].
