In unserem heutigen Beitrag wollen wir uns mit dem Thema beschäftigen, wie man aus einem JSON String wieder die Daten extrahieren kann.
Diesen Anwendungsfall hatte ich die letzten 24 Monat sehr häufig, deswegen wurde es mal wieder Zeit sich mit dem Thema JSON in Oracle zu beschäftigen.
Wir fangen mit einer kleinen Test-Tabelle basiernd auf den Emp und Dept Daten des Benutzers SCOTT an:
Die Besonderheit / Schwierigkeit besteht darin, dass die Mitarbeiter in einem Array eingetragen werden (["CLARK","KING","MILLER"]
CREATE TABLE dept_json
( json_text CLOB,
CONSTRAINT check_json CHECK ( json_text IS JSON ) );
INSERT INTO dept_json
VALUES ('{ "DEPT" : {
"DEPTNO" : 10,
"DNAME" : "ACCOUNTING",
"LOC" : "NEW YORK",
"EMPS" : ["CLARK","KING","MILLER"] }}');
INSERT INTO dept_json
VALUES ('{"DEPT" : {
"DEPTNO" : 20,
"DNAME" : "RESEARCH",
"LOC" : "DALLAS",
"EMPS" : ["SMITH","JONES","SCOTT","ADAMS","FORD"] }}');
INSERT INTO dept_json
VALUES ('{"DEPT" : {
"DEPTNO" : 30,
"DNAME" : "SALES",
"LOC" : "CHICAGO",
"EMPS" : ["ALLEN","BLAKE","JAMES","MARTIN","TURNER","WARD"] }}');
Als Bonustrack legen wir einen Oracle Text-Index auf die JSON Spalte:
CREATE INDEX dept_json_index
ON dept_json(json_text)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');
Wir können dann mit der Funktion JSON_TEXTCONTAINS im JSON String suchen.
SELECT json_text
FROM dept_json
WHERE JSON_TEXTCONTAINS(json_text, '$', 'KING');
Das Ergebnis sieht dann so aus:
{ "DEPT" : {
"DEPTNO" : 10,
"DNAME" : "ACCOUNTING",
"LOC" : "NEW YORK",
"EMPS" : ["CLARK","KING","MILLER"] }}
Wenn man die Daten der JSON Tabelle wieder extrahieren (und weiterverarbeiten) möchte:
SELECT deptno, dname, loc, linenum, arrayval
from dept_json,json_table(json_text, '$.DEPT[*]'
columns ( arraynum for ordinality, DEPTNO,DNAME,LOC,
nested path '$.EMPS[*]'
columns ( linenum for ordinality,nested path '$[*]'
columns( arrayval varchar2 path '$')
) ));
Das Ergebnis sieht dann so aus:
| DEPTNO | DNAME | LOC | LINENUM | ARRAYVAL |
|---|
| 10 | ACCOUNTING | NEW YORK | 1 | CLARK |
| 10 | ACCOUNTING | NEW YORK | 2 | KING |
| 10 | ACCOUNTING | NEW YORK | 3 | MILLER |
| 20 | RESEARCH | DALLAS | 1 | SMITH |
| 20 | RESEARCH | DALLAS | 2 | JONES |
| 20 | RESEARCH | DALLAS | 3 | SCOTT |
| 20 | RESEARCH | DALLAS | 4 | ADAMS |
| 20 | RESEARCH | DALLAS | 5 | FORD |
| 30 | SALES | CHICAGO | 1 | ALLEN |
| 30 | SALES | CHICAGO | 2 | BLAKE |
| 30 | SALES | CHICAGO | 3 | JAMES |
| 30 | SALES | CHICAGO | 4 | MARTIN |
| 30 | SALES | CHICAGO | 5 | TURNER |
| 30 | SALES | CHICAGO | 6 | WARD |
Wenn man mal einen unbekannten JSON String auswerten möchte stellt Oracle dafür auch ein Package zur Verfügung:
SELECT json_dataguide(json_text, DBMS_JSON.FORMAT_HIERARCHICAL, DBMS_JSON.PRETTY)
FROM dept_json;
Die Ausgabe sieht dann so aus:
{
"type" : "object",
"o:length" : 128,
"properties" :
{
"DEPT" :
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DEPT",
"properties" :
{
"LOC" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "LOC"
},
"EMPS" :
{
"type" : "array",
"o:length" : 64,
"o:preferred_column_name" : "EMPS",
"items" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "scalar_string"
}
},
"DNAME" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DNAME"
},
"DEPTNO" :
{
"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "DEPTNO"
}
}
}
}
}
Und nun viel Spaß mit der Auswertung von Daten aus JSON-Ausdrücken. Bei uns wird das Thema ausführlich behandelt in den Kursen Oracle PL/SQL II, Oracle REST und Oracle Dev New Features 12c - 21c.