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.

bucketby Attribute

• The only valid attribute is single in this release.

• The ‘single’ mode produces a list of all unique values for the facet and a document count for each value.

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.

Single Count

The following statements insert a few rows into the table mytab. 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>

sortby and order Attributes

• sortby supports the “count” and “value” attribute values.

  • 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>