13.19 JSON_TRANSFORMの演算子SORT

JSON_TRANSFORMの演算子SORTは、配列の要素をソートします。

これは、SQL/JSONパス式で対象となっている配列の要素をソートしてそれらの順序を変更します。結果には、配列のすべての要素が含まれます(何も削除されません)。可能性のある唯一の変更は、順序が変更されることです。

配列のソートは通常は基本的なソートであり、次の例で示すように、正規のJSON型ソート順を使用してそれらの要素の値をソートします。

例13-39 JSON_TRANSFORM: 配列内の要素をそれらの値でソート

ここでは配列要素は正規のJSON型ソート順を使用して降順でソートされます。 

SELECT json_transform('{"a":[ 1, null, 2, "cat", true, 3.1416 ]}',
                      SORT '$.a' DESC);

結果:

{"a":[ true, "cat", 3.1416, 3, 2, 1, null ]}

このような基本的なソートの他に、パス指示型ソートを使用できます。どちらの方法でも、キーワードREMOVE NULLSを使用して、ソートする値からJSONのnull値を削除できます。

  • 基本的な要素ソート: 正規のJSON型ソート順に従って、要素をそれらの値を基準にしてソートします。

    対象とする配列へのパスの後ろに昇順ソートまたは降順ソートに対応するオプションのキーワードASC (デフォルト)またはDESCを続けるか、または要素順序の逆転を意味するキーワードREVERSEを続けます。

    パスおよびオプションのキーワードASCまたはDESCの後ろに、重複する配列要素の削除を意味するオプションのキーワードUNIQUEを続けます。

  • パス指示型ソート: ORDER BY句で対象となっている複数の値(その配列への相対的なパス1つ以上を指定している)を基準にして、要素をソートします。

    各パスの後ろに、昇順ソートまたは降順ソートに対応するオプションのキーワードASC (デフォルト)またはDESCを続けます。

    まず、最初のORDER BYパスで対象とされた値を比較し、次に2番目のORDER BYパスで対象とされた値を比較し、その後も同様の比較を繰り返して各要素のペアをソートします。

    ORDER BYのパスでの各ステップは単純である必要があります。つまり、それは単一のJSON値を対象としている必要があり、配列ステップは単一の配列要素を対象としている必要があります。ステップは子孫ステップにすることも、述語、ワイルドカードまたは項目メソッドを含めることもできません。それ以外の場合は、コンパイル時にエラーが発生します。

    最も簡単な例では、ORDER BYの後に単一パス@を続けます。これは、配列に対して相対的であるため、それぞれの配列要素を相互に比較して順序付けします。つまり、ORDER BY '@'は、単に基本的な要素のソートを指定するための別の方法ということです。

    それぞれのORDER BYパスは、配列要素の各ペアに対して順次チェックされます。

    • ペアの1つの要素がパスで一致し、もう1つの要素が一致しない場合、一致しない要素は一致する要素の前に(キーワードDESCでは後に)ソートされます。

    • それ以外の場合(どちらの要素もパスと一致しないまたは両方の要素が一致する場合):

      • これがチェックされる最後のパスである場合、2つの要素はそれらの値に応じてJSON型の正規のソート順序を使用してソートされます(深い比較が実施されます)。低い値の要素は、もう一方の前に(キーワードDESCでは後に)ソートされます。

      • それ以外の場合、このパスのみでは2つの要素の順序は決定されず、その次のORDER BYパスを使用して同じペアをチェックします。

    指定された要素に対してORDER BYのパスが一致しない理由としては、型が正しくないことや値が欠落していることが挙げられます。次に、配列要素Eと一致しない例を示します。

    • パスは文字列を対象としていますが、E内の対象とされる値は数値です(不正な型)。

    • パスはオブジェクトのフィールドを対象としていますが、E内には、該当するオブジェクトまたは該当するフィールドが存在していません(値の欠落)。

    • パスは範囲外の配列要素を対象としています(値の欠落)。

単一パス句のORDER BY '@.name'は、次の要素を保持している配列を次のようにソートします。 

[ "cat", "dog", {"animal":cat"}, {"name":"cow"}, {"name":"horse"} ]

  • 要素"cat"、"dog"および{"animal":cat"}はパスと一致しません。これらは、その他の要素の前にソートされ、正規の値でソートされます。

  • 要素{"name":"cow"}および{"name":"horse"}はパスと一致します。これらは、その他の要素の後にソートされ、フィールドnameの値でソートされます。

2パス句のORDER BY '@.name', '@.age' DESCは、次の要素を保持している配列を次のようにソートします。 

[ "cat", "dog", {"animal":cat"},
  {"name":"cow"}, {"name":"cow", "age":2},
  {"name":"horse", "age":6, "color":"black"},
  {"name":"horse", "age":3} ]

  • 要素"cat"、"dog"および{"animal":cat"}は、どちらのパスとも一致しません。これらは、その他の要素の前にソートされ、正規の値でソートされます。

  • 要素{"name":"cow"}は、最初のパスにのみ一致します。これは、両方のパスと一致する要素の前にソートされます。

  • 要素{"name":"cow", "age":2}、{"name":"horse", "age":6, "color":"black"}および{"name":"horse", "age":3}は、両方のパスと一致します。これは、その他の要素の後にソートされ、最初にフィールドnameの値(昇順)、次にフィールドageの値(降順)でソートされます。

例13-40 JSON_TRANSFORM: 配列内の要素をフィールド値でソート

この例は、パス指示型ソートです。これは、配列LineItems内のオブジェクトを、最初にフィールドPart.UnitPriceでソートし、次にフィールドItemNumberでソートします。どちらの場合も、数値の大きい順(キーワードDESC)です。(昇順のASCがデフォルトです)。 

SELECT json_transform(data,
                      SORT '$.LineItems'
                        ORDER BY '$.Part.UnitPrice' DESC,
                                 '$.ItemNumber' DESC
                      RETURNING VARCHAR2(4000))
  FROM j_purchaseorder;

次に、この結果の1行を示します。要素は、降順のUnitPrice値でソートされています。UnitPrice値が同じ要素は、降順のItemNumber値でソートされます。 

{"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} ]}

演算子SORTで使用できるハンドラは次のとおりです:

  • IGNORE ON MISSING (デフォルト)、ERROR ON MISSING、NULL ON MISSING。

  • ERROR ON MISMATCH (デフォルト)、IGNORE ON MISMATCH、NULL ON MISMATCH。

  • ERROR ON EMPTY (デフォルト)、IGNORE ON EMPTY。

  • ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

親トピック: Oracle SQLファンクションJSON_TRANSFORM