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

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.

Donnerstag, 17. April 2008

Ein Einstieg in Oracle TEXT ...

Oracle TEXT ist eine in die Datenbank integrierte Volltextrecherche, die in allen Datenbankeditionen enthalten ist (kostet also nichts extra) und normalerweise ohne weitere Installation direkt zur Verfügung steht. Man kann in einem "normalen" Datenbankschema also sofort starten. Und da die meisten Oracle-Anwender oder Entwickler Oracle TEXT noch nicht kennen, wollen wir nun genau dies tun:
Das folgende SQL-Skript erzeugt eine Tabelle, fügt ein paar (kleine) "Dokumente" ein, erzeugt den Volltextindex und zeigt, wie man darin (sogar linguistisch) suchen kann ...
Zunächst: Tabelle anlegen und füllen - als "Dokumente" haben wir ein paar "Schlagzeilen" generiert ...
drop table texttabelle
/
drop sequence seq_texttabelle
/

create table texttabelle(
  id          number(10),
  dokument    clob
)
/

create sequence seq_texttabelle
/

insert into texttabelle values (seq_texttabelle.nextval, 'A-Partei gewinnt Wahl in Hansestadt');
insert into texttabelle values (seq_texttabelle.nextval, 'Terror in Nahost: Kriminalität steigt immer weiter an');
insert into texttabelle values (seq_texttabelle.nextval, 'Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr');
insert into texttabelle values (seq_texttabelle.nextval, 'Olympia rückt näher: Der Fackellauf ist in vollem Gange');
insert into texttabelle values (seq_texttabelle.nextval, 'Wer wird US-Präsident? Obama und Clinton machen Wahlkampf');
insert into texttabelle values (seq_texttabelle.nextval, 'Papst bestürzt über jüngsten Skandal!');
insert into texttabelle values (seq_texttabelle.nextval, 'Wahlkampf in den USA geht weiter:  Clinton und Obama LIVE zu sehen');
insert into texttabelle values (seq_texttabelle.nextval, 'Software-Kenntnisse werden immer wichtiger');
insert into texttabelle values (seq_texttabelle.nextval, 'Umfrage:  Alle wollen mehr Geld!');
insert into texttabelle values (seq_texttabelle.nextval, 'Der Papst liest seine erste Messe in den USA!');

commit
/
Nun wird der Index erzeugt ...
create index idx_text on texttabelle (dokument)
indextype is ctxsys.context
/
Fertig. Nun kann man suchen ... und das geht wie folgt:
  1. Die einfachste Variante: Suche nach einem Wort:
    SQL> select * from texttabelle where contains(dokument, 'Papst')>0;
    
            ID DOKUMENT
    ---------- ---------------------------------------------------------------
             6 Papst bestürzt über jüngsten Skandal!
            10 Der Papst liest seine erste Messe in den USA!
    
    2 Zeilen ausgewählt.
    
  2. Boole'sche Operatoren wie AND, OR, NOT gehen natürlich auch ...
    SQL> select * from texttabelle where contains(dokument, 'Papst and Skandal')>0;
    
            ID DOKUMENT
    ---------- ---------------------------------------------------------------------------
             6 Papst bestürzt über jüngsten Skandal!
    
    1 Zeile wurde ausgewählt.
    
  3. Nun wird's interessant: Wir suchen Dokumente, in denen jemand etwas "liest" ... und das kann sprachlich ja unterschiedlich aussehen ... (lesen, las, liest, gelesen, ...). Dazu gibt es in Oracle TEXT eine Wortstammsuche ...
    SQL> select * from texttabelle where contains(dokument, '$lesen')>0;
    
            ID DOKUMENT
    ---------- ------------------------------------------------------------------------
            10  Der Papst liest seine erste Messe in den USA!
    
    1 Zeile wurde ausgewählt.
    
  4. Oracle TEXT ist übrigens auch noch fehlertolerant ... wenn man den Fuzzy-Operator verwendet ... Suchen wir mal nach dem USA-"Wahlkrampf":
    SQL> select * from texttabelle where contains(dokument, '?Wahlkrampf')>0;
    
            ID DOKUMENT
    ---------- ------------------------------------------------------------------------
             5 Wer wird US-Präsident? Obama und Clinton machen Wahlkampf 
             7 Wahlkampf in den USA geht weiter:  Clinton und Obama ...
    
    2 Zeilen ausgewählt.
    
  5. Ein (vorerst) letztes Beispiel: Zwei Wörter sollen nah beeinander stehen ("nah" meint hier: es darf nur ein Wort dazwischen stehen):
    SQL> select * from texttabelle 
      2   where contains(dokument, 'NEAR((Clinton, Wahlkampf),2)')>0;
    
            ID DOKUMENT
    ---------- --------------------------------------------------------------------------------
             5 Wer wird US-Präsident? Obama und Clinton machen Wahlkampf 
    
    1 Zeile wurde ausgewählt.
    
Am besten probiert man es einfach mal aus ... mehr zum Thema kommt in Kürze ...

Beliebte Postings