Order clauses
JSONiq follows the W3C standard for order by clauses. The following explanations, provided as an informal summary for convenience, are non-normative.
Figure 56. OrderByClause
Order clauses are for reordering tuples.
For each incoming tuple, the expression in the where clause is evaluated to an atomic. The tuples are then sorted based on the atomics they are associated with, and then forwarded to the next clause.
Like for ordering comparisons, null values are always considered the smallest.
The following query is the counterpart of SQL's "SELECT * FROM captains ORDER BY name".
Example 123. An order by clause.
for $x in collection("captains")
order by $x.name
return $x
Result (run with Zorba): { "name" : "Benjamin Sisko", "series" : [ "The next generation", "Deep Space 9" ], "century" : 24 } { "name" : "James T. Kirk", "series" : [ "The original series" ], "century" : 23 } { "name" : "Jean-Luc Picard", "series" : [ "The next generation" ], "century" : 24 } { "name" : "Jonathan Archer", "series" : [ "Entreprise" ], "century" : 22 } { "name" : "Kathryn Janeway", "series" : [ "The next generation", "Voyager" ], "century" : 24 } { "name" : "Samantha Carter", "series" : [ ], "century" : 21 } { "codename" : "Emergency Command Hologram", "surname" : "The Doctor", "series" : [ "Voyager" ], "century" : 24 }
Multiple sorting criteria can be given - they are treated like a lexicographic order (most important criterium first).
Example 124. An order by clause.
for $x in collection("captains")
order by size($x.series), $x.name
return $x
Result (run with Zorba): { "name" : "Samantha Carter", "series" : [ ], "century" : 21 } { "name" : "James T. Kirk", "series" : [ "The original series" ], "century" : 23 } { "name" : "Jean-Luc Picard", "series" : [ "The next generation" ], "century" : 24 } { "name" : "Jonathan Archer", "series" : [ "Entreprise" ], "century" : 22 } { "codename" : "Emergency Command Hologram", "surname" : "The Doctor", "series" : [ "Voyager" ], "century" : 24 } { "name" : "Benjamin Sisko", "series" : [ "The next generation", "Deep Space 9" ], "century" : 24 } { "name" : "Kathryn Janeway", "series" : [ "The next generation", "Voyager" ], "century" : 24 }
It can be specified whether the order is ascending or descending. Empty sequences are allowed and it can be chosen whether to put them first or last.
Example 125. An order by clause.
for $x in collection("captains")
order by $x.name descending empty greatest
return $x
Result (run with Zorba): { "codename" : "Emergency Command Hologram", "surname" : "The Doctor", "series" : [ "Voyager" ], "century" : 24 } { "name" : "Samantha Carter", "series" : [ ], "century" : 21 } { "name" : "Kathryn Janeway", "series" : [ "The next generation", "Voyager" ], "century" : 24 } { "name" : "Jonathan Archer", "series" : [ "Entreprise" ], "century" : 22 } { "name" : "Jean-Luc Picard", "series" : [ "The next generation" ], "century" : 24 } { "name" : "James T. Kirk", "series" : [ "The original series" ], "century" : 23 } { "name" : "Benjamin Sisko", "series" : [ "The next generation", "Deep Space 9" ], "century" : 24 }
An error is raised if the expression does not evaluate to an atomic or the empty sequence.
Example 126. An order by clause.
for $x in collection("captains")
order by $x
return $x.name
Result (run with Zorba): An error was raised: can not atomize an object item: an object has probably been passed where an atomic value is expected (e.g., as a key, or to a function expecting an atomic item)
Collations can be used to give a specific way of how strings are to be ordered. A collation is identified by a URI.
Example 127. Use of a collation in an order by clause.
for $x in collection("captains")
order by $x.name collation "http://www.w3.org/2005/xpath-functions/collation/codepoint"
return $x.name
Result (run with Zorba): Benjamin Sisko James T. Kirk Jean-Luc Picard Jonathan Archer Kathryn Janeway Samantha Carter