14 Using Faceted Navigation
Become familiar with the faceted navigation feature.
This chapter contains the following topics:
14.1 About Faceted Navigation
This feature implements group counts, also known as facets, which are frequently used in e-commerce or catalog applications. In various applications, it is preferable not only to display the list of hits returned by a query, but also to categorize the results.
For example, an e-commerce application wants to display all products matching a query for the term management along with faceting information. The facets include ‘type of product’ (books or DVDs), ‘author’, and ‘date’. For each facet, the application displays the unique values (books or DVDs) and their counts. You can quickly assess that most of the product offerings of interest fall under the ‘books’ category. You can further refine the search by selecting the ‘books’ value under ‘type of product’.
A group count is defined as the number of documents that have a certain value. If a value is repeated within the same document, the document contributes a count of 1 to the total group count for the value. Group counts or facets are supported for SDATA
sections that use optimized_for search
SDATA.
To request a computation of facets for a query, use the Result Set Interface.
14.2 Defining Sections As Facets
SDATA
refers to structured data. Group counts or facets are supported for SDATA
sections that you create with the optimized_for
attribute set to either ‘search’ or ‘sort and search’. In the MULTI_COLUMN_DATASTORE
preference, when data appears between tags or columns that are specified as optimized_for search
SDATA,
the data is automatically indexed as the facet data. Any data that does not match its declared type is handled according to the same framework that currently handles indexing errors for a specific row.
Examples
In the following statements, some tagged data is inserted into a VARCHAR2
column of a table. You can later define SDATA
sections to collect the data based on the tags used here.
-
Binary float or binary double with tag price:
insert into mytab values (1, 'red marble' <price>1.23</price>');
-
Time stamp with tag T:
insert into mytab values (1,'blue marbles <T>2012-12-05T05:20:00</T>');
In the following statements, a section group is created and various SDATA
section groups are added. The section definition includes the section group to which it belongs, the name of the section, the tag to be looked for, and the data type.
exec ctx_ddl.create_section_group('sg','BASIC_SECTION_GROUP')
exec ctx_ddl.add_SDATA_section('sg','sec01','name', 'varchar2')
exec ctx_ddl.add_SDATA_section('sg','sec02','count', 'number')
exec ctx_ddl.add_SDATA_section('sg','sec03','date', 'date')
exec ctx_ddl.add_SDATA_section('sg','sec04','timestamp', 'timestamp')
exec ctx_ddl.add_SDATA_section('sg','sec05','new price', 'binary_double')
exec ctx_ddl.add_SDATA_section('sg','sec06','old price','binary_float')
exec ctx_ddl.add_SDATA_section('sg','sec07','timestamp','timestamp with time zone')
The name given to the facet is ‘sec01’
and the ‘name’
tag is the actual tag name that occurs inside the document that is to be indexed. The ‘date’
, ‘timestamp’
, and ‘timestamp with time zone’
data types require the input data to be in the standard ISO format.
See Also:
Oracle Database Globalization Support Guide for more information about the standard ISO formats
Example 14-1 Using Faceted Navigation
The following statements create a table named products:
drop table products;
create table products(name varchar2(60), vendor varchar2(60), rating number, price number, mydate date);
The following statement inserts values into products:
insert all
into products values ('cherry red shoes', 'first vendor', 5, 129, sysdate)
into products values ('bright red shoes', 'first vendor', 4, 109, sysdate)
into products values ('more red shoes', 'second vendor', 5, 129, sysdate)
into products values ('shoes', 'third vendor', 5, 109, sysdate)
select * from dual;
The following statements create a MULTI_COLUMN_DATASTORE
preference named ds
to bring various other columns into the index (name
) to be used as facets:
/*A MULTI_COLUMN_DATASTORE automatically adds tags by default so that the text to be indexed looks like
'<name>cherry red shoes</name><vendor>first vendor</vendor><rating> .... '*/
exec ctx_ddl.drop_preference ('ds')
exec ctx_ddl.create_preference('ds', 'MULTI_COLUMN_DATASTORE')
exec ctx_ddl.set_attribute ('ds', 'COLUMNS', 'name, vendor, rating, price, mydate')
Note:
Oracle does not allow table columns with binary_float, binary_double, timestamp,
and timestamp with timezone
data types. It is therefore difficult to use such data types with MULTI_COLUMN_DATASTORE.
You can still create facets if the document contains tagged data for these data types. Alternatively, you can convert 'timestamp' columns to 'date' and you can store binary_float
and binary_double
as 'number'.
The following statements create a section group named sg
and enable the optimized_for search
attribute for each column to be treated as a facet:
/* A Section Group is created to specify the data type of each column (varchar2 is the default) and
how each column that is brought into the index should be used.*/
exec ctx_ddl.drop_section_group ('sg')
exec ctx_ddl.create_section_group ('sg', 'BASIC_SECTION_GROUP')
exec ctx_ddl.add_sdata_section ('sg', 'vendor', 'vendor', 'VARCHAR2')
exec ctx_ddl.add_sdata_section ('sg', 'rating', 'rating', 'NUMBER')
exec ctx_ddl.add_sdata_section ('sg', 'price', 'price', 'NUMBER')
exec ctx_ddl.add_sdata_section ('sg', 'mydate', 'mydate', 'DATE')
exec ctx_ddl.set_section_attribute('sg', 'vendor', 'optimized_for', 'SEARCH')
exec ctx_ddl.set_section_attribute('sg', 'rating', 'optimized_for', 'SEARCH')
exec ctx_ddl.set_section_attribute('sg', 'price', 'optimized_for', 'SEARCH')
exec ctx_ddl.set_section_attribute('sg', 'mydate', 'optimized_for', 'SEARCH')
The following statement creates an index on name
and specifies the preferences by using the PARAMETERS
clause:
CREATE INDEX product_index ON products (name)
INDEXTYPE IS ctxsys.context
PARAMETERS ('datastore ds section group sg');
The following statements query for a product name, ‘red shoes’ and the facets for computation can be specified. The count
attribute shows the total number of items that match the query for the product. The Result Set Interface specifies various requirements, such as the top vendors that have the largest number of matching items, the lowest available prices, and the latest arrivals:
set long 500000
set pagesize 0
variable displayrs clob;
declare
rs clob;
begin
ctx_query.result_set('product_index', 'red shoes', '<ctx_result_set_descriptor>
<count/>
<group sdata="vendor" topn="5" sortby="count" order="desc">
<count exact="true"/>
</group>
<group sdata="price" topn="3" sortby="value" order="asc">
<count exact="true"/>
</group>
<group sdata="mydate" topn="3" sortby="value" order="desc">
<count exact="true"/>
</group>
</ctx_result_set_descriptor>',
rs);
/* Pretty-print the result set (rs) for display purposes.
It is not required if you are going to manipulate it in XML.*/
select xmlserialize(Document XMLType(rs) as clob indent size=2) into :displayrs from dual;
dbms_lob.freetemporary(rs);
end;
/
select :displayrs from dual;
The following is output:
<ctx_result_set>
<count>3</count>
<groups sdata="VENDOR">
<group value="first vendor">
<count>2</count>
</group>
<group value="second vendor">
<count>1</count>
</group>
</groups>
<groups sdata="PRICE">
<group value="109">
<count>1</count>
</group>
<group value="129">
<count>2</count>
</group>
</groups>
<groups sdata="MYDATE">
<group value="2017-12-06 05:44:54">
<count>3</count>
</group>
</groups>
</ctx_result_set>
14.3 Querying Facets by Using the Result Set Interface
Starting with Oracle Database Release 18c, the group-counting operation for a specified list of facets is provided. You can obtain the group counts for each single value by using the bucketby
attribute with its value set to single.
The topn,
sortby,
and order
attributes are also supported. Starting with Oracle Database Release 21c, you can obtain the group counts for a range of numeric and variable character facet values by using the range
element, which is a child element of the group
element.
bucketby Attribute
Valid attributes are single
and custom.
-
The 'single' mode produces a list of all unique values for the facet and a document count for each value.
-
The 'custom' mode produces document counts for a range of numeric values.
count Element (Single Count)
In the following example, a few rows are inserted into the mytab
table. Some rows have two values for the facet <B>,
and some rows have a single value.
begin
insert into mytab values (1, '<B>1.234</B><B>5</B>');
insert into mytab values (2, '<B>1.432</B>');
insert into mytab values (3, '<B>2.432</B><B>6</B>');
insert into mytab values (4, '<B>2.432</B>');
end;
Single counts show each unique value and the number of documents that have this value:
<ctx_result_set>
<groups sdata="SEC01">
<group value="2.432"><count>2</count></group>
<group value="1.234"><count>1</count></group>
<group value="5"><count>1</count></group>
<group value="6"><count>1</count></group>
<group value="1.432"><count>1</count></group>
</groups>
</ctx_result_set>
If document 1 is deleted, you see the following result:
<ctx_result_set>
<groups sdata="SEC01">
<group value="2.432"><count>2</count></group>
<group value="6"><count>1</count></group>
<group value="1.432"><count>1</count></group>
</groups>
</ctx_result_set>
range Element
The range
element supports start, greaterthan, end,
and lessthan
attributes. The start
and greaterthan
attributes specify the beginning value for the range. The end
and lessthan
attributes specify the ending value for the range.
Ranges can overlap each other. For example, <range start="1" end="2"/>
and <range start="2" end="3"/>.
Ranges can also be open ended. For example, you can specify only the start value or the end value. If you do not specify the attributes of the range
element, all results are returned.
Example 14-2 Obtaining Group Counts for a Range of Facets
Create a table named products
and populate it:
drop table products;
create table products(name varchar2(60), vendor varchar2(60), rating number, price number);
insert all
into products values ('cherry red shoes', 'first vendor', 5, 129)
into products values ('bright red shoes', 'first vendor', 4, 109)
into products values ('more red shoes', 'second vendor', 5, 129)
into products values ('shoes', 'third vendor', 5, 109)
into products values ('dark red shoes', 'fourth vendor', 3, 98)
into products values ('light red shoes', 'fifth vendor', 2, 49)
select * from dual;
Create a MULTI_COLUMN_DATASTORE
preference named ds
to bring various other columns into the index (name)
to be used as facets:
exec ctx_ddl.drop_preference ('ds')
exec ctx_ddl.create_preference('ds', 'MULTI_COLUMN_DATASTORE')
exec ctx_ddl.set_attribute ('ds', 'COLUMNS', 'name, vendor, rating, price')
Create a section group named sg
and enable the optimized_for search
attribute for each column to be treated as a facet:
exec ctx_ddl.drop_section_group ('sg')
exec ctx_ddl.create_section_group ('sg', 'BASIC_SECTION_GROUP')
exec ctx_ddl.add_sdata_section ('sg', 'rating', 'rating', 'NUMBER')
exec ctx_ddl.add_sdata_section ('sg', 'price', 'price', 'NUMBER')
exec ctx_ddl.add_sdata_section ('sg', 'vendor', 'vendor', 'VARCHAR2')
exec ctx_ddl.set_section_attribute('sg', 'rating', 'optimized_for', 'SEARCH')
exec ctx_ddl.set_section_attribute('sg', 'price', 'optimized_for', 'SEARCH')
exec ctx_ddl.set_section_attribute('sg', 'vendor', 'optimized_for', 'SEARCH')
Create an index on name
and specify the preferences by using the parameters
clause:
create index mytab_idx on products (name)
indextype is ctxsys.context
parameters ('datastore ds section group sg');
Query for a product name, ‘red shoes’ by setting the bucketby
attribute to custom
and provide the values for the range
element:
set long 500000
set pagesize 0
variable displayrs clob;
declare
rs clob;
begin
ctx_query.result_set('mytab_idx', 'red shoes', '<ctx_result_set_descriptor>
<group sdata="rating" bucketby="custom">
<range start="1" lessthan="10"/>
<range start="10" lessthan="20"/>
<range start="20"/>
</group>
<group sdata="price" bucketby="custom">
<range end="1"/>
<range greaterthan="1" end="10"/>
<range greaterthan="10" end="100"/>
<range greaterthan="100"/>
</group>
<group sdata="vendor" bucketby="custom">
<range greaterthan="a"/>
<range start="s"/>
<range end="f"/>
</group>
</ctx_result_set_descriptor>',
rs);
select xmlserialize(Document XMLType(rs) as clob indent size=2) into :displayrs from dual;
dbms_lob.freetemporary(rs);
end;
/
select :displayrs from dual;
The following is output:
<ctx_result_set>
<groups sdata="RATING">
<group value="range" start="1" lessthan="10">
<count>5</count>
</group>
<group value="range" start="10" lessthan="20">
<count>0</count>
</group>
<group value="range" start="20" end="5">
<count>0</count>
</group>
</groups>
<groups sdata="PRICE">
<group value="range" start="49" end="1">
<count>0</count>
</group>
<group value="range" greaterthan="1" end="10">
<count>0</count>
</group>
<group value="range" greaterthan="10" end="100">
<count>2</count>
</group>
<group value="range" greaterthan="100" end="129">
<count>3</count>
</group>
</groups>
<groups sdata="VENDOR">
<group value="range" greaterthan="a" end="second vendor">
<count>5</count>
</group>
<group value="range" start="s" end="second vendor">
<count>1</count>
</group>
<group value="range" start="fifth vendor" end="f">
<count>0</count>
</group>
</groups>
</ctx_result_set>
topn Attribute
-
Valid attribute values are non-negative numbers greater than zero.
-
This attribute specifies that only top
n
facet values and their counts are returned. -
Group count determines the top
n
values to return unless thesortby
attribute is set tovalue.
In that case, the values are sorted according to the data type and the topn
results of the sort are returned. The order attribute is respected for the sort. -
By default, the results are sorted by the group count in descending order.
-
If a tie occurs in the count, the ordering of the facet values within this tie is not guaranteed.
sortby and order Attributes
sortby
supports count
and value
attributes.
-
count
sorts by group counts (numbers). This is the default. -
value
sorts by value depending on the data type.
order
supports ASC
(ascending order) and DESC
(descending order), which is the default.
If there is no selection, the default is count DESC.
This example shows the grouping of a number facet if bucketby
is set to single,
where mytab_idx
is the name of the index, text
is the query, and group SDATA
requests the facets:
begin
ctx_query.result_set('mytab_idx', 'text',
'<ctx_result_set_descriptor>
<group sdata="sec01" topn = "4" sortby = "value" order="asc" bucketby="single">
<count/>
</group>
</ctx_result_set_descriptor>'
:rs);
end;
The following is a sample output showing that the values are listed in alphabetical order because the sortby
attribute is set to value
instead of count.
The values are also displayed in ascending order (ABC to XYZ) because the order
attribute is set to asc.
Only four values are displayed because the topn
attribute is set to 4.
<ctx_result_set>
<group SDATA="SEC01">
<group value="ABC"><count>2</count>
</group>
<group value="DEF"><count>1</count>
</group>
<group value="GHI"><count>10</count>
</group>
<group value="XYZ"><count>1</count>
</group>
</ctx_result_set>
14.4 Refining Queries by Using Facets As Filters
CONTAINS
queries with the standard set of database comparison operators available for SDATA. The following example is based on the ‘name’ varchar2 section. When you use it with numbers, do not use quotation marks around the numeric term to be searched.contains (text, 'SDATA(sec01 = "run")', 1) > 0
contains (text, 'SDATA(sec01 > "run")', 1) > 0
contains (text, 'SDATA(sec01 >= "run")', 1) > 0
contains (text, 'SDATA(sec01 < "run")', 1) > 0
contains (text, 'SDATA(sec01 <= "run")', 1) > 0
contains (text, 'SDATA(sec01 <> "run")', 1) > 0
contains (text, 'SDATA(sec01 != "run")', 1) > 0
contains (text, 'SDATA(sec01 between "run1" and "run2")', 1) > 0
contains (text, 'SDATA(sec01 not between "run1" and "run2")', 1) > 0
contains (text, 'SDATA(sec01 is null)', 1) > 0
contains (text, 'SDATA(sec01 is not null)', 1) > 0
contains (text, 'SDATA(sec01 like "%run")', 1) > 0
contains (text, 'SDATA(sec01 like "run%")', 1) > 0
contains (text, 'SDATA(sec01 like "%run%")', 1) > 0
contains (text, 'SDATA(sec01 not like "%run")', 1) > 0
contains (text, 'SDATA(sec01 not like "run%")', 1) > 0
contains (text, 'SDATA(sec01 not like "%run%")', 1) > 0
contains (text, 'SDATA(sec02 = 9)', 1) > 0
contains (text, 'SDATA(sec02 < 10)', 1) > 0
contains (text, 'SDATA(sec02 between 2 and 20)', 1) > 0
The comparison operators behave according to the current optimized_for search
SDATA
behavior for the various data types.
14.5 Multivalued Facets
If multiple values are in an optimized for search SDATA
section within the same document, then each value is indexed if the value is enclosed in its own tag corresponding to the SDATA section. Values that are not enclosed within separate section tags, but that appear together within the same section tag, are treated as a single value.
For example, in a document, <car>First Car, Second Car</car>
is treated as a single string of value ‘First Car, Second Car’. However, <car>First Car</car><car>Second Car</car>
is treated as two separate values for the document.