Posts mit dem Label json_textcontains werden angezeigt. Alle Posts anzeigen
Posts mit dem Label json_textcontains werden angezeigt. Alle Posts anzeigen

Donnerstag, 8. Juni 2017

Suche in JSON Dokumenten in 12.2: wie geht das?

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.
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            INDEX
Alle 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:

Mittwoch, 25. März 2015

JSON (ab 12.1.0.2) mit Oracle TEXT indizieren

Mit dem Patchset 12.1.0.2 wurde die JSON-Unterstützung in der Oracle-Datenbank eingeführt; auf dem Blog SQL und PL/SQL in Oracle ist dazu auch ein Posting erschienen. Gemeinsam mit dem SQL/JSON-Funktionen wurde auch eine JSON-Unterstützung in Oracle TEXT eingeführt. Um die soll es jetzt gehen: Angenommen, wir haben eine Tabelle JSON_TAB, welche die JSON-Dokumente als CLOB in der Spalte JSON enthält ...
SQL> desc json_tab
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 FILENAME                                           VARCHAR2(200)
 JSON                                               CLOB


SQL> select json from json_tab where rownum = 1;

JSON
--------------------------------------------------------------------------------
{"PurchaseOrder":{"$":{"xmlns:xsi":"http://www.w3.org/2001/XMLSchema-instance","
xsi:noNamespaceSchemaLocation":"http:/localhost:9021/public/XMLDEMO/purchaseOrde
r.xsd"},"Reference":["ADAMS-2001112712104128PST"],"Actions":[{"Action":[{"User":
["SCOTT"]}]}],"Reject":[""],"Requestor":["Julie P. Adams"],"User":["ADAMS"],"Cos
tCenter":["R20"],"ShippingInstructions":[{"name":["Julie P. Adams"],"address":["
300 Oracle Parkway\r\nRedwood Shores\r\nCA\r\n94065\r\nUSA"],"telephone":["650 5
06 7300"]}],"SpecialInstructions":["Hand Carry"],"LineItems":[{"LineItem":[{"$":
{"ItemNumber":"1"},"Description":["The Life of Brian"],"Part":[{"$":{"Id":"71551
5010320","UnitPrice":"39.95","Quantity":"2"}}]},{"$":{"ItemNumber":"2"},"Descrip
tion":["Hamlet"],"Part":[{"$":{"Id":"037429128428","UnitPrice":"29.95","Quantity
Wie man mit den SQL-Funktionen JSON_VALUE, JSON_QUERY oder JSON_TABLE in diese JSON-Dokumente "hineingreifen" kann, könnt Ihr in oben erwähntem Blog-Posting nachlesen - jetzt wollen wir aber die Volltextsuche ermöglichen - dazu erzeugen wir, wie immer, einen Oracle TEXT-Index und verwenden dazu die neue Section Group CTXSYS.JSON_SECTION_GROUP - JSON-Dokumente werden also ganz ähnlich wie XML-Dokumente indiziert.
create index ft_jsontable on json_tab (json) 
indextype is ctxsys.context 
parameters ('section group ctxsys.json_section_group');
Nach dem Indizieren werfen wir einen Blick in die Token-Tabelle ($I). JSON-Attributnamen werden (wie auch XML-Tags) als Token-Type 7, Daten als Token-Type 0 indiziert.
SQL> select token_text from DR$FT_JSONTABLE$I where token_type = 7 and rownum <= 100;

TOKEN_TEXT
----------------------------------------------------------------
Action
Actions
CostCenter
Description
Id
ItemNumber
:

SQL> select token_text from DR$FT_JSONTABLE$I where token_type = 0 and rownum <= 100;

TOKEN_TEXT
----------------------------------------------------------------
WALKABOUT
WARD
WASHINGTON
WATCHED
WAVE
WESTMINSTER
WHISPERS
WILD
:
Demzufolge kann man die JSON-Dokumente nun, wie auch XML-Dokumente, mit WITHIN abfragen ... und natürlich gehen auch Oracle Text-Funktionen wie die Fuzzy-Suche. Achtet bei euren WITHIN-Abfragen aber darauf, dass die Namen der JSON-Attribute auch für Oracle TEXT Case-Sensitiv sind. Die Daten, also der Token-Type 0 werden dagegen (normalerweise) Case-Insensitiv indiziert.
SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (Description)') > 0;

JSON_VALUE(JSON,'$.PURCHASEORDER.REFERENCE[0]')
--------------------------------------------------------------------------------
BLAKE-20021009123336231PDT
ALLEN-2002100912333742PDT
ALLEN-20021009123337553PDT
FORD-20021009123337463PDT
:

18 Zeilen ausgewählt.

SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (UnitPrice)') > 0;

Keine Zeilen ausgewählt.

SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where contains(json, '?Washingtn within (DESCRIPTION)') > 0;

Keine Zeilen ausgewählt.
Mit JSON_TEXTCONTAINS wurde noch ein zusätzlicher Query-Operator geschaffen; anstelle von CONTAINS mit WITHIN kann also auch eine Abfrage mit JSON_TEXTCONTAINS ausgeführt werden - letzterer erlaubt die Verwendung der gleichen JSON-Path-Expressions, wie sie auch in den SQL/JSON-Funktionen JSON_VALUE, JSON_QUERY und JSON_TABLE verwendet werden.
SQL> select json_value(json, '$.PurchaseOrder.Reference[0]') 
  2  from json_tab 
  3  where json_textcontains(json, $.PurchaseOrder.LineItems.LineItem.Description', 'Washington');

JSON_VALUE(JSON,'$.PURCHASEORDER.REFERENCE[0]')
--------------------------------------------------------------------------------
BLAKE-20021009123336231PDT
ALLEN-2002100912333742PDT
ALLEN-20021009123337553PDT
FORD-20021009123337463PDT
FORD-20021009123337653PDT
:

18 Zeilen ausgewählt.
Allerdings unterstützt JSON_TEXTCONTAINS nicht die Oracle TEXT Abfragesyntax; das bedeutet, dass man zwar standardisierte JSON-Pfadausdrücke verwenden kann, nicht jedoch Features wie Stemming, Fuzzy-Suche, UND/ODER-Kombinationen, Progressive Relaxation und vieles mehr. Insofern bleibt der "klassische" CONTAINS-Operator auch für JSON-Dokumente nach wie vor interessant. Viel Spaß beim Ausprobieren.

Beliebte Postings