Sorting Query Results

The results of Full Text Searches are returned as objects, the data within which can be sorted.

Sorting Result Data

On query-completion, sorting allows specified members of the result-set to be displayed prior to others: this facilitates review of the most significant data.

Within a JSON query object, the required sort-type is specified by using the sort field. This takes, as its value, an array of either strings or objects.

Sorting with Strings

The value of the sort field can be specified as an array of strings. These can be of three types:

  • field name: Specifies the name of a field. If multiple fields are included in the array, the sorting of documents begins according to their values for the field whose name is first in the array. If any number of these values are identical, their documents are sorted again, this time according to their values for the field whose name is second; then, if any number of these values are identical, their documents are sorted a third time, this time according to their values for the field whose name is third; and so on.

    Any document-field may be specified to hold the value on which sorting is to be based, provided that the field has been indexed in some way, whether dynamically or specifically.

    The default sort-order is ascending. If a field-name is prefixed with the - character, that field’s results are sorted in descending order.

  • _id: Refers to the document identifier. Whenever encountered in the array, causes sorting to occur by document identifer.

  • _score: Refers to the score assigned the document in the result-set. Whenever encountered in the array, causes sorting to occur by score.

For example:

"sort": ["country", "state", "city","-_score"]

This sort statement specifies that results will first be sorted by country. If some documents are then found to have the same value in their country fields, they are re-sorted by state. Next, if some of these documents are found to have the same value in their state fields, they are re-sorted by city. Finally, if some of these documents are found to have the same value in their city fields, they are re-sorted by score, in descending order.

The following JSON query demonstrates how and where the sort property can be specified:

{
  "explain": false,
  "fields": [
    "title"
  ],
  "highlight": {},
  "sort": ["country", "-_score","-_id"],
  "query":{
    "query": "beautiful pool"
  }
}

Sorting with Objects

Fine-grained control over sort-procedure can be achieved by specifying objects as array-values in the sort field. Each object can have the following fields:

  • by: Sorts results on id, score, or a specified field in the Full Text Index.

  • field: Specifies the name of a field on which to sort. Used only if field has been specified as the value for the by field; otherwise ignored.

  • missing: Specifies the sort-procedure for documents with a missing value in a field specified for sorting. The value of missing can be first, in which case results with missing values appear before other results; or last (the default), in which case they appear after.

  • mode: Specifies the search-order for index-fields that contain multiple values (in consequence of arrays or multi-token analyzer-output). The default order is undefined but deterministic, allowing the paging of results from from (`offset), with reliable ordering. To sort using the minimum or maximum value, the value of `mode should be set to either min or max.

The example below shows how an object-sort can be specified. It assumes that the travel-sample bucket has been loaded, and a default index has been created on it. This query sorts search-results based on reviews.ratings.Overall — a field that is normally multi-valued, because it contains an array of different users' ratings. When there are multiple values, the highest Overall ratings are used for sorting. Hotels with no Overall rating are placed at the end.

For information on loading sample buckets, see Sample Buckets. For instructions on creating a default Full Text Index by means of the Couchbase Web Console, see Performing Searches.

{
  "explain": false,
  "fields": [
     "*"
   ],
   "highlight": {},
   "query": {
     "match": "bathrobes",
     "field": "reviews.content",
     "analyzer": "standard"
   },
   "size" : 10,
   "sort": [
      {
       "by" : "field",
       "field" : "reviews.ratings.Overall",
       "mode" : "max",
       "missing" : "last"
      },
   ]
}

Note also that sort field accepts combinations of strings and objects as its value. This is demonstrated as follows:

{
   ...
   "sort": [
      "country",
      {
       "by" : "field",
       "field" : "reviews.ratings.Overall",
       "mode" : "max",
       "missing" : "last"
      },
      {
       "by" : "field",
       "field" : "reviews.ratings.Location",
       "mode" : "max",
       "missing" : "last"
      },
      "-_score"
   ]
}