Querying Facets by Using the Result Set Interface

15.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 15-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 the sortby attribute is set to value. In that case, the values are sorted according to the data type and the top n 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>