Orderby Clause Sorts Selected Objects

A filter specification with an orderby clause returns the selected JSON documents in sorted order.

There are two ways of controlling the ordering behavior, with different orderby-clause syntaxes:

Orderby Clause Array Syntax

The simplest orderby array syntax is operator $orderby followed by an array of objects, each of which has a path field, which targets a particular field from the root of the candidate object, followed by at most one of each of these fields:

For example, this filter specification selects objects in which field salary has a value greater than 10,000 and less than or equal to 20,000. It sorts the objects first by descending age, interpreted as a number, then by ascending name, interpreted as a string.

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

The following SQL SELECT statement fragment is analogous:

WHERE (salary > 10000) AND (salary <= 20000) ORDER BY age DESC, name ASC

This syntax serves most purposes. No error is raised just because the targeted field is absent, and any other error encountered is raised.1

If you need to specify special handling of missing fields or errors then you need to use the more elaborate array syntax. This wraps the array in a $fields object, which lets you add another field, $scalarRequired or $lax, to the $orderby object. You cannot specify a true value for both $lax and $scalarRequired, or else a syntax error is raised at query time.

If neither scalarRequired nor lax is specified as true then the default error-handling behavior applies (no error is raised just because the targeted field is absent, and any other error encountered is raised).

For example, this filter specification has the same behavior as the preceding one, except that it raises an error if any of the targeted fields is missing.

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

Note: If you use Oracle Database Release 12c (12.1.0.2) then you must specify either $scalarRequired or $lax; otherwise a syntax error is raised.

Note: If you have defined a B-tree index for any of the fields targeted by a filter that has an orderby clause then that index must be specified with a true value of indexNulls for it to be picked up for that query.

See Also:

Orderby Clause Abbreviated Syntax

The abbreviated $orderby syntax specifies the fields to use for sorting, along with their individual directions and the order of sorting among the fields. It does not specify the SQL data types to use when interpreting field values for sorting, and it does not let you limit string sorting to the first N characters.

The orderby abbreviated syntax is $orderby followed by an object with one or more members, whose fields are used for sorting:

"$orderby" : { field1 : direction1, field2 : direction2, ... }

Each field is a string that is interpreted as a path from the root of the candidate object.

Each direction is a non-zero integer. The returned documents are sorted by the field value in ascending or descending order, depending on whether the value is positive or negative, respectively.

The fields in the $orderby operand are sorted in the order of their magnitudes (absolute values), smaller magnitudes before larger ones. For example, a field with value -1 sorts before a field with value 2, which sorts before a field with value 3. As usual, the order of the fields in the object value of $orderby is immaterial.

If the absolute values of two or more sort directions are equal then the order in which the fields are sorted is determined by the order in which they appear in the serialized JSON content that you use to create the JSON document.

Oracle recommends that you use sort directions that have unequal absolute values, to precisely govern the order in which the fields are used, especially if you use an external tool or library to create the JSON content and you are unsure of the order in which the resulting content is serialized.

This query acts like the one in Orderby Clause Array Syntax, except that interpretation of data types is not specified here, and (assuming that field name has string values) all characters in the name are used for sorting here. Note that the order of the object members is irrelevant here. In particular, it does not specify which field is sorted first — that is determined by the value magnitudes.

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

The following SQL SELECT statement fragment is analogous:

WHERE (salary >= 10000) AND (salary <= 20000) ORDER BY age DESC, name ASC

Related Topics

  1. The default error-handling behavior corresponds to the SQL/JSON semantics ERROR ON ERROR NULL ON EMPTY. ↩

  2. A true value of $scalarRequired corresponds to the use of SQL clause ERROR ON ERROR for a json_value expression. ↩

  3. A true value of $lax corresponds to the use of SQL clause NULL ON ERROR for a functional index created on a json_value expression. ↩