Oracle native json Support

Oracle native json Support
Erste Schritte
1 Ausgangslage
Als erster Schritt erstellen wir eine Tabelle, die wir für den weiteren Verlauf brauchen werden. Die
Felder Id, Created und Username sind „normale“ Attribute, das Feld Extended wird später die
dynamischen Properties beinhalten. Aus SQL Sicht ist es prinzipiell eine normale Spalte, die einen
String-Wert beinhalten kann. Die dynamische Struktur wird im Verlauf dieses Artikels komplexer,
die Tabelle muss hierfür aber nicht angepasst werden.
Wir haben eine Tabelle SAMPLE1 mit folgendem Inhalt:
Name
Type
Id
NUMBER
Created
TIMESTAMP
Username
VARCHAR2
Extended
CLOB
-- Table
CREATE TABLE SAMPLE1 (
Id NUMBER(10) NOT NULL,
CREATED TIMESTAMP (0),
USERNAME VARCHAR2(20 BYTE),
EXTENDED CLOB CONSTRAINT valid_json CHECK (Extended IS JSON),
PRIMARY KEY (Id))
--Trigger
CREATE SEQUENCE Sample1_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER Sample1_seq_tr
BEFORE INSERT ON Sample1 FOR EACH ROW
WHEN (NEW.Id IS NULL)
BEGIN
SELECT Sample1_seq.NEXTVAL INTO :NEW.Id FROM DUAL;
END;
/
Die Spalte Extended nutzen wir um den JSON-String abzulegen. Hier setzen wir einen Constraint
„valid_json“, der überprüft, ob die Eingabe einem gültigen JSON-String entspricht. Diese
Überprüfung ist wichtig, andernfalls können die Felder nicht abgefragt werden.
1.1 Abfrage von Feldern mit JSON
Zuerst legen wir Beispiel-Daten an:
INSERT INTO Sample1 (Created, Username, Extended) VALUES (TO_DATE('2016-11-17
17:26:30', 'yyyy-mm-dd hh24:mi:ss'), 'Test', '{
"name": "Nineteen Eighty-Four",
"author": "George Orwell",
"genre": "Political fiction",
"price": 14.99,
"pages": 328
}')
INSERT INTO Sample1 (Created, Username, Extended) VALUES (TO_DATE('2008-01-28
21:28:27', 'yyyy-mm-dd hh24:mi:ss'), 'Test', '{
"name": "The Metamorphosis",
"author": "Franz Kafka",
Oracle native json Support - Erste Schritte
Seite 2 von 8
"genre": "Short Story",
"price": 11.99,
"pages": 201
}')
Die „klassischen“ Attribute können wie gewohnt über eine Query abgefragt werden:
SELECT * FROM Sample1 WHERE Username = 'Test'
Aber wie werden die zusätzlichen Felder in der Spalte Extended abgefragt?
Hierfür gibt es mehrere Möglichkeiten, zwei davon werde ich hier vorstellen:
Die einfachste Methode ist die Dot-Notation. Diese eignet sich für simple Aufgaben, kommt aber,
bei beispielsweise Arrays, schnell an ihre Grenzen.
SELECT s.Extended.name FROM sample1 s
Hier werden beide Buchtitel ausgegeben:
SELECT s.Extended.name FROM sample1 s WHERE s.Extended.pages > 300
Oder nur das Buch ausgegeben, das mehr als 300 Seiten umfasst:
Eine andere Vorgehensweise ist der Einsatz von JSON_TABLE:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
name VARCHAR2(100) PATH '$.name',
author VARCHAR2(100) PATH '$.author',
price NUMBER(10,2) PATH '$.price',
pages NUMBER(10) PATH '$.pages'
)
) as jt
Hier werden die JSON-Properties als relationale Tabellen-Spalten projiziert. Es kann natürlich
auch hier gefiltert werden:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
name VARCHAR2(100) PATH '$.name',
Oracle native json Support - Erste Schritte
Seite 3 von 8
author VARCHAR2(100) PATH '$.author',
price NUMBER(10,2) PATH '$.price',
pages NUMBER(10) PATH '$.pages'
)
) as jt
WHERE price > 12 AND Username = 'Test'
Ein paar Erklärungen zum Syntax: $ bezieht sich auf das Root-Element, hier ist Root „ganz oben“
also das komplette Dokument. Danach werden die Spalten aufgelistet, die ausgewertet werden
sollen. Es ist wichtig den richtigen Datentyp zu wählen - oder falls dies nicht möglich ist,
VARCHAR2 nehmen. Mit VARCHAR2 können dann natürlich keine Operatoren (Vergleiche,
logische oder numerische Operatoren) benutzt werden.
Um das Beispiel etwas zu erweitern, importieren wir mehr Datensätze, die diesmal komplexer
aufgebaut sind bzw. nicht mehr flach sind. Das Property address beinhaltet ebenfalls ein UnterObjekt und phoneNumber ist ein Array von mehreren Objekten.
INSERT INTO Sample1 (Created, Username, Extended) VALUES (TO_DATE('1970-05-08
04:58:52', 'yyyy-mm-dd hh24:mi:ss'), 'Test2', '{
"firstName" : "Robin",
"lastName" : "Hood",
"age" : 22,
"address" : {
"street" : "Wood 12",
"city" : "London",
"zip" : "1000"
},
"phoneNumber" :
[{
"type" : "home",
"number" : "111 222-3333"
}, {
"type" : "fax",
"number" : "111 222-4444"
}
]
}')
INSERT INTO Sample1 (Created, Username, Extended) VALUES (TO_DATE('1995-06-10
10:52:45', 'yyyy-mm-dd hh24:mi:ss'), 'Test2', '{
"firstName" : "Bruce",
"lastName" : "Lee",
"age" : 31,
"address" : {
"street" : "Street 42",
"city" : "Hong Kong",
"zip" : "1234"
},
"phoneNumber" :
[{
"type" : "home",
"number" : "123456789"
}, {
"type" : "fax",
"number" : "987654321"
}
]
}')
Oracle native json Support - Erste Schritte
Seite 4 von 8
Führen wir die Select Queries aus dem letzten Schritt aus, bekommen wir dieselben Resultate.
Das ist auch zu erwarten, denn die zwei neuen Datensätze enthalten kein Feld mit dem Titel name
oder price.
Fragen wir aber das Feld age ab, ergibt sich ein anderes Bild:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age'
)
) as jt
WHERE age > 18
Die Properties address und phoneNumber sind komplexer: Wie bereits erwähnt, enthält die
adress-Property weitere Unter-Properties, die zusammen wieder ein Objekt ergeben: street, city
und zip. In der folgenden Query werden wir den Wert city ausgeben. Die anderen Properties
funktionieren analog dazu.
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age',
city VARCHAR2(100) PATH '$.address.city'
)
) as jt
WHERE age > 18
Hier wird einfach mittels der Dot-Notation eine weitere Spalte angezeigt ($.address.city). Das
ganze kann auch ohne Hilfe der JSON_TABLE Funktion verwendet werden:
SELECT s.Extended.address.city FROM sample1 s WHERE s.Extended.age > 18
Das Property phoneNumber beinhaltet einen Array, es funktioniert deshalb etwas anders als
bisher. Mit der Dot-Notation kann zwar auf die Property zugegriffen werden, Oracle verbindet aber
die Werte, was wahrscheinlich oft nicht das gewünschte Resultat liefert:
SELECT s.Extended.phoneNumber."number" FROM sample1 s WHERE s.Extended.age > 18
Oracle native json Support - Erste Schritte
Seite 5 von 8
Hinweis: number ist ein reserviertes Oracle Keyword und muss escaped werden.
Mit JSON_TABLE kann das Ganze detaillierter aufgelistet werden:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age',
NESTED PATH '$.phoneNumber[*]' COLUMNS (
phoneType VARCHAR2(20) PATH '$.type',
phoneNumber VARCHAR2(50) PATH '$.number'
)
)
) as jt WHERE age > 18
Spannend ist hier das Keyword NESTED im Zusammenhang mit dem Pfad $.phoneNumber[*] das
den nachfolgenden Array zeilenweise ausgibt. Das Stern-Symbol (*) bezieht sich hier auf alle
Einträge. Stünde beispielsweise stattdessen eine 0 in den Klammern, würde es nur jeweils den
ersten Eintrag ausgeben.
Hier sieht man aber sehr gut, dass die Datensätze doppelt ausgegeben werden, weil das
phoneNumber Array auch jeweils zwei Einträge besitzt.
Natürlich kann auch hier gefiltert werden:
[...] WHERE phoneType = 'fax'
Im letzten Beispiel verschieben wir das Root-Element der Abfrage:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$.phoneNumber[*]' COLUMNS (
phoneType VARCHAR2(100) PATH '$.type',
phoneNumber VARCHAR2(100) PATH '$.number'
)
) as jt
Hier wird direkt auf die Property phoneNumber verwiesen und die beiden Unter-Properties type
und number werden ausgegeben. Die restlichen Properties wie firstName oder age können bei
diesem Ansatz aber nicht ausgegeben werden. Je nach Aufgabenstellung ist dies eventuell auch
nicht nötig.
Oracle native json Support - Erste Schritte
Seite 6 von 8
1.2 Performance
Neben der reinen Abfragetechniken, möchten wir auch eine Aussage zur Performance machen.
Dieser Test gibt einen kleinen Überblick, wie sich die Performance mit JSON-Date in Oracle
Datenbanken auswirkt. In unserer Testdatenbank befinden sich 500'000 Datensätze mit zweifach
verschachtelten JSON-Daten. Die Tests wurden auf einen Desktop-Rechner mit i7-3770 CPU (3.4
Ghz), 16 GB RAM, SDD auf Windows 8.1 ausgeführt Das Feld Extended besitzt einen Index:
CREATE INDEX SAMPLE_JSON_IDX ON SAMPLE1 (Extended) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');
String Length Query ohne JSON-Bezug:
SELECT COUNT(*) FROM Sample1 WHERE length(Username) > 0
Antwortzeit: 0.09s
String Length Query mit JSON-Bezug:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age'
)
) as jt
WHERE length(lastName) > 0
Antwortzeit: 2.4s
Integer Vergleich ohne JSON-Bezug:
SELECT COUNT(*) FROM Sample1 WHERE Id >= 0
Antwortzeit: 0.085s
Integer Vergleich mit JSON-Bezug:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age'
)
) as jt
WHERE age >= 0
Antwortzeit: 2.25s
JSON-Table Join mit normalen Tabelle:
SELECT sample1.*, jt.*
FROM sample1,
JSON_TABLE(Extended,
'$' COLUMNS (
firstName VARCHAR2(100) PATH '$.firstName',
lastName VARCHAR2(100) PATH '$.lastName',
age NUMBER(10,2) PATH '$.age'
)
) as jt
INNER JOIN person ON age = person.age
Oracle native json Support - Erste Schritte
Seite 7 von 8
Antwortzeit: 2.28s
Hinweis: Hier wird eine zweite Tabelle referenziert, die nicht Teil dieses Artikels ist.
1.3 Fazit
Mittels der JSON-Erweiterung für Oracle können viele Vorteile der dokumentbasierten
Datenbanken auch in relationalen Oracle Datenbanken verwendet werden. Auf die gewohnte SQL
Syntax muss hierbei nicht verzichtet werden. Damit wird für viele Anwender, die auf NoSQL
Datenbanken wechseln (müssen) ein Hindernis auf die Seite geräumt.
An einigen Stellen merkt man allerdings, dass hier zwei Parteien miteinander verheiratet werden,
die eigentlich gar nicht füreinander bestimmt sind. Zum Beispiel sind JSON-Properties nicht
typisiert, der SQL Standard verlangt diese aber. Ein anderes Fass macht man mit OR-Mappern
auf: Sie unterstützen die erweiterten Abfragen (noch) nicht, der Entwickler ist also gezwungen
einen eigenen Datenbank-Layer zu entwickeln. In Punkto Performance ergeben sich zwar
Einbussen aber diese sind verhältnismässig gering.
Wir werden den Ansatz auf jeden Fall bei der Realisierung von dynamischen Datenmodellen
weiterverfolgen.
Oracle native json Support - Erste Schritte
Seite 8 von 8