Seit 12c (12.1.0.2) sind auch JSON Zugriffe in der Datenbank möglich. Die Verwendung ist ganz einfach: Man definiert eine Datenbankspalte mit einem beliebigen Datentyp für Textstrings (wie zum Beispiel VARCHAR2 oder CLOB). 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. Starten wir mit einem einfachen Beispiel. Wir laden uns die Datei PurchaseOrders.dmp mit JSON Dokumenten von Github und stellen diese via EXTERNAL TABLE Syntax zur Verfügung.
A character string. The condition searches for the character string in all of the string and numeric property values in the matched JSON object, including array values. The string must exist as a separate word in the property value. For example, if you search for 'beth', then a match will be found for string property value "beth smith", but not for "elizabeth smith". If you search for '10', then a match will be found for numeric property value 10 or string property value "10 main street", but a match will not be found for numeric property value 110 or string property value "102 main street".
Probieren wir eine erste Abfrage und überprüfen wir das Ganze mit dem Ausführungsplan.
Lust auf mehr JSON in der Oracle Datenbank? Weitere Informationen in deutscher Sprache finden Sie auch unter:
create table json_contents (json_document CLOB)
organization external
(type oracle_loader default directory json_dir
access parameters
(records delimited by 0x'0A'
fields (json_document CHAR(5000)))
location ('PurchaseOrders.dmp')) reject limit unlimited;
Im vorher definierten logischen Directory JSON_DIR liegt dabei PurchaseOrders.dmp. Danach legen wir eine relationale Tabelle an, die mithilfe der Bedingung IS JSON die Dokumente auf Gültigkeit validiert. Danach laden wir die Dokumente in die Tabelle JSON_TAB.create table json_tab
(id number generated as identity,
json_document clob constraint ensure_json CHECK (json_document IS JSON));
insert into json_tab (json_document)
select json_document from json_contents;
Commit;
Soweit so gut. Wie sieht es jetzt aber mit der Suche aus? In 12.2 ist dazu eine neue Datenstruktur eingeführt worden. Ein einfaches Beispiel demonstriert die Verwendung.create search index JSON_TAB_GUIDE
on JSON_TAB (JSON_DOCUMENT)
for json PARAMETERS ('DATAGUIDE ON SYNC (ON COMMIT)')
Was bewirkt diese Syntax? Wichtig für Oracle Text User ist die Information, dass damit eine neue Art von Text Index angelegt wird, der beim Commit synchronisiert wird - der neue JSON Search Index. Zusätzlich wird ein sogenannter Data Guide angelegt. Mit diesem wird die Struktur des JSON Dokuments im Data Dictionary hinterlegt. Diese Strukturen können manuell ausgelesen werden, zur automatischen View Erzeugung beitragen oder automatisch virtuelle Spalten anlegen bzw. löschen. Mehr dazu findet sich übrigens auch im Blockeintrag "JSON in 12.2: JSON Generierung, neues Data Guide Konzept, Objekttypen". Alle Syntaxformen und eine Erklärung dazu finden sich im Text Reference Guide. Aber listen wir einfach einmal die erzeugten Objekte auf. Einige davon kommen Ihnen sicher bekannt vor. Die Tabelle DR$JSON_TAB_GUIDE$I gibt beispielsweise die gespeicherten Tokens aus. Eine Erklärung aller Komponenten würde hier allerdings zu weit führen.SQL> select object_name, object_type from user_objects where object_name like 'DR$%' OBJECT_NAME OBJECT_TYPE ------------------------------ ----------------------- DR$JSON_TAB_GUIDE$DG TABLE DR$JSON_TAB_GUIDE$DGSQ SEQUENCE DR$JSON_TAB_GUIDE$G TABLE DR$JSON_TAB_GUIDE$H INDEX DR$JSON_TAB_GUIDE$I TABLE DR$JSON_TAB_GUIDE$K TABLE DR$JSON_TAB_GUIDE$KI INDEX DR$JSON_TAB_GUIDE$N TABLE DR$JSON_TAB_GUIDE$NI INDEX DR$JSON_TAB_GUIDE$R TABLE DR$JSON_TAB_GUIDE$RC INDEX DR$JSON_TAB_GUIDE$SN TABLE DR$JSON_TAB_GUIDE$SNI INDEX DR$JSON_TAB_GUIDE$ST TABLE DR$JSON_TAB_GUIDE$STI INDEX DR$JSON_TAB_GUIDE$U TABLE DR$JSON_TAB_GUIDE$UI INDEX DR$JSON_TAB_GUIDE$X INDEXAlle bekannten CTX Views wie wie CTX_USER_INDEXES, CTX_USER_INDEX_VALUES usw. können nun zur Hilfe genommen werden um den Index näher zu beleuchten. Interessant sind in erster Linie aber die Abfrage Möglichkeiten. Im Unterschied zu den "normalen" Textabfragen wird dazu nicht der Operator CONTAINS verwendet sondern die neue Bedingung JSON_TEXTCONTAINS, die im SQL Reference Guide beschrieben ist. In der Kurzfassung sieht die Syntax folgendermassen aus:
JSON_TEXTCONTAINS( column, JSON_basic_path_expression, string )Dabei gilt für den Suchstring folgendes:
A character string. The condition searches for the character string in all of the string and numeric property values in the matched JSON object, including array values. The string must exist as a separate word in the property value. For example, if you search for 'beth', then a match will be found for string property value "beth smith", but not for "elizabeth smith". If you search for '10', then a match will be found for numeric property value 10 or string property value "10 main street", but a match will not be found for numeric property value 110 or string property value "102 main street".
Probieren wir eine erste Abfrage und überprüfen wir das Ganze mit dem Ausführungsplan.
SQL> select distinct(json_value(json_document, '$.CostCenter'))from json_tab
where json_textcontains(json_document, '$.CostCenter', 'A40'); 2
(JSON_VALUE(JSON_DOCUMENT,'$.COSTCENTER'))
----------------------------------------------------------------------------------------------------
A40
SQL> select * from table (dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 8su4ht9v5ks1a, child number 0
-------------------------------------
select distinct(json_value(json_document, '$.CostCenter'))from json_tab
where json_textcontains(json_document, '$.CostCenter', 'A40')
Plan hash value: 333039129
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | HASH UNIQUE | | 1 | 1997 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JSON_TAB | 5 | 9985 | 7 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX | JSON_TAB_GUIDE | | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'A40 INPATH
(/CostCenter)')>0)
Offensichtlich wird der Index verwendet. Aber auch typische Oracle Text Abfrage Operatoren wie NEAR, FUZZY, $ können verwendet werden. Folgende Beispiele lassen sich dazu einfach ausprobieren.... json_textcontains(json_document,'$','Sporting near green') ... ... json_textcontains(json_document,'$','Sporting near green') ... ... json_textcontains(json_document,'$.ShippingInstructions','fuzzy(fransesco)') ... ... json_textcontains(json_document, '$.LineItems', '$tie') ...Dabei verwendet auch der neue Operator JSON_EXISTS, den neuen JSON Search Index, wie folgendes Beispiel zeigt.
SQL>select distinct(json_value(json_document, '$.ShippingInstructions.Address.zipCode')) from json_tab
where json_exists(json_document,'$.ShippingInstructions.Address.zipCode');
(JSON_VALUE(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.ADDRESS.ZIPCODE'))
----------------------------------------------------------------------------------------------------
26192
98199
99236
SQL> select * from table (dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID brjtjcwgqz2rc, child number 0
-------------------------------------
select distinct(json_value(json_document,
'$.ShippingInstructions.Address.zipCode')) from json_tab where
json_exists(json_document,'$.ShippingInstructions.Address.zipCode')
Plan hash value: 333039129
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | HASH UNIQUE | | 1 | 1997 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JSON_TAB | 5 | 9985 | 7 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX | JSON_TAB_GUIDE | | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'HASPATH(/ShippingInstruct
ions/Address/zipCode)')>0)
Lust auf mehr JSON in der Oracle Datenbank? Weitere Informationen in deutscher Sprache finden Sie auch unter:
