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) or DESC, for ascending or descending sort order, respectively, or by keyword REVERSE, which means reverse the order of the elements.

    The path and optional keyword ASC or DESC is optionally followed by keyword UNIQUE, 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) or DESC, 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 second ORDER 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 field name.

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, field name (ascending), and then, field age (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.