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 Arrays 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 JsonPointer, der zum "people" Array im Dokument führt.
Innerhalb der Datensätze aus dem Array werden wieder mit Hilfen von JsonPointern 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 JsonPointer 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 JsonPointer 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 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 "/resarch" 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 der SELECT-Klausel nicht 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:
Der 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 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 "" das gesamte Dokument zurückliefert, 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 ergeben folgende Selektionen:
"/foo" -> ["bar", "baz"]
"/foo/0" -> "bar"
"/" -> 0
"" -> {"foo": ["bar", "baz"], "": 0}Weil der SQL-Parser der Jdbc-Treiber keine leeren Spalten in der SELECT-Klausel und keine leeren Tabellen in der FROM-Klausel unterstützt, muss abweichend vom Json-Pointer Standard "/" zur Selektion des gesamten Dokuments in einem SELECT oder FROM 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 "/" selektiert. 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 selektiert werden können (sowohl in der SELECT-Klausel als auch in der FROM-Klausel).