Der JsonJdbc Treiber erlaubt es, Json-Dateien über Jdbc zu lesen. Mit Hilfe von SQL-Abfragen können damit Daten aus einer Json-Datei genauso wie aus einer Datenbanktabelle extrahiert und weiterverarbeitet werden.
Zur Abfrage werden Elemente in einem Json-Dokument als Tabellen angesprochen. Dazu verwendet man drei Komponenten in der Abfrage
Alle Pfade werden als Json-Pointer angegeben. Zur Syntax siehe die RFC zu Json-Pointer.
Das folgende Json-Dokument kann man zum Beispiel mit Hilfe des Treibers einlesen:
{
"people": [
{
"id": 10,
"name": {
"fname": "Henry",
"lname": "Cavendish"
},
"profession": "scientist"
},
{
"id": 21,
"name": {
"fname": "Isaac",
"lname": "Newton"
},
"profession": "scientist"
},
{
"id": 33,
"name": {
"fname": "Doctor",
"lname": "Seltsam"
},
"profession": "scientist"
}
]
}Eine SQL-Abfrage über den JsonJdbc Treiber sieht dann so aus:
SELECT "/id"
, "/name/fname"
, "/name/lname"
, "/profession"
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Small.json"."/people" COLUMNS("/id", "/name/fname", "/name/lname", "/profession")Dabei wird der Dateiname "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Small.json" als Schema angegeben und "/people" ist ein Json-Pointer, der zum "people" Array im Dokument führt.
Innerhalb der Datensätze aus dem Array werden wieder mit Hilfen von Json-Pointern die Spalten "/id", "/name/fname", "/name/lname" und "/profession" angesprochen.
Das Ergebnis sieht dann so aus:
/id /name/fname /name/lname /profession
--- ----------- ----------- -----------
10 Henry Cavendish scientist
21 Isaac Newton scientist
33 Doctor Seltsam scientist Wie gewohnt können Spaltenaliase vergeben werden, um die etwas kryptischen Json-Pointer in besser lesbare Namen umzuwandeln:
SELECT "/id" AS id
, "/name/fname" AS fname
, "/name/lname" AS lname
, "/profession" AS profession
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Small.json"."/people" COLUMNS("/id", "/name/fname", "/name/lname", "/profession")Das liefert dann:
id fname lname profession
-- ----- ----- ----------
10 Henry Cavendish scientist
21 Isaac Newton scientist
33 Doctor Seltsam scientist Neben den Datenspalten aus dem Json-Dokument generiert der JsonJdbc Treiber zusätzliche Pseudospalten:
SELECT rownumber AS rownum
, id AS path
, "/id" AS id
, "/name/fname" AS fname
, "/name/lname" AS lname
, "/profession" AS profession
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Small.json"."/people" COLUMNS("/id", "/name/fname", "/name/lname", "/profession")Die Pseudospalte "rownumber" liefert eine fortlaufende Nummer für jeden Datensatz des Ergebnisses. Die Pseudospalte "id" liefert den Json-Pointer des Datensatzes im Dokument. Das Ergebnis der Beispielabfrage sieht so aus:
rownum path id fname lname profession
------ ---- -- ----- ----- ----------
1 /people/0 10 Henry Cavendish scientist
2 /people/1 21 Isaac Newton scientist
3 /people/2 33 Doctor Seltsam scientist Hinweis: Es gibt weiterhin noch die Pseudospalte "parentid". Sie liefert den Json-Pointer des darüberliegenden Arrays, dazu mehr im nächsten Abschnitt.
Ein Array in einem Json-Dokument kann wieder Sub-Arrays enthalten wie in dem folgenden Beispiel:
{
"people": [
{
"id": 10,
"name": {
"fname": "Henry",
"lname": "Cavendish"
},
"profession": "scientist",
"research": [
{
"area": "Chemistry"
},
{
"area": "Density of the Earth"
},
{
"area": "Electricity"
}
]
},
{
"id": 21,
"name": {
"fname": "Isaac",
"lname": "Newton"
},
"profession": "scientist",
"research": [
{
"area": "Calculus"
},
{
"area": "Optics"
},
{
"area": "Gravity"
}
]
},
{
"id": 33,
"name": {
"fname": "Doctor",
"lname": "Seltsam"
},
"profession": "scientist"
}
]
}Auch Sub-Arrays können als Tabellen angesprochen werden. Dazu muss die Array-Hierarchie in der FROM-Klausel als Liste von Json-Pointern mit Komma getrennt angegeben werden:
SELECT "/area" AS area
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Researcher.json"."/people,/research" COLUMNS("/area")Hier ist "/people" der Json-Pointer zum ersten Array. Dieses Array enthält die Element "/people/0", "/people/1" usw.. Jedes dieser Elemente kann dann wieder ein Sub-Array enthalten, das den relativen Pfad "/research" hat.
Die Abfrage liefert folgendes Ergebnis:
area
-----
Chemistry
Density of the Earth
Electricity
Calculus
Optics
GravityHinweis: Die über Json-Pointer referenzierten Spalten navigieren per Json-Pointer immer zu Elementen des Sub-Arrays "/research". Die Elemente des darüberliegenden Arrays "/people" können in derselben SELECT-Klausel nicht gleichzeitig angesprochen werden.
Leider ist damit keine direkte Korrelation der gefundenen Datensätze zur darüberliegenden Tabelle bzw. zum darüberliegenden Json Array möglich. Da hilft die Pseusospalte "parentid":
SELECT "parentid" AS parentid
,"/area" AS area
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/Researcher.json"."/people,/research" COLUMNS("/area")Jetzt können die gefunden Zeilen mit der Elterntabelle korrelliert werden.
parentid area
-------- ----
/people/0 Chemistry
/people/0 Density of the Earth
/people/0 Electricity
/people/1 Calculus
/people/1 Optics
/people/1 GravityDie Werte der Pseudospalte "parentid" im Sub-Array entsprechen den Werten der Pseudospalte "id" im darüberliegenden Eltern-Array (siehe oben). Das erlaubt folgende Vorgehensweise zum Einlesen eines Json Dokuments in Datenbanktabellen:
Es kann statt zu einem Json-Arrays auch zu einem einzelnen Json-Objekt per Json-Pointer navigiert werden. In diesem Fall generiert der Jsonjdbc Treiber ein künstliches Array, das nur dieses eine Objekt enthält und führt die anschließende Selektion auf diesem Array aus. Als Ergebnis gibt er dann das Objekt und seine Elemente (Spalten) als einzigen, gefundenen Datensatz zurück.
Dazu ein einfaches Beispiel. Für diese Json-Datei
{
"fname": "John",
"lname": "Doe"
}liefert diese Abfrage
SELECT "/fname"
, "/lname"
FROM "src/test/resources/de/softquadrat/jdbc/json/ObjectTest.json"."/" COLUMNS("/fname", "/lname")dieses Ergebnis
/fname /lname
------ ------
John DoeDer JsonJdbc Treiber unterstützt Verzeichnisse als Systemtabellen. Das erlaubt in einer Loop-Query eines datasqill Moduls nach Dateien in einem Verzeichnis zu suchen, die dann anschließend geladen werden können.
Die folgende Abfrage benutzt die Funktion "files()" als Systemtabelle und ihre Spalten "directory" und "filename".
SELECT directory, filename
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json".files()
WHERE filename = 'JsonValidate.json' or filename = 'PeopleTest.json'Als Ergebnis werden die gesuchten Dateien in dem verwendeten Verzeichnis zurückgeliefert:
directory filename
--------- --------
jsonjdbc/src/test/resources/de/softquadrat/jdbc/json JsonValidate.json
jsonjdbc/src/test/resources/de/softquadrat/jdbc/json PeopleTest.jsonDer JsonJdbc Treiber liefert alle Ergebnisse als Text zurück. Eine Umwandlung in andere Datentypen muss in nachgelagerten Arbeitsschritten erfolgen.
Sollte eine Abfrage keine Datensätze zurückgeben, so ist zu prüfen, ob die Json-Pointer in der FROM-Klausel tatsächlich alle zu einem Json-Array oder Json-Objekt navigieren. Ist dies nicht der Fall, ignoriert der JsonJdbc Treiber die Abfrage und gibt keine Ergebnisse zurück.
Der JsonJdbc Treiber liefert für selektierte Spalten in der SELECT-Klausel deren Inhalt zurück. Ist das per Json-Pointer angesprochene Element nicht vorhanden oder ist es null, wird ein Null-Wert zurückgegeben.
{
"test" : [
{
"id": 1,
"name": "Test1"
}
{
"id": 2,
"name": null
}
]
}SELECT "/id"
, "/name"
, "/illegal"
FROM "test.json"."/test" COLUMNS("/id", "/name", "/illegal")/id /name /illegal
--- ----- --------
1 Test1 null
2 null null Ist das per Json-Pointer angesteuerte Element in der SELECT-Klausel nicht "null", kein Boolescher Wert, keine Zahl oder keine Zeichenkette sondern ein Objekt oder ein Array, wird der gefundene Json-Teilstrings als Ergebnis zurückgegeben:
{
"test" : [
{
"id": 1,
"name": "Test1",
"child": {
"date": "17.3.2022",
"description": "only a test"
}
},
{
"id": 2,
"name": "Test2",
"child": [
{
"col1": "One",
"col2": "Two"
}
]
}
]
}SELECT "/id"
, "/name"
, "/child"
FROM "test.json"."/test" COLUMNS("/id", "/name", "/child")/id /name /child
--- ----- ------
1 Test1 {"date":"17.3.2022","description":"only a test"}
2 Test2 [{"col1":"One","col2":"Two"}]Die Json-Pointer Syntax unterscheidet sorgfältig "" und "/". Während "" zum gesamten Dokument navigiert, steuert "/" das Element mit dem Schlüssel "" im Json Dokument an.
Alles klar? Hier ein konkretes Beispiel aus der RFC:
{
"foo": ["bar", "baz"],
"": 0
}Folgende Json-Pointer navigieren zu folgende Elementen im Baum:
"/foo" -> ["bar", "baz"]
"/foo/0" -> "bar"
"/" -> 0
"" -> {"foo": ["bar", "baz"], "": 0}Weil der SQL-Parser der Jdbcjdbc Treiber keine leeren Spalten in der SELECT-Klausel und keine leeren Tabellen in der FROM-Klausel unterstützt, verwendet er "/" für das gesamte Dokument. Es muss also abweichend vom Json-Pointer Standard "/" zur Navigation zum gesamten Dokument in der FROM-Klausel und zur Selektion des gesamten Dokuments in der SELECT-Klausel verwendet werden:
[
{ "name": { "fname": "John", "lname": "Doe"} },
{ "name": { "fname": "Jane", "lname": "Roberts"} }
]SELECT "/name/fname" AS fname
, "/name/lname" AS lname
, "/" AS json
FROM "jsonjdbc/src/test/resources/de/softquadrat/jdbc/json/ArrayTest.json"."/" COLUMNS("/name/fname","/name/lname","/")Hier wird das gesamte Json-Dokument in der FROM-Klausel mit "/" angesteuert. Weiterhin wird mit "/" in der SELECT-Klausel das Json-Dokument des jeweiligen Array-Elements zurückgegeben.
fname lname json
----- ----- ----
John Doe {"name":{"fname":"John","lname":"Doe"}}
Jane Roberts {"name":{"fname":"Jane","lname":"Roberts"}} Diese Abweichung hat zur Folge, dass keine Elemente mit leerem Schlüssel "" in einem Json-Dokument per Json-Pointer angesteuert (FROM-Klausel) oder selektiert (SELECT-Klausel) werden können.
Hat man eine Json-Datei mit einem Objekt vorliegen, so kann das ganze Objekt als Json-String selektieren:
{
"id": 1,
"name": "Test1",
"child": {
"date": "17.3.2022",
"description": "only a test"
}
}Dieses SQL navigiert zum Wurzelknoten des Dokuments und selektiert das gesamte Json-Objekt als String:
SELECT "/" AS json
FROM "test3.json"."/" COLUMNS("/")Das liefert dieses Ergebnis:
json
----
{"id":1,"name":"Test1","child":{"date":"17.3.2022","description":"only a test"}}Achtung: Ist das Wurzelelement in der Json-Datei ein Array, so wird dieses Array als Tabelle behandelt und es werden die einzelnen Elemente im Array zurückgegeben. In diesem Fall kann man deshalb nicht das gesamte Json (inklusive der eckigen Klammern []) selektieren.
Das bedeutet, diese Json-Datei
[
{
"id": 1,
"name": "Test1",
"child": {
"date": "17.3.2022",
"description": "only a test"
}
}
]liefert mit demselben SQL dasselbe Ergebnis wie bei dem vorherigen Beispiel mit dem einfachen Objekt. Anders ausgedrückt, die beiden Beispiele sind äquivalent, weil der Jsonjdbc Treiber beim ersten Beispiel das Array selbstständig ergänzt (siehe Abschnitt Json-Objekte).