Sorting Query Results

      +
      The FTS results are returned as objects. FTS query includes options to order the results.

      Sorting Result Data

      FTS sorting is sorted by descending order of relevance. It can , however, be customized to sort by different fields, depending on the application.

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

      Within a JSON query object, the required sort-type is specified by using the sort field.

      This takes an array of either strings, objects, or numeric as its value.

      Sorting with Strings

      You can specify the value of the sort field 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.

      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"
        }
      }

      The following example shows how the sort field accepts combinations of strings and objects as its value.

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

      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.

      • type: Specifies the type of the search-order field value. For example, string for text fields, date for DateTime fields, or number for numeric/geo fields.

      To fetch more accurate sort results, we strongly recommend specifying the type of the sort fields in the sort section of the search request.

      Example

      The example below shows how to specify the object-sort.

      {
        "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",
             "type": "number"
            }
         ]
      }
      The above sample assumes that the travel-sample bucket has been loaded, and a default index has been created on it.

      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 Creating Index from UI.

      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.

      The following example shows how the sort field accepts combinations of strings and objects as its value.

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

      Sorting with Numeric

      You can specify the value of the sort field as a numeric type. You can use the type field in the object that you specify with the sort.

      With type field, you can specify the type of the search order to numeric, string, or DateTime.

      Example

      The example below shows how to specify the object-sort with type field as number.

      {
        "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",
             "type": "number"
            }
         ]
      }

      Tips for Sorting with fields

      When you sort results on a field that is not indexed, or when a particular document is missing a value for that field, you will see the following series of Unicode non-printable characters appear in the sort field:

      \ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd

      The same characters may render differently when using a graphic tool or command line tools like jq.

            "sort": [
              "����������",
              "hotel_9723",
              "_score"
            ]

      Check your index definition to confirm that you are indexing all the fields you intend to sort by. You can control the sort behavior for missing attributes using the missing field.