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.
