Overview of Filter Operator $orderby

Filter operator $orderby is described. It sorts query results in ascending or descending order.

You can specify the sort order for individual fields and the relative sort order among fields.

Operator $orderby can be used with two alternative syntaxes: array and abbreviated.

Regardless of the syntax choice, when you use $orderby in a filter specification together with one or more filter conditions, you must wrap those conditions with operator $query. In the queries shown here, the returned documents are restricted to those that satisfy a filter condition that specifies that field age must have a value greater than 40.

Using the Orderby Clause Array Syntax

The array syntax is the more straightforward of the two. You follow $orderby by an array of the fields to sort, in their relative sort order: the first array element specifies the first field to sort by, the second element specifies the second field to sort by, and so on.

The array syntax also lets you specify the SQL data type to use for sorting a given field, that is, how to interpret the field values, for sorting purposes.

For example, you can specify whether a field that has numeric codes (as a string or as a number) should be sorted lexicographically (as a string of digit characters) or numerically as a sequence of digits interpreted as a number). With "varchar2" as the sort data type, "100" sorts, in ascending order, before "9". With "number" as the sort type, "9" sorts, in ascending order, before "100", since the number 9 is smaller than the number 100.

Note: To use a datatype value of date or timestamp you need Oracle Database Release 18c or later.

Finally, the array syntax also lets you specify, for a string-valued field, a maximum number of characters at the start of the string. An error is raised if a string value for the targeted field is too long.

The following filter selects objects in which field salary is between 10,000 and 20,000, inclusive. It sorts the objects first by descending age, interpreted as a number, then by ascending name, interpreted as a string. An error is raised if the string value of field name is longer than 100 characters in any matching document. The default error handling also applies: raise an error if the value of any of the specified fields is not convertible to the specified datatype, but do not raise an error just because some of the specified fields are missing.

{ "$query"   : { "salary" : { "$gt" : 10000, "$lte" : 20000 } },
  "$orderby" : [ { "path" : "age",
                   "datatype" :  "number",
                   "order" : "desc" },
                 { "path" :      "name",
                   "datatype" :  "varchar2",
                   "order" :     "asc",
                   "maxLength" : 100 } ] }

The following filter is the same, except that it specifies scalarRequired = true, to require that field name be present in each matching document (as well as requiring that its value be convertible to a string). Otherwise, an error is raised at query time.

{ "$query"   : { "salary" : { "$gt" : 10000, "$lte" : 20000 } },
  "$orderby" : { "$fields" :
                 [ { "path" :      "age",
                     "datatype" :  "number",
                     "order" :     "desc" },
                   { "path" :      "name",
                     "datatype" :  "varchar2",
                     "order" :     "asc",
                     "maxLength" : 100 } ],
                 "$scalarRequired" : true } }

Using the Orderby Clause Abbreviated Syntax

The abbreviated syntax lets you list the fields to sort by and their relative sort order in a succinct way. You cannot use it to specify how to interpret the values of a given field for sorting purposes, that is, which data type to interpret the values as. And you cannot specify a maximum number of characters to take into account when sorting a string field.

The following filter specifies the order of fields age and name when sorting documents where the salary is between 10,000 and 20,000. A value of –1 specifies descending order for age. A value of 2 specifies ascending order for name. Sorting is done first by age and then by name, because the absolute value of –1 is less than the absolute value of 2 — not because -1 is less than 2, and not because field age appears before field name in the $orderby object.

{ "$query" : { "salary" : { $between [10000, 20000] } },
  "$orderby" :  { "age" : -1, "name" : 2 } }

Related Topics