13.19 JSON_TRANSFORM Operator SORT
JSON_TRANSFORM
operator SORT
sorts the
elements of an array.
It sorts the elements of an array that's targeted by a SQL/JSON path expression, changing their order. The result includes all elements of the array (none are dropped); the only possible change is that they are reordered.
Array sorting is typically basic, sorting the elements by their values using the canonical JSON
-type sort order, as illustrated in the
following example.
Example 13-39 JSON_TRANSFORM: Sorting Elements In an Array By Their Values
The array elements are sorted here in descending order using the
canonical JSON
-type sort order.
SELECT json_transform('{"a":[ 1, null, 2, "cat", true, 3.1416 ]}',
SORT '$.a' DESC);
Result:
{"a":[ true, "cat", 3.1416, 3, 2, 1, null ]}
Besides such basic sorting, you can use path-directed sorting. In either case
you can use the keywords REMOVE NULLS
, which removes any JSON
null
values from the values to be sorted.
-
Basic element sort: Sort elements by their values, according to the canonical
JSON
-type sort order.The path to the targeted array is optionally followed by keyword
ASC
(default) orDESC
, for ascending or descending sort order, respectively, or by keywordREVERSE
, which means reverse the order of the elements.The path and optional keyword
ASC
orDESC
is optionally followed by keywordUNIQUE
, which means remove any duplicate array elements. -
Path-directed sort: Sort elements by the values that are targeted in an
ORDER BY
clause, which specifies one or more paths relative to the array.Each path is optionally followed by keyword
ASC
(default) orDESC
, for ascending or descending sort order, respectively.Sort each pair of elements first by comparing the values targeted by the first
ORDER BY
path, then by comparing the values targeted by the secondORDER BY
path, and so on.Each step in an
ORDER BY
path must be simple: it must target a single JSON value: an array step must target a single array element. A step cannot be a descendant step or include a predicate, a wildcard, or an item method. Otherwise, a compile-time error is raised.The simplest example is just
ORDER BY
followed by the single path@
. Being relative to the array, this means order the array elements by comparing each of them with each of the others. In other words,ORDER BY '@'
is just another way to specify the basic element sort.Each
ORDER BY
path is checked, in turn, against each pair of array elements.-
If one element of the pair is matched by the path and the other is not, then the element that is not matched sorts before the element that is matched (after it, with keyword
DESC
). -
Otherwise (neither element is matched by the path or both elements are matched by it):
-
If this is the last path to be checked, then the two elements sort according to their values, using the canonical JSON-type sort order (a deep comparison is done). The element with the lower value sorts before the other (after it, with keyword
DESC
). -
Otherwise, the order of the two elements is not determined by this path alone — check the same pair using the next
ORDER BY
path.
-
Reasons for an
ORDER BY
path not to match for a given element include a value of the wrong type and a missing value. These are examples of not matching an array element E:-
The path targets a string but the targeted value within E is a number (wrong type).
-
The path targets a field of an object, but there is no such object or no such field within E (missing value).
-
The path targets an array element that is out of bounds (missing value).
-
The single-path clause ORDER BY '@.name'
sorts an array
having these elements as follows:
[ "cat", "dog", {"animal":cat"}, {"name":"cow"}, {"name":"horse"} ]
-
Elements
"cat"
,"dog"
, and{"animal":cat"}
don't match the path. They sort before the other elements, and they are sorted by their canonical values. - Elements
{"name":"cow"}
and{"name":"horse"}
match the path. They are sorted after the other elements, and they are sorted by their values of fieldname
.
The two-path clause ORDER BY '@.name', '@.age' DESC
sorts
an array having these elements as follows:
[ "cat", "dog", {"animal":cat"},
{"name":"cow"}, {"name":"cow", "age":2},
{"name":"horse", "age":6, "color":"black"},
{"name":"horse", "age":3} ]
-
Elements
"cat"
,"dog"
, and{"animal":cat"}
don't match either path. They sort before the other elements, and they are sorted by their canonical values. -
Element
{"name":"cow"}
matches only the first path. It sorts before the elements that match both paths. - Elements
{"name":"cow", "age":2}
,{"name":"horse", "age":6, "color":"black"}
, and{"name":"horse", "age":3}
match both paths. They sort after the other elements, and they are sorted by their values of, first, fieldname
(ascending), and then, fieldage
(descending).
Example 13-40 JSON_TRANSFORM: Sorting Elements In an Array By Field Values
This example is a path-directed sort. It sorts the objects in array
LineItems
first by field Part.UnitPrice
and
then by field ItemNumber
, in both cases from higher to lower number
(keyword DESC
). (Ascending order, ASC
, is the
default.)
SELECT json_transform(data,
SORT '$.LineItems'
ORDER BY '$.Part.UnitPrice' DESC,
'$.ItemNumber' DESC
RETURNING VARCHAR2(4000))
FROM j_purchaseorder;
Here is one row of the result. The elements are sorted by descending
UnitPrice
value. Those elements that have the same
UnitPrice
value are sorted by descending
ItemNumber
value.
{"LineItems" :
[ {"ItemNumber" : 1,
"Part" :
{"Description" : "Making the Grade",
"UnitPrice" : 20,
"UPCCode" : 27616867759},
"Quantity" : 8},
{"ItemNumber" : 3,
"Part" :
{"Description" : "Eric Clapton: Best Of 1981-1999",
"UnitPrice" : 19.95,
"UPCCode" : 75993851120},
"Quantity" : 5},
{"ItemNumber" : 2,
"Part" :
{"Description" : "Nixon",
"UnitPrice" : 19.95,
"UPCCode" : 717951002396},
"Quantity" : 5} ]}
These are the handlers allowed for operator SORT
:
-
IGNORE ON MISSING
(default),ERROR ON MISSING
,NULL ON MISSING
. -
ERROR ON MISMATCH
(default),IGNORE ON MISMATCH
,NULL ON MISMATCH
. -
ERROR ON EMPTY
(default),IGNORE ON EMPTY
. -
ERROR ON ERROR
(default),IGNORE ON ERROR
.
Parent topic: Oracle SQL Function JSON_TRANSFORM