Using the JSON Result Set Interface

16.7 Using the JSON Result Set Interface

The CTX_QUERY.RESULT_SET() and CTX_QUERY.RESULT_SET_CLOB_QUERY() APIs enable you to obtain query results with a single query, rather than running multiple CONTAINS() queries to achieve the same result. The two APIs are identical except that one uses a VARCHAR2 query parameter, and the other uses a CLOB query parameter to allow for longer queries.

Usage

The input Result Set Descriptor (RSD) query consists of the following parts:

$query - Use $query to specify a search query, the path constraints, and additional path based filter conditions. The $query part is supported only when a JSON search index exists on the column.
$search - Use $search to display the score ranked search results and their count. For a non-JSON Oracle Text full-text index, you can also specify the SDATA sections to project for the search results.
$facet - Use $facet to specify the facets for various paths of a JSON document or SDATA sections of a context indexed document. Facets bucketed by a single unique value and facets per user specified range buckets are supported. The facts can also be one of the aggregations like COUNT, MIN, etc.

The result set output is of the following format:

{
  "$count" : number ,               
  "$hit" :    
  [
    { 
      "score" : <search_score>,
      "rowid" : <rowid>,
      "project" : {"<sdata_name>" : <sdata_value>, … }
    },
    …
  ],
"$facets" : 
  [
    {"<field>" : [ ..., { "value" : <value_i>, "$uniqueCount" : <group_count_i>}, ...  ]},           
    {"<field>" : [ ..., { "bucket" : <bucket_object_i>, "<op>" : <group_count_i>}, ...  ]},
    {"<field>" : { "<op>" : <actual_value of the aggregation> } },
    …         
  ]
}