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.

Keine Kommentare:

Beliebte Postings