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 onid
,score
, or a specifiedfield
in the Full Text Index. -
field
: Specifies the name of a field on which to sort. Used only iffield
has been specified as the value for theby
field; otherwise ignored. -
missing
: Specifies the sort-procedure for documents with a missing value in a field specified for sorting. The value ofmissing
can befirst
, in which case results with missing values appear before other results; orlast
(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). Thedefault
order is undefined but deterministic, allowing the paging of results fromfrom (`offset
), with reliable ordering. To sort using the minimum or maximum value, the value of `mode
should be set to eithermin
ormax
.
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" ] }