If you have created a JSON search index then you can also use PL/SQL procedure CTX_QUERY.result_set to perform facet search over JSON data. This search is optimized to produce various kinds of search hits all at once, rather than, for example, using multiple separate queries with SQL function contains.

To search using procedure CTX_QUERY.result_set you pass it a result set descriptor (RSD), which specifies (as a JSON object with predefined operator fields $query, $search, and $facet) the JSON values you want to find from your indexed JSON data, and how you want them grouped or aggregated. The values you can retrieve and act on are either JSON scalars or JSON arrays of scalars.

(Operator-field $query is also used in SODA query-by-example (QBE) queries. You can use operator $contains in the value of field $query for full-text matching similar to that provided by Oracle SQL condition json_textcontains.)

The RSD fields serve as an ordered template, specifying what to include in the output result set. (In addition to the found JSON data, a result set typically includes a list of search-hit rowids and some counts.)

A $facet field value is a JSON array of facet objects, each of which defines JSON data located at a particular path and perhaps satisfying some conditions, and perhaps an aggregation operation to apply to that data.

You can aggregate facet data using operators $count, $min, $max, $avg, and $sum. For example, $sum returns the sum of the targeted data values. You can apply an aggregation operator to all scalar values targeted by a path, or you can apply it separately to buckets of such values, defined by different ranges of values.

Finally, you can obtain the counts of occurrences of distinct values at a given path, using operator $uniqueCount.

For example, consider this $facet value:

[ {"$uniqueCount" : "zebra.name"},
  {"$sum"         : {"path"  : "zebra.price",                   
                     "bucket : [{"$lt"  : 3000},
                                {"$gte" : 3000}]},
  {"$avg"         : "zebra.rating"} ]

When query results are returned, the value of field $facet in the output is an array of three objects, with these fields:

• zebra.name — The number of occurrences of each zebra name.

• zebra.price — The sum of zebra prices, in two buckets: prices less than 3000 and prices at least 3000.

• zebra.rating — The average of all zebra ratings. (Zebras with no rating are ignored.)

[ {"zebra.name"   : [ {"value":"Zigs",
                       "$uniqueCount:2},                  
                      {"value":"Zigzag",
                       "$uniqueCount:1},             
                      {"value":"Storm",
                       "$uniqueCount:1} ]},
  {"zebra.price"  : [ {"value":1000,
                       "$uniqueCount:2},                  
                      {"value":3000,
                       "$uniqueCount:2},                  
                      {"value":2000,
                       "$uniqueCount:1} ]},
  {"zebra.rating" : {"$avg":4.66666666666666666667}} ]