Sort Query Results
-
When making a query, the server will return the results sorted only if explicitly requested by the query.
If no sorting method is specified when issuing the query then results will not be sorted.- Note: An exception to the above rule is when Boosting is involved in the query.
Learn more in Automatic score-based ordering.
- Note: An exception to the above rule is when Boosting is involved in the query.
-
Sorting is applied by the server after the query filtering stage.
Applying filtering is recommended as it reduces the number of results RavenDB needs to sort
when querying a large dataset. -
Multiple sorting actions can be chained.
-
This article provides examples of sorting query results when making a dynamic-query.
For sorting results when querying a static-index see sort index query results. -
In this page:
Order by field value
- Use
orderBy
ororderByDescending
to order the results by the specified document-field.
const products = await session
// Make a dynamic query on the 'products' collection
.query({ collection: "products" })
// Apply filtering (optional)
.whereGreaterThan("UnitsInStock", 10)
// Call 'orderBy'
// Pass the document-field by which to order the results and the ordering type
.orderBy("UnitsInStock", "Long")
.all();
// Results will be sorted by the 'UnitsInStock' value in ascending order,
// with smaller values listed first.
from "Products"
where UnitsInStock > 10
order by UnitsInStock as long
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 Force ordering type below.
Order by score
-
When querying with some filtering conditions, a basic score is calculated for each item in the results
by the underlying indexing engine. -
The higher the score value the better the match.
-
Use
orderByScore
ororderByScoreDescending
to order by this score.
const products = await session
.query({ collection: "products" })
// Apply filtering
.whereLessThan("UnitsInStock", 5)
.orElse()
.whereEquals("Discontinued", true)
// Call 'orderByScore'
.orderByScore()
.all();
// Results will be sorted by the score value
// with best matching documents (higher score values) listed first.
from "Products"
where UnitsInStock < 5 or Discontinued == true
order by score()
Get resulting score:
The score details can be retrieved by either:
-
Request to include explanations:
You can get the score details and see how it was calculated by requesting to include explanations in the query. Currently, this is only available when using Lucene as the underlying indexing engine.
Learn more in Include query explanations. -
Get score from metadata:
-
The score is available in the
@index-score
metadata property within each result.
Note the following difference between the underlying indexing engines:-
When using Lucene:
This metadata property is always available in the results.
Read more about Lucene scoring here. -
When using Corax:
In order to enhance performance, this metadata property is Not included in the results by default.
To get this metadata property you must set the Indexing.Corax.IncludeDocumentScore configuration value to true.
Learn about the available methods for setting an indexing configuration key in this indexing-configuration article.
-
-
The following example shows how to get the score from the metadata of the resulting entities that were loaded to the session:
// Make a query: // ============= const employees = await session .query({ collection: "Employees"}) .search('Notes', 'English') .search('Notes', 'Italian') .boost(10) .all(); // Get the score: // ============== // Call 'getMetadataFor', pass an entity from the resulting employees list const metadata = session.advanced.getMetadataFor(employees[0]); // Score is available in the '@index-score' metadata property const score = metadata[CONSTANTS.Documents.Metadata.INDEX_SCORE];
-
Order by random
-
Use
randomOrdering
to randomize the order of the query results. -
An optional seed parameter can be passed.
const products = await session
.query({ collection: "products" })
.whereGreaterThan("UnitsInStock", 10)
// Call 'randomOrdering'
.randomOrdering()
// An optional seed can be passed, e.g.:
// .randomOrdering("someSeed")
.all();
// Results will be randomly ordered.
from "Products"
where UnitsInStock > 10
order by random()
// order by random(someSeed)
Order by spatial
-
If your data contains geographical locations,
spatial query results can be sorted based on their distance from a specific point. -
See detailed explanation in Spatial Sorting.
Order by count (aggregation query)
- The results of a group-by query can be sorted by the
count
aggregation operation used in the query.
const numberOfProductsPerCategory = await session
.query({ collection: "products" })
// Group by category
.groupBy("Category")
.selectKey("Category")
// Count the number of product documents per category
.selectCount()
// Order by the count value
.orderBy("count", "Long")
.all();
// Results will contain the number of Product documents per category
// ordered by that count in ascending order.
from "Products"
group by Category
order by count() as long
select key() as "Category", count()
Order by sum (aggregation query)
- The results of a group-by query can be sorted by the
sum
aggregation operation used in the query.
const numberOfUnitsInStockPerCategory = await session
.query({ collection: "products" })
// Group by category
.groupBy("Category")
.selectKey("Category")
// Sum the number of units in stock per category
.selectSum(new GroupByField("UnitsInStock", "sum"))
// Order by the sum value
.orderBy("sum", "Long")
.all();
// Results will contain the total number of units in stock per category
// ordered by that number in ascending order.
from "Products"
group by Category
order by sum as long
select key() as 'Category', sum(UnitsInStock) as sum
Force ordering type
-
If no ordering type is specified in the query then the server will apply the default lexicographical ordering.
-
A different ordering can be forced by passing the ordering type explicitly to
orderBy
ororderByDescending
. -
The following ordering types are available:
Long
Double
AlphaNumeric
String
(lexicographic ordering)
Using alphanumeric ordering example:
-
When ordering mixed-character strings by the default lexicographical ordering
then comparison is done character by character based on the Unicode values.
For example, "Abc9" will come after "Abc10" since 9 is greater than 1. -
If you want the digit characters to be ordered as numbers then use alphanumeric ordering
where "Abc10" will result after "Abc9".
const products = await session
.query({ collection: "products" })
// Call 'OrderBy', order by field 'QuantityPerUnit'
// Pass a second param, requesting to order the text alphanumerically
.orderBy("QuantityPerUnit", "AlphaNumeric")
.all();
from "Products"
order by QuantityPerUnit as alphanumeric
// Running the above query on the NorthWind sample data,
// would produce the following order for the QuantityPerUnit field:
// ================================================================
// "1 kg pkg."
// "1k pkg."
// "2 kg box."
// "4 - 450 g glasses"
// "5 kg pkg."
// ...
// While running with the default Lexicographical ordering would have produced:
// ============================================================================
// "1 kg pkg."
// "10 - 200 g glasses"
// "10 - 4 oz boxes"
// "10 - 500 g pkgs."
// "10 - 500 g pkgs."
// ...
Chain ordering
-
It is possible to chain multiple orderings in the query.
Any combination of secondary sorting is possible as the fields are indexed independently of one another. -
There is no limit on the number of sorting actions that can be chained.
const products = await session
.query({ collection: "products" })
.whereGreaterThan("UnitsInStock", 10)
// Apply the primary sort by 'UnitsInStock'
.orderByDescending("UnitsInStock", "Long")
// Apply a secondary sort by the score
.orderByScore()
// Apply another sort by 'Name'
.orderBy("Name")
.all();
// Results will be sorted by the 'UnitsInStock' value (descending),
// then by score,
// and then by 'Name' (ascending).
from "Products"
where UnitsInStock > 10
order by UnitsInStock as long desc, score(), Name
Custom sorters
-
The Lucene indexing engine allows you to create your own custom sorters.
Custom sorters are not supported by Corax. -
Custom sorters can be deployed to the server by either:
-
Sending the Put Sorters Operation from your code.
-
Uploading a custom sorter from Studio, see Custom Sorters View.
-
-
Once the custom sorter is deployed, you can sort the query results with it.
const products = await session
.query({ collection: "products" })
.whereGreaterThan("UnitsInStock", 10)
// Order by field 'UnitsInStock', pass the name of your custom sorter class
.orderBy("UnitsInStock", { sorterName: "MySorter" })
.all();
// Results will be sorted by the 'UnitsInStock' value
// according to the logic from 'MySorter' class
from "Products"
where UnitsInStock > 10
order by custom(UnitsInStock, "MySorter")
Syntax
// orderBy overloads:
orderBy(field);
orderBy(field, ordering);
orderBy(field, options);
// orderByDescending overloads:
orderByDescending(field);
orderByDescending(field, ordering);
orderByDescending(field, options);
Parameter | Type | Description |
---|---|---|
field | string |
The name of the field to sort by |
ordering | string |
The ordering type that will be used to sort the results:Long Double AlphaNumeric String (default) |
options | object |
An object that specifies the custom sorterName |