Sort Index Query Results
-
This article provides examples of sorting query results when querying a static-index.
-
Prior to this article, please refer to Sort dynamic queries results for dynamic-queries examples
and general knowledge about Sorting. -
All sorting capabilities provided for a dynamic query can also be used when querying a static-index.
-
In this page:
Order by index-field value
- Use
orderBy
ororderByDescending
to order the results by the specified index-field.
const products = await session
// Query the index
.query({ indexName: "Products/ByUnitsInStock" })
// Apply filtering (optional)
.whereGreaterThan("unitsInStock", 10)
// Call 'orderByDescending'
// Pass the index-field by which to order the results and the ordering type
.orderByDescending("unitsInStock", "Long")
.all();
// Results will be sorted by the 'unitsInStock' value in descending order,
// with higher values listed first.
class Products_ByUnitsInStock extends AbstractJavaScriptIndexCreationTask {
constructor() {
super();
this.map("Products", p => {
return {
unitsInStock: p.UnitsInStock
};
});
}
}
from index "Products/ByUnitsInStock"
where unitsInStock > 10
order by unitsInStock as long desc
Ordering Type:
-
If no ordering type is specified in the query then the server will apply the default lexicographical ordering.
-
In the above example, the ordering type was set to
Long
. -
Different ordering can be forced.
See section Force ordering type for all available ordering types.
The same syntax used with dynamic queries also applies to queries made on indexes.
Order results when index-field is searchable
-
When configuring an index-field for full-text search, the content of the index-field is broken down into terms at indexing time. The specific tokenization depends on the analyzer used.
-
When querying such index, if you order by that searchable index-field, results will come back sorted based on the terms, and not based on the original text of the field.
-
To overcome this, you can define another index-field that is not searchable and sort by it.
class Products_BySearchName extends AbstractJavaScriptIndexCreationTask {
constructor() {
super();
this.map("Products", p => {
return {
// Index-field 'name' will be configured below for full-text search
name: p.Name,
// Index-field 'nameForSorting' will be used for ordering query results
nameForSorting: p.Name
// Note:
// Both index-fields are assigned the same content (the 'Name' from the document)
};
});
// Configure only the 'name' index-field for FTS
this.index("name", "Search");
}
}
const products = await session
// Query the index
.query({ indexName: "Products/BySearchName"})
// Call 'search':
// Pass the index-field that was configured for FTS and the term to search for.
// Here we search for terms that start with "ch" within index-field 'name'.
.search("name", "ch*")
// Call 'orderBy':
// Pass the other index-field by which to order the results.
.orderBy("nameForSorting")
.all();
// Running the above query on the NorthWind sample data, ordering by 'NameForSorting' field,
// we get the following order:
// =========================================================================================
// "Chai"
// "Chang"
// "Chartreuse verte"
// "Chef Anton's Cajun Seasoning"
// "Chef Anton's Gumbo Mix"
// "Chocolade"
// "Jack's New England Clam Chowder"
// "Pâté chinois"
// "Teatime Chocolate Biscuits"
// While ordering by the searchable 'Name' field would have produced the following order:
// ======================================================================================
// "Chai"
// "Chang"
// "Chartreuse verte"
// "Chef Anton's Cajun Seasoning"
// "Pâté chinois"
// "Chocolade"
// "Teatime Chocolate Biscuits"
// "Chef Anton's Gumbo Mix"
// "Jack's New England Clam Chowder"
from index "Products/BySearchName"
where search(name, "ch*")
order by nameForSorting
Additional sorting options
-
When querying an index, the following sorting options are the same as when making a dynamic query.
-
Refer to the examples in the links below to see how each option is achieved.