|
JSON in 12.2: JSON Generierung, neues Data Guide Konzept, neue Objekttypen
JSON Daten werden häufig in NoSQL oder anderen speziellen Datenbanken gespeichert bzw. damit in Verbindung gebracht. Diese erlauben zwar die Speicherung und den Zugriff der Daten, aber weisen kein vergleichbares Konsistenzmodell, Transaktionsmodell und andere Standardfunktionalitäten von relationalen Datenbanken auf.
"All your Data" in der Oracle Datenbank gilt immer noch auch und gerade in der aktuellen Datenbankversion Oracle Database 12c Release 2: Sei es im Bereich XML, bei Texten mit linguistischen Suchanforderungen oder auch für Geoinformationen (Spatial) und im semantischen Netzwerk Umfeld um nur einige Beispiele zu nennen.
So wurden mit 12c (12.1.0.2) dann auch JSON Zugriffe möglich. Die Verwendung ist dabei ganz einfach: Man definiert eine Datenbankspalte mit einem beliebigen Datentyp für Textstrings (wie zum Beispiel VARCHAR2 oder CLOB). (Hinweis: VARCHAR2 Felder können übrigens seit 12c bis zu 32k groß werden, sofern die Möglichkeit der Erweiterung der Datentypen aktiviert wurde.) Mit der Bedingung IS JSON kann zusätzlich der Inhalt validiert werden - auf Wohlgeformtheit oder auf die Art der Syntaxverwendung (STRICT oder LAX). Die Zugriffe erfolgen dann mit Standardmitteln und neu eingeführten SQL/JSON Funktionen. Liegen die JSON Daten in einem DMP Format außerhalb der Datenbank vor, kann sogar über die EXTERNAL TABLE Funktion zugegriffen werden. Es ist keine zusätzliche Installation von Software erforderlich; eine JSON Unterstützung ist in allen Cloud und Softwareinstallation der Datenbank sofort verfügbar. Mehr über die Basisfunktionen in der Oracle Datenbank können Sie im Community Beitrag "JSON in der Datenbank mit Basisfunktionen" nachlesen.
In Oracle 12.2 gibt es jetzt auch Lösungen zu folgenden Fragestellungen: Wie kann man beispielsweise JSON mit Datenbankmitteln generieren? Wie lassen sich JSON Inhalte in der Datenbank mit PL/SQL Mitteln manipulieren. Wie kann man schnell und einfach relationale Views erzeugen? Wie lässt sich umfassend in JSON suchen? Für hohe Performance Anforderungen ist zusätzlich zu den bestehenden In-Memory Zugriffen ein spezielles optimiertes JSON Dokument Handling in 12.2. implementiert worden.
Folgende Abschnitte geben einen Einblick in ausgewählte neue Funktionen der Oracle Database 12.2 und illustrieren die Verwendung an kleinen Code Beispielen. Um ein vollständiges Bild zu bekommen, empfehle ich die Links im letzten Abschnitt.
JSON Daten generieren mit JSON/SQL Operatoren
Kennt man die entsprechenden Werkzeuge, lassen sich auch schon vor 12.2 JSON Daten aus der Datenbank generieren. Arbeitet man beispielsweise mit APEX kann man JSON Dokumente mit der Funktion APEX_JSON erzeugen. Eine andere Möglichkeit bietet das Linemode Werkzeug SQLcl. Mit dem Format Kommando SET SQLFORMAT JSON wird beispielsweise automatisch eine Ausgabe im JSON Format angezeigt. In 12.2 gibt es nun eine dritte Möglichkeit unter Verwendung reiner SQL Mittel und den neuen SQL/JSON Operatoren wie JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECT und JSON_OBJECTAGG. Schauen wir uns ein paar einfache Beispiele dazu an. Mit JSON_ARRAY wird ein zugehöriges JSON Feld ausgegeben, JSON_OBJECT hingegen erzeugt ein "Key:Value" Paar aus jeder referenzierten Spalte.
SQL> select JSON_ARRAY(empno, ename, mgr, deptno) emp
from scott.emp where deptno=10;
EMP
--------------------------------------------------------------------------------
[7782,"CLARK",7839,10]
[7839,"KING",10]
[7934,"MILLER",7782,10]
SQL> select JSON_OBJECT('empid' is empno,'empname' is ename, 'Managerid' is mgr, 'Deptid' is deptno) emp
from scott.emp where deptno=10;
EMP
--------------------------------------------------------------------------------
{"empid":7782,"empname":"CLARK","Managerid":7839,"Deptid":10}
{"empid":7839,"empname":"KING","Managerid":null,"Deptid":10}
{"empid":7934,"empname":"MILLER","Managerid":7782,"Deptid":10}
SQL> select JSON_OBJECT(object_type is object_name,'Status' is STATUS) from USER_OBJECTS
where OBJECT_TYPE like 'PACK%';
JSON_OBJECT(OBJECT_TYPEISOBJECT_NAME,'STATUS'ISSTATUS)
--------------------------------------------------------------------------------
{"PACKAGE":"PACK1","Status":"VALID"}
{"PACKAGE":"STOCK_TRACK_PACK","Status":"VALID"}
{"PACKAGE BODY":"STOCK_TRACK_PACK","Status":"VALID"}
Interessant ist auch die Möglichkeit ein "Key:Value" Paar bzgl. einer bestimmten Spalte zu aggregieren. Um die reine Funktionsweise aufzuzeigen, bleiben wir bei der einfachen EMP Tabelle. Wir haben 14 Angestellte, mit 6 verschiedenen Managern. Im nächsten Beispiel geben wir pro Manager die entsprechenden Angestellten mit Berufsbezeichnung als "Key:Value" Paar aus. Das Ergebnis sieht dann folgendermassen aus.
SQL> select json_objectagg (ename, job) employees_per_manager from emp
group by mgr;
EMPLOYEES
--------------------------------------------------------------------------------
{"SCOTT":"ANALYST","FORD":"ANALYST"}
{"ALLEN":"SALESMAN","JAMES":"CLERK","TURNER":"SALESMAN","MARTIN":"SALESMAN","WARD":"SALESMAN"}
{"MILLER":"CLERK"}
{"ADAMS":"CLERK"}
{"JONES":"MANAGER","CLARK":"MANAGER","BLAKE":"MANAGER"}
{"SMITH":"CLERK"}
{"KING":"PRESIDENT"}
Natürlich lassen sich diese Operatoren auch miteinander kombinieren und ergänzen. Weitere Beispiele sind im JSON Handbuch, in Artikeln im Internet oder auch auf Github zu finden (siehe auch Informationen unten).
PL/SQL Integration mit JSON Datenstrukturen
Eine neue PL/SQL API in 12.2 ermöglicht einen Zugriff auf JSON Dokumente, die in der Datenbank gespeichert sind. Damit ist es möglich "Key:Value" Paare hinzuzufügen, zu verändern oder zu löschen. Man kann durch ein JSON Dokument navigieren vergleichbar mit dem XML Document Object Model (DOM). Die neuen Objekttypen mit ihren zugehörigen Funktionen können bei der Umsetzung hilfreich sein: JSON_ELEMENT_T, JSON_OBJECT_T und JSON_ARRAY_T. JSON_OBJECT_T und JSON_ARRAY_T sind eine Erweiterung des Objekttyps JSON_ELEMENT_T. Ein DESCRIBE auf die drei Objekttypen gibt erste Hinweise auf die Verwendungsmöglichkeiten. Der Basistyp JSON_ELEMENT_T liefert beispielsweise eine PARSE Funktion um die interne Repräsentation für die weitere Verarbeitung zur Verfügung zu stellen.
SQL> desc json_element_t JSON_ELEMENT_T is NOT FINAL JSON_ELEMENT_T is NOT INSTANTIABLE Name Null? Type ----------------------------------------- -------- ---------------------------- DOM SYS.JDOM_T METHOD ------ STATIC FUNCTION PARSE RETURNS JSON_ELEMENT_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JSN VARCHAR2 IN
Darüberhinaus existiert ein spezielles Error Handling, PUT und GET Methoden, Clone und Rename/Remove Funktionen, um nur einige Beispiele zu nennen. Da die Erläuterung der einzelnen Methoden und Konstruktoren zu weit führen würde, möchte ich an einem einfachen Beispiel die generelle Vorgehensweise demonstrieren. Wir verwenden dazu die Beispielumgebung aus dem Community Beitrag "JSON in der Datenbank mit Basisfunktionen". Im folgenden wird noch einmal ein kurze Beschreibung der Vorgehensweise gegeben:
-- Testdaten beispielsweise von https://github.com/oracle/db-sample-schemas/blob/master/order_entry/PurchaseOrders.dmp SQL> create table json_dump_file_contents (json_document CLOB) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY home ACCESS PARAMETERS (RECORDS DELIMITED BY 0x'0A' BADFILE 'JSONDumpFile.bad' LOGFILE 'JSONDumpFile.log' FIELDS (json_document CHAR(5000))) LOCATION ('PurchaseOrders.dmp')) REJECT LIMIT UNLIMITED; Table created SQL> create table json_tab (id number generated as identity, json_document clob constraint ensure_json CHECK (json_document IS JSON)); Table created. SQL> insert into json_tab (json_document) select json_document from json_dump_file_contents; 9999 rows created.
In der Spalte JSON_DOCUMENT, in der die JSON Daten gespeichert sind, wird der Wert des Keys User verändert (von 'CJOHNSON' in den Wert 'USCHWINN). Sinnvoll ist es dabei auch, den Wert vorher und nachher wie folgt abzuprüfen.
select json_document
from json_tab
where json_value(json_document,'$.User')='USCHWINN';
declare
cursor getDocuments
is
select JSON_DOCUMENT
from JSON_TAB j
where json_value(json_document,'$.User') = 'CJOHNSON' for UPDATE;
V_RESULT JSON_ELEMENT_T;
V_DOCUMENT_OBJECT JSON_OBJECT_T;
V_NEW_DOCUMENT VARCHAR2(4000);
begin
for doc in getdocuments loop
-- setup of the internal representation
V_RESULT := JSON_ELEMENT_T.parse(doc.json_DOCUMENT);
V_DOCUMENT_OBJECT := treat(V_RESULT as JSON_OBJECT_T);
if (V_Document_OBJECT.get_String('User') = 'CJOHNSON') then
V_document_object.put('User','USCHWINN');
end if;
-- transformation to a string
V_NEW_DOCUMENT := V_DOCUMENT_OBJECT.to_string();
-- update operation
update %TABLE_NAME%
set JSON_DOCUMENT = V_NEW_DOCUMENT
where current of getDocuments;
commit;
end loop;
end;
/
Eine genaue Erläuterung findet sich im Database PL/SQL Packages and Types Reference im Kapitel JSON Data Structures. Weitere Beispiele sind auf Github zu finden.
View Erzeugen und Textsuche ganz einfach mit der neuen Struktur Data Guide
Hat man mit großen und unübersichtlichen JSON Daten zu tun, stellt sich schnell die Frage, wie man darin performant nach einem Wert oder Eintrag suchen kann. Auch die Möglichkeit eine relationale Sichtweise auf JSON Dokumente zur Verfügung zu stellen - wie im Falle von XML - ist eine übliche Fragestellung. Die Basisfunktionen in 12.1 liefern schon erste Möglichkeiten diese Anforderungen zu lösen. Allerdings ist die Handhabung etwas unhandlich. Eine Vereinfachung und weitere Funktionen verspricht nun das neue Konzept in 12.2. Die Metadaten eines JSON Dokuments können nun im Data Dictionary gespeichert werden. Optional wird ein zugehöriger Index zur Volltextsuche zur Verfügung gestellt. Die neue Struktur hat den treffenden Namen Data Guide. Damit ist es nun möglich schnell und einfach mit einem Befehl relationale Views zu erzeugen, eine performante Suche im Text zu gestalten und sogar automatisch virtuelle Spalten zu einer View hinzuzufügen sobald sich der Inhalt des JSON Dokuments erweitert.
Starten wir mit dem Erzeugen eines Data Guides. Auch hier nehmen wir die Tabelle JSON_TAB als Grundlage. 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 (erstes Beispiel), die einige Defaulteinstellungen mit sich bringt - wie zum Beispiel das automatische Erzeugen eines Textindex. Ein paar weitere Beispiele sind ebenfalls gelistet.
create search index JSON_TAB_GUIDE
on JSON_TAB (JSON_DOCUMENT) for json;
-- ohne Oracle Text Index (auch Search Index)
create search index JSON_TAB_GUIDE ON JSON_TAB (JSON_DOCUMENT) for json
PARAMETERS ('DATAGUIDE ON SEARCH_ON NONE');
-- mit Search Index und Synchronisierung bei DML nach COMMIT
CREATE SEARCH INDEX JSON_TAB_GUIDE ON JSON_TAB (JSON_DOCUMENT) for json
PARAMETERS ('DATAGUIDE ON SYNC (ON COMMIT)')
-- ohne Data Guide nur Search Index
create search index JSON_TAB_GUIDE ON JSON_TAB (JSON_DOCUMENT) for json
PARAMETERS ('DATAGUIDE OFF SEARCH_ON TEXT')
Es dauert eine kleine Weile, bis die Strukturen analysiert und die zugehörigen neuen Objekte im Data Dictionary angelegt worden sind. Kontrolliert man USER_OBJECTS wird man feststellen, dass eine ganze Reihe neuer zusätzlicher Objekte wie Indizes, Tabellen und LOBs erzeugt wurden. Die Oracle Text Experten werden auch einen entsprechenden Eintarg in CTX_USER_INDEXES finden.
SQL> select object_name, object_type from user_objects where created>= sysdate-1/24; OBJECT_NAME OBJECT_TYPE ------------------------------ ----------------------- DR$JSON_TAB_GUIDE$I TABLE JSON_TAB_GUIDE INDEX SYS_IL0000098698C00006$$ INDEX ... DR$JSON_TAB_GUIDE$SNI INDEX DR$JSON_TAB_GUIDE$STI INDEX 30 rows selected.
Genaue Informationen über die Syntax findet man übrigens im Oracle Text Reference Guide. So kann man beispielsweise auf das Erzeugen eines Text Index verzichten, die Synchronisierung beeinflussen u.v.m.
Kennt man sich zusätzlich etwas mit Oracle Text Funktionen aus, kann man einige Parallelen in der Syntax und der weiteren Verwendung erkennen.
Wo liegen nun die Strukturen und was genau wird gespeichert? Die Strukturen sind beispielsweise in USER_JSON_DATAGUIDES zu finden oder lassen sich über die neue Funktion DBMS_JSON.GET_INDEX_DATAGUIDE ausgeben.
SQL> set pagesize 100 long 10000
SQL> select dbms_json.get_index_dataguide (
OWNER => 'SCOTT',
TABLENAME => 'JSON_TAB',
JCOLNAME => 'JSON_DOCUMENT',
FORMAT => dbms_json.format_hierarchical,
PRETTY => dbms_json.pretty
) data_guide
from dual;
DATA_GUIDE
--------------------------------------------------------------------------------
{
"type" : "object",
"properties" :
{
"User" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$User"
},
"PONumber" :
{
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$PONumber"
},
"LineItems" :
{
"type" : "array",
"o:length" : 1024,
"o:preferred_column_name" : "JSON_DOCUMENT$LineItems",
"items" :
{
"properties" :
{
"Part" :
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "JSON_DOCUMENT$Part",
"properties" :
{
"UPCCode" :
{
"type" : "number",
"o:length" : 16,
"o:preferred_column_name" : "JSON_DOCUMENT$UPCCode"
},
"UnitPrice" :
{
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$UnitPrice"
},
"Description" :
{
"type" : "string",
"o:length" : 128,
"o:preferred_column_name" : "JSON_DOCUMENT$Description"
}
}
},
"Quantity" :
{
"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "JSON_DOCUMENT$Quantity"
},
"ItemNumber" :
{
"type" : "number",
"o:length" : 1,
"o:preferred_column_name" : "JSON_DOCUMENT$ItemNumber"
}
}
}
},
"Reference" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$Reference"
},
"Requestor" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$Requestor"
},
"CostCenter" :
{
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "JSON_DOCUMENT$CostCenter"
},
"AllowPartialShipment" :
{
"type" : "boolean",
"o:length" : 4,
"o:preferred_column_name" : "JSON_DOCUMENT$AllowPartialShipment"
},
"ShippingInstructions" :
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "JSON_DOCUMENT$ShippingInstructions",
"properties" :
{
"name" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$name"
},
"Phone" :
{
"type" : "array",
"o:length" : 128,
"o:preferred_column_name" : "JSON_DOCUMENT$Phone",
"items" :
{
"properties" :
{
"type" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$type"
},
"number" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "JSON_DOCUMENT$number"
}
}
}
},
"Address" :
{
"oneOf" :
[
{
"type" : "string",
"o:length" : 1,
"o:preferred_column_name" : "JSON_DOCUMENT$Address"
},
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "JSON_DOCUMENT$Address_1",
"properties" :
{
"city" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$city"
},
"state" :
{
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "JSON_DOCUMENT$state"
},
"county" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$county"
},
"street" :
{
"type" : "string",
"o:length" : 64,
"o:preferred_column_name" : "JSON_DOCUMENT$street"
},
"country" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$country"
},
"zipCode" :
{
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$zipCode"
},
"postcode" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DOCUMENT$postcode"
},
"province" :
{
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "JSON_DOCUMENT$province"
}
}
}
]
}
}
},
"Special Instructions" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "JSON_DOCUMENT$SpecialInstructions"
}
}
}
Das Ganze sieht schon sehr vielversprechend aus: Es werden die einzelnen Elemente, Datentypen und mögliche Spaltennamen (hier "JSON_DOCUMENT$User") ausgegeben. Das Package DBMS_JSON kann dann im nächsten Schritt zur Erzeugung einer Standard View (hier: JSON_PO_VIEW) dienen.
begin
dbms_json.create_view_on_path (
viewName => 'JSON_PO_VIEW',
tableName => 'JSON_TAB',
jcolname => 'JSON_DOCUMENT',
path => '$');
end;
Ein Blick auf die Objekte des Users beweist, dass wir damit die neue View JSON_PO_VIEW erzeugt haben. Die Spaltennamen lassen sich anschliessend mit der Prozedur DBMS_JSON.RENAME_COLUMN umbennen.
SQL> desc JSON_PO_VIEW Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER JSON_DOCUMENT$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) JSON_DOCUMENT$city VARCHAR2(32) JSON_DOCUMENT$state VARCHAR2(2) JSON_DOCUMENT$county VARCHAR2(8) JSON_DOCUMENT$street VARCHAR2(64) JSON_DOCUMENT$country VARCHAR2(32) JSON_DOCUMENT$zipCode NUMBER JSON_DOCUMENT$postcode VARCHAR2(8) JSON_DOCUMENT$province VARCHAR2(2) JSON_DOCUMENT$SpecialInstructions VARCHAR2(32) JSON_DOCUMENT$UPCCode NUMBER JSON_DOCUMENT$UnitPrice NUMBER JSON_DOCUMENT$Description VARCHAR2(128) JSON_DOCUMENT$Quantity NUMBER JSON_DOCUMENT$ItemNumber NUMBER JSON_DOCUMENT$type VARCHAR2(8) JSON_DOCUMENT$number VARCHAR2(16)
Zum Abschluss möchte ich noch auf die Verwendung des neuen Textindex eingehen. Ein typisches Beispiel liefert eine Ad-hoc Abfrage mit JSON_EXISTS. Der Ausführungsplan bestätigt dabei die Verwendung des Textindex (siehe Operation DOMAIN INDEX).
Das letzte Beispiel zeigt eine Volltextsuche mit einem Oracle Text Operator. Die Aufgabe besteht darin die Dokumente zu finden, bei denen der Wert von Requestor auf "Sarah", "Sarath" oder einem ähnlichen Namen lautet. Hier bietet sich der Einsatz des Oracle Text Operators FUZZY oder SOUNDEX an. Das Ergebnis sieht dann folgendermaßen aus.
Möchte man mehr über die Möglichkeiten der Oracle Text Abfragen wissen, eignet sich die Lektüre des Oracle Text Handbuchs.
Hinweis zur Lizenzierung
Die JSON Funktionen in der Datenbank sind in allen Editionen (Standard und Enterprise) und Cloudangeboten der Datenbank ab Oracle Database 12c verfügbar. Es ist keine zusätzliche Lizenzierung erforderlich.
Weitere Informationen
- Community Beitrag: JSON in der Datenbank mit Basisfunktionen
- Oracle Magazine: JSON in Motion
- Beispiele auf github
- My Oracle Support: JSON Database Patch Bundle Details (Doc ID 1992767.1)
- Oracle Dojo #11: Arbeiten mit JSON und Oracle 12c
- JSON Data Structures im PL/SQL Guide
- JSON Developer's Guide
- Oracle Text Reference Guide
- Dojo #11: Arbeiten mit JSON und Oracle 12c
Zurück zum Anfang des Artikels
Zurück zur Community-Seite