1. JSON開発者ガイド
  2. JSONデータの問合せ
  3. SQL/JSONパス式

16 SQL/JSONパス式

Oracle Databaseでは、SQL/JSONパス式を使用したJSONデータへのSQLアクセスが提供されます。

  • SQL/JSONパス式の概要
    Oracle Databaseでは、SQL/JSONパス式を使用したJSONデータへのSQLアクセスが提供されます。
  • SQL/JSONパス式の構文
    SQL/JSONパス式は、SQL/JSONファンクションまたは条件によってJSONデータと照合され、その一部が選択またはテストされます。パス式にはワイルドカードおよび配列範囲を使用できます。照合では大文字/小文字が区別されます。
  • SQL/JSONパス式の項目メソッド
    SQL/JSONパス式に使用可能なOracle項目メソッドについて説明します。ターゲットJSONデータに対する動作については、一般的な用語と各項目メソッドで説明します。
  • 比較における型
    SQL/JSONパス式のフィルタ条件における比較では、コンパイル時に静的に型が指定されます。比較のオペランドの有効な型が同じであると認識されない場合、型キャストでの調整が行われることがあります。

親トピック: JSONデータの問合せ

16.1 SQL/JSONパス式の概要

Oracle Databaseでは、SQL/JSONパス式を使用したJSONデータへのSQLアクセスが提供されます。

SQL/JSONパス式は、XMLデータのXQueryまたはXPath式とある程度似ています。これらは、SQL/XMLがXQuery式を使用してXMLデータへのSQLアクセスを許可するのと同様に、JSONデータへのSQLアクセスを提供します。

SQL/JSONパス式の構文は単純です。パス式では、この式と一致するかこの式を満たす0(ゼロ)個以上のJSON値が選択されます。

SQL/JSON条件json_existsの場合、少なくとも1つの値が一致する場合はtrueが返され、一致する値がない場合はfalseが返されます。単一の値が一致した場合、この値がスカラーである場合はSQL/JSONファンクションjson_valueによってこの値が返され、スカラーでない場合はエラーが発生します。パス式に一致する値がない場合は、json_valueによってSQL NULLが戻されます。

SQL/JSONファンクションjson_queryは、一致する値のすべてを返します。つまり、この関数は複数の値を返すことができます。この動作は、XQueryの場合のように値のシーケンスを戻す動作とみなすことも、複数の値を戻す動作とみなすこともできます。(ユーザーから見えるシーケンスが現れることはありません。)

どのような場合でも、パス式の照合では、パス式の各ステップを順番に照合しようとします。いずれかのステップの照合が失敗すると、後続のステップの照合は行われず、パス式の照合は失敗します。各ステップの照合が成功すると、パス式の照合は成功します。

SQL/JSONパス式のテキストの最大長は32Kバイトです。ただし、パス式の有効な長さは基本的に無制限です。これは、式が文字列値にバインドされたSQL/JSON変数(それぞれ32Kバイトに制限される)を使用できるためです。

関連項目

関連項目:

概要については、TomのSQL-JSONパス式ビデオをご覧ください

親トピック: SQL/JSONパス式

16.2 SQL/JSONパス式の構文

SQL/JSONパス式は、SQL/JSONファンクションまたは条件によってJSONデータと照合され、その一部が選択またはテストされます。パス式にはワイルドカードおよび配列範囲を使用できます。照合では大文字/小文字が区別されます。

SQL/JSONパス式およびJSONデータをSQL/JSONファンクションまたは条件に渡します。パス式はデータに対して照合され、一致するデータが特定のSQL/JSONファンクションまたは条件によって処理されます。この照合プロセスは、パス式が一致データを関数または条件に戻すという観点で検討することができます。

  • 基本的なSQL/JSONパス式の構文
    SQL/JSONパス式の基本的な構文を示します。これは、コンテキスト項目記号($)とその後のゼロ個以上のオブジェクト、配列および子孫ステップで構成されます。それぞれの後にフィルタ式を続けることができ、オプションでファンクション・ステップを指定できます。例を示します。
  • SQL/JSONパス式の構文の緩和
    暗黙的な配列のラップおよびアンラップを可能にするため、SQL/JSONパス式の基本的な構文は緩和されています。これは、データが発展して特定のJSON値のかわりにこのような値の配列が使用される(またはこの逆)場合、コード内のパス式を変更する必要がないことを意味します。例を示します。
  • パス式での否定
    パス式での否定は、パスで一致したデータが複数である場合、混乱を招く可能性があります。いくつかの簡単な例を説明します。

関連項目

親トピック: SQL/JSONパス式

16.2.1 基本的なSQL/JSONパス式の構文

SQL/JSONパス式の基本的な構文を示します。これは、コンテキスト項目記号($)とその後のゼロ個以上のオブジェクト、配列および子孫ステップで構成されます。それぞれの後にフィルタ式を続けることができ、オプションでファンクション・ステップを指定できます。例を示します。

ただし、この基本的な構文は、非配列パターンに対する配列の照合および配列パターンに対する非配列の照合の緩和によって拡張されます。SQL/JSONパス式の構文の緩和を参照してください。

SQL/JSONパス式に対するデータの照合では、大/小文字が区別されます。

  • SQL/JSONの基本的なパス式(ここでは単にパス式とも呼びます)は、絶対パス式または相対パス式です。

  • 絶対パス式は、パス式のコンテキスト項目(つまり、照合するJSONデータ)を表すドル記号($)から始まります。一致するデータは、SQL/JSONファンクションに引数として渡されるSQL式を評価することによって見つかります。ドル記号の後にゼロ個以上の非ファンクション・ステップが続き、その後にオプションのファンクション・ステップが続きます。

  • 相対パス式は、アット記号(@)の後にゼロ個以上の非ファンクション・ステップが続き、その後にオプションのファンクション・ステップが続く形式です。ドル記号($)のかわりにアット記号を使用する点を除き、絶対パス式と同じ構文になります。

    相対パス式はフィルタ式(フィルタと短縮される)内で使用されます。アット・マークは、パス式のカレント・フィルタ項目を表します。つまり、相対パス式を含むフィルタの前にある(周囲の)パス式の部分に一致するJSONデータを表します。相対パス式は、絶対パス式がコンテキスト項目に対して照合されるのと同じように、カレント・フィルタ項目に対して照合されます。

  • 非ファンクション・ステップは、オブジェクト・ステップ、配列ステップまたは子孫ステップの後にオプションのフィルタ式が続く形式です。

  • 単一のファンクション・ステップは、基本パス式(絶対または相対)ではオプションです。このステップがあったら、これがパス式の最後のステップになります。これは、ドットと呼ばれることもあるピリオド(.)の後ろにSQL/JSON項目メソッドが続き、その後に左カッコ(()が続き、おそらく引数リストがあり、その後ろに右カッコ())が続く形式です。これらのカッコの中にホワイトスペースを入れることができます(このようなホワイトスペースは重要ではありません)。

    項目メソッドは、(1)ファンクション・ステップの前にある同じパス式の残りの部分によって対象とされるデータと、(2)カッコ内の引数(ある場合)に適用されます。項目メソッドは、対象データを変換するために使用されます。引数としてパス式が渡されるSQLファンクションまたは条件は、変換したデータを対象データのかわりに使用します。

    一部の項目メソッドのみに、カッコ間の引数リストを使用できます。引数はスカラーJSON値であり、カンマ(,)で区切ります。項目メソッドには、このような引数を1つ以上必要とするものがあります。その他のメソッドでは、このような引数が許容されますが、必須ではありません。

  • オブジェクト・ステップは、ピリオド(.)の後にオブジェクト・フィールド名またはすべてのフィールド(の値)を表すアスタリスク (*)ワイルドカードが続く形式です。フィールド名は空にすることができます。この場合は、""(ホワイトスペースなし)と記述する必要があります。空でないフィールド名は、大文字または小文字の文字A-Zで始まり、このような文字または10進数字(0-9)のみを含む必要があり、それ以外の場合は、二重引用符(")で囲む必要があります。

    オブジェクト・ステップは、指定されたフィールドの値を戻します。フィールドにワイルドカードが使用されている場合、すべてのフィールドの値が特に順序は指定されずに戻されます。

  • 配列ステップは、左大カッコ([)の後ろに、すべての配列要素を表すアスタリスク(*)ワイルドカードまたはカンマ(,)で区切られた1つ以上の特定の配列索引または範囲指定のどちらかが続き、その後ろに右大カッコ(])が付いた形式です。

    アスタリスクと配列索引または範囲指定の両方を使用すると、エラーが発生します。索引または範囲の指定がない場合、エラーが発生します。[]は有効な配列ステップではありません。

    配列索引は、単一の配列の位置(整数(0、1、2...))を指定します。このため、配列索引はリテラルの整数(0、1、2、…)であり、配列位置および索引付けは配列に関するJavaScript規則にあるようにゼロベースです。先頭の配列要素は、索引0 (位置0を指定)です。

    任意のサイズの空ではない配列の末尾の要素は、索引lastを使用して参照できます。

    配列索引には、last - Nという形式もあります。ここで、-はマイナス記号(ハイフン)で、Nは配列サイズ-1を超えないリテラルの整数(0、1、2、…)です。

    末尾の1つ前の配列要素は、索引last-1を使用して参照できます。末尾の2つ前の配列要素は索引last-2で、以下同様になります。マイナス記号(ハイフン)の前後の空白は無視されます。

    たとえば、配列["a", "b", 42]の場合、索引1 (位置1)の要素は文字列"b" (2番目の配列要素)です。索引2または索引lastの要素は、数値42です。索引0またはlast-2の要素は、"a"です。

    Oracle SQLファンクションjson_transformでは、last + Nという形式の索引も使用できます。ここで、Nは整数です。これにより、現在の配列サイズ-1を超える位置を指定することによって、既存の配列に新しい要素を追加できます。プラス記号の前後の空白は無視されます。この形式の索引は、範囲指定(次を参照)などの他の索引と組み合せて使用できません。その場合は、エラーが発生します。

    範囲指定の形式は、N to Mです。ここで、NとMは配列索引であり、toの前後には1つ以上の空白文字を記述します。脚注1

    範囲指定のNからMと、MからNは同等です。それぞれ、N、MおよびNとMの間の索引をすべて昇順で明示的に指定することと同等です。

    つまり、NとMの順序は重要ではありません。3番目から6番目の要素の範囲は、2 to 5または5 to 2として記述できます。6個の要素を持つ配列の場合は、同じ範囲を2 to lastまたはlast to 2として記述できます。範囲指定N to N (toの両側に同じ索引N)は、単一の索引Nと同じです([N, N]と同じではありません)。

    配列ステップに配列索引および配列範囲を指定する順序は重要であり、パス式を使用するファンクションで生成される配列に反映されます。

    同じ配列ステップ内の複数の範囲指定は、個別に処理されます。特に、重複する範囲では、重複している要素が繰り返されます。

    たとえば、配列ラッパー(複数の問合せ結果をラップして単一のJSON配列を返します)を指定したSQL/JSONファンクションjson_queryに、配列ステップ[3 to 1, 2 to 4, last-1 to last-2, 0, 0]を指定したパス式を渡して、問合せを行うとします。この問合せによって返されるデータには、問合せ対象のデータの配列の次の要素から生成された配列が含まれています。

    • 2番目から4番目の要素(範囲3 to 1)

    • 3番目から5番目の要素(範囲2 to 4)

    • 末尾の2つ前から末尾の1つ前までの要素(範囲last-1 to last-2)

    • 先頭の要素(索引0)

    • 再び先頭の要素(索引0)

    データの配列["1", "2", "3", "4", "5", "6", "7", "8", "9"]と照合すると、問合せ結果の配列は["2", "3", "4", "3", "4", "5", "7", "8", "1", "1"]になります。

    データの配列(0から配列サイズ-1まで)の範囲外の位置を指定する配列索引を使用しても、エラーは発生しません。指定されたパス式がデータと一致しないだけです。配列にそのような位置はありません。(配列ステップのみではなく、通常、SQL/JSONパス式の照合はこのルールに従っています。)

    これは、たとえば、要素が7個未満の配列に対して索引last-6を照合しようとした場合などです。6個の要素を持つ配列の場合、lastは5であるため、last-6は(0より小さい)無効な位置を指定することになります。

    また、任意の配列ステップを空の配列に対して照合しようとする場合にも当てはまります。たとえば、配列ステップ[0]と[last]はどちらもデータ配列[]と一致しません。[]には先頭の要素がないため、ステップ[0]は一致しません。また、[]には索引-1 (配列の長さ-1)の要素がないため、ステップ[last]は一致しません。

    これは、ファンクションjson_transform以外で、索引last+N (Nはゼロ以外)を使用した場合にも当てはまります。json_transformの場合、これは、既存の配列要素と照合するためではなく、既存の配列の変更時に新しい要素を挿入する位置を指定するために使用します。

    範囲指定は明示的な昇順の配列索引と等価であるため、範囲外の暗黙的な索引はデータと一致しません。明示的な索引と同様に、それらの索引は無視されます。

    これについて考えるもう1つの方法は、範囲指定は実際には所与のデータ配列の最も近い境界(0またはlast)で切り捨てられるということです。たとえば、配列["a", "b", "c"]と照合する場合、範囲指定last-3 to 1、2 to last+1およびlast-3 to last+1は、実際にはそれぞれ0 to 1、2 to 2および0 to 2に切り捨てられます。これらの範囲の(暗黙的な)範囲外の索引last-3 (ここでは-1)およびlast+1 (ここでは3)は無視されます。

  • 子孫ステップでは、2つの連続したピリオド(..)(ドット・ドットとも呼ばれる)の後にフィールド名を指定します(オブジェクト・ステップの場合と同じ構文)。

    これは、直前のステップ(前のステップがない場合はコンテキスト項目)と一致するオブジェクトまたは配列で、再帰的に子孫を参照します。

    各子孫レベルでは、各オブジェクトおよびオブジェクトである各配列要素について、指定された名前を持つすべてのフィールドの値が収集されます。収集されたフィールド値をすべて返します。

    たとえば、次の問合せとデータについて考えます。

    json_query(some_json_column, '$.a..z' WITH ARRAY WRAPPER)
    { "a" : { "b" : { "z" : 1 },
          "c" : [ 5, { "z" : 2 } ],
          "z" : 3 },
  "z" : 4 }

    この問合せでは[1,2,3]のような配列が返され、その要素は1、2および3になります。ドット・ドット(..)の直前のステップ、つまりフィールドa内の各フィールドzの値が収集されます。値4の最上位のフィールドzは、フィールドaの値内にないため一致しません。

    フィールドaの値は、子孫が参照されるオブジェクトです。

    • zというフィールドがあり、その値(3)が収集されます。また、値がオブジェクトであるフィールドbもあります。これは、フィールドzの値(1)を収集するために子孫が参照されます。

    • 値が配列であるフィールドcもあり、この配列には、値(2)が収集されるフィールドzを持つオブジェクトである要素があります。

    したがって、収集されるJSON値は3、1および2です。これらの値は未定義の順序で配列にラップされます。可能性のある戻り値の1つは、[1,2,3]です。

  • フィルタ式(フィルタと短縮される)は、疑問符(?)の後カッコ(())で囲まれたフィルタ条件を記述します。条件が満たされるとフィルタが満たされ、trueが戻されます。

  • フィルタ条件は、その引数に述語(ブール関数)を適用します。これは、cond、cond1およびcond2のそれぞれがフィルタ条件を表す、次のいずれかとして再帰的に定義されます。脚注2

    • ! cond: condの否定で、condが満たされてはならないことを意味します。!は接頭辞単項述語です。(パス式での否定を参照してください。)

    • ( cond ): カッコを使用してグループ化することで、前後にあるその他のフィルタ条件から、フィルタ条件condを1つのユニットとして区別します。

      コードが読みやすくなるのであれば、影響のない箇所でかっこを使用することもできます。たとえば、必要に応じてexistscondのみでなく、exists(cond)のように、述語の引数の周囲にそれらを配置できます。

      条件引数の先頭と末尾が不明瞭な場合は必ず括弧が必要です。たとえば、condがcomparison条件である場合は常に、!(cond)に必要です(次を参照)。たとえば、!@.x > 5ではなく!(@.x > 5)を使用する必要があります。(ただし、!exists@.xまたは!(exists@.x)のいずれでも使用できます。)

    • cond1 && cond2: cond1とcond2の論理積(and)で、両方を満たす必要があります。&&は中置二項述語です。

    • cond1 || cond2: cond1およびcond2の包含的論理和(or)で、cond1、cond2またはその両方を満たす必要があります。||は中置二項述語です。

    • exists、相対パス式: ターゲット・データが存在する条件。existsは、接頭辞の単項述語です。

    • 相対パス式の後にinが続き、その後に値リストが続きます。これは、値が値リスト内の値の1つであることを意味します。inは中置二項述語です。

      複数の値リストの要素があるinフィルタ条件は、値リストの要素に対する等価(==)比較の論理和(||)に相当します。脚注3たとえば、次のものは同等です。 

      @.z in ("a", "b", c")
      (@.z == "a") || (@.z == "b") || (@.z == "c")

      値リストは、(の後ろに、0個以上のJSONリテラル値またはカンマ(,)で区切られたSQL/JSON変数のリストの後に)が続いたものです。脚注4値リストは、inの後にのみ指定できます。それ以外の場合は、エラーが発生します。

      • リスト内の各変数がJSONデータ型の場合、「JSONデータ型の値の比較とソート」に説明がある正規のソート順を使用して、リストされた各値(リテラルまたは変数の値)が、ターゲットJSONデータに対して等価比較されます。条件inは、リストされた値のいずれかがターゲット・データと等しい場合に満たされます。

      • それ以外の(少なくとも1つの変数がJSONデータ型ではない)場合、リスト内のすべての値は(リテラルか変数かに関係なく)、同じJSON言語型のスカラー値であることが必要です。たとえば、これらはすべて文字列であることが必要で、そうでない場合はエラーが発生します。

        JSONのnull値は、この同じタイプの制限に対する例外です。nullは、常に値リストで使用できます。ターゲット・データのnull値(のみ)によって照合されます。

    • 比較(次のいずれか)

      • JSONスカラー値、その後に比較述語、その後に別のJSONスカラー値。

      • 相対パス式の後ろに比較述語が続き、その後に別の相対パス式が続きます。

      • JSONスカラー値またはSQL/JSON変数、その後に比較述語、その後に相対パス式。

      • 相対パス式、その後に比較述語、その後にJSONスカラー値またはSQL/JSON変数。

      • 相対パス式の後にキーワードのhas substring、starts with、like、like_regex、regex like、regex、eq_regex、ci_like_regexまたはci_regexが続き、その後にJSON文字列またはSQL文字列(データベース文字セットからUTF8に自動的に変換される)にバインドされているSQL/JSON変数が続きます。

        これらのすべての述語について、空の文字列("")のパターンは空の文字列のデータに一致します。また、like_regex以外のすべてで、空でない文字列であるパターンは空の文字列のデータと一致しません。like_regexの場合、空でないパターンは空の文字列データと一致します。

        • has substringは、一致するデータ値に部分文字列として指定された文字列が含まれていることを意味します。

        • starts withは、一致するデータ値に接頭辞として指定された文字列が含まれていることを意味します。

        • likeは、JSON文字列データ値が、SQL LIKE4文字セット・セマンティクスを使用するSQL LIKEパターンとして解釈される指定された文字列と一致することを意味します。パターン内のパーセント記号(%)は、ゼロ個以上の文字に一致します。アンダースコア(_)は1文字と一致します。

          ノート:

          SQLのLIKEの場合とは異なり、パス式の述語likeのエスケープ文字は選択できません。常に文字「`」(グレイヴ・アクセント(U +0060)、バッククォートやバックティックとも呼ばれます)を使用します。

          21cより前のデータベース・リリースでは、パス式の述語likeにエスケープ文字はありません。そのようなリリースでは、likeパターンで文字「`」(グレイヴ・アクセント(U+0060))を使用しないようにすることをお薦めします。

        • like_regexまたはそのシノニムのregex like (下線なし)は、JSON文字列データ値が指定の文字列と一致することを意味します。この文字列は、SQL LIKE4文字セット・セマンティクスを使用するSQL REGEXP LIKE正規表現パターンとして解釈されます。

          ci_like_regexは、like_regexと同じですが、照合では大文字と小文字が区別されません。

          like_regexとci_like_regexは、パターン一致比較の例外であり、そのパターンは空のJSON文字列("")と一致します。

        • eq_regexとそのシノニムregex equals (下線なし)およびregexは、次の2つの違いを除いてlike_regexと同じです。

          • eq_regexは、JSON文字列データ値全体に対する正規表現パターンと一致します。完全な文字列は、比較対象のパターンと一致する必要があります。like_regexは、JSON文字列の一部がパターンと一致する場合に満たされます。

          • eq_regexパターンが空のJSON文字列("")と一致しません。

          ci_regexは、eq_regexと同じですが、照合では大文字と小文字が区別されません。

    SQL/JSON変数は、ドル記号($)の後に、PASSING句でバインドされた変数の名前が続きます。(SQL/JSON変数名の必要な構文は、「SQLファンクションおよび条件のPASSING句」を参照。)

    比較述語は==、<>、!=<a id="fn_5" name="fn_5" href="#fn_5" onclick='footdisplay(5, "!= is an Oracle alias for the SQL/JSON standard comparison predicate <>.")'>脚注5、<、<=、>=または>です(それぞれ等しい、等しくない、未満、以下、以上、より大きい)。(等しくない述語、<>またはそのOracle別名!=の使用の詳細は、パス式での否定を参照してください。)

    したがってフィルタ条件で使用できる述語は、&&、||、!、exists、==、<>、!=、<、<=、>=、>、in、has substring、starts with、like、like_regex、regex like、regex、eq_regex、ci_like_regexおよびci_regexです。

    例を示します。次の基準の両方が満たされると、フィルタ条件(a || b) && (!(c) || d < 42)が満たされることになります。

    • aとbのフィルタ条件のうち、少なくともいずれか1つが満たされる: (a || b)

    • フィルタ条件cが満たされない、は数値dが42以下になる、またはその両方が当てはまる: (!(c) || d < 42)

    条件述語!、&&、||は、この順番で優先度が高くなります。カッコは、常にグループ化を制御するために使用できます。

    前述の例の(a || b) && (!(c) || d < 42)で、グループ化にカッコを使用することなく、単にa || b && !(c) || d < 42としていたとすると、少なくとも次の基準の1つが満たされている場合に、これが満たされるようになります。

    • 条件b && !(c)が満たされる。つまり、条件bと!(c)がそれぞれ満たされることを意味する(つまり、条件cは満たされない)。

    • 条件aが満たされる。

    • 条件d < 42が満たされる。

比較の少なくとも一方をSQL/JSON変数にすることはできません。

比較の対象となるデータがJSONデータ型であり、比較に使用されるすべてのSQL/JSON変数もJSON型の場合、比較では「JSONデータ型の値の比較およびソート」で説明した正規のソート順が使用されます。

それ以外の場合、比較のデフォルトの型は、コンパイル時に非変数側の型に基づいて定義されます。型指定項目メソッドを使用すると、このデフォルトを別の型でオーバーライドできます。一致するデータの型は、比較のために自動的に変換され、決定された型(デフォルトまたは項目メソッドで指定)に適合します。たとえば、$.a > 5では5が数値であるため、数値の比較が課され、$.a > "5"では"5"が文字列であるため、文字列の比較が課されます。

ヒント:

頻繁に使用する問合せの場合には、PASSING句を使用してSQLバインド変数を定義します。これらは、パス式でSQL/JSON変数として使用されます。これにより、(変数)値の変更時に問合せの再コンパイルを回避することで、パフォーマンスが向上する可能性があります。

たとえば、この問合せは、バインド変数v1の値をSQL/JSON変数$v1として渡します。 

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,                    
                    '$.LineItems.Part?(@.UPCCode == $v1)'
                    PASSING '85391628927' AS "v1");

ノート:

Oracle SQLファンクションjson_textcontainsを使用すると、JSONデータの強力な全文検索を実行できます。単純な文字列のパターン一致のみが必要な場合は、かわりにパス式フィルタ条件をhas substring、starts with、like、like_regexまたはeq_regexのいずれかのパターン一致比較とともに使用できます。

基本的なパス式の例

パス式の例を次に示しますが、その意味も詳述しています。

  • $ - コンテキスト項目。

  • $.friends - コンテキスト項目オブジェクトのフィールドfriendsの値。ドル記号($)の直後のドット(.)は、コンテキスト項目がJSONオブジェクトであることを示します。

  • $.friends[0] - コンテキスト項目オブジェクトのフィールドfriendsの値である配列の最初の要素であるオブジェクト。大カッコ表記法は、フィールドfriendsの値が配列であることを示します。

  • $.friends[0].name - コンテキスト項目オブジェクトのフィールドfriendsの値である配列の最初の要素であるオブジェクトのフィールドnameの値。2番目のドット(.)は、配列friendsの最初の要素が(フィールドnameを持つ)オブジェクトであることを示します。

  • $.friends[*].name - コンテキスト項目オブジェクトのフィールドfriendsの値である配列の各オブジェクトのフィールドnameの値。

  • $.*[*].name - コンテキスト項目オブジェクトのフィールドの配列値に含まれる各オブジェクトのフィールドnameの値。

  • $.friends[3, 8 to 10, 12] — 配列friends(コンテキスト項目オブジェクトのフィールド)の4番目、9番目から11番目、13番目の要素。要素は指定された順序で返されます(4番目、9番目、10番目、11番目、13番目)。

    照合する配列の要素が13個未満の場合、索引12は一致しません。照合する配列の要素が10個のみの場合、索引12と一致しないのみではなく、範囲8 to 10は実際には位置8および9 (要素9および10)に切り捨てられます。

  • $.friends[12, 3, 10 to 8, 12] - 配列friendsの13番目、4番目、9番目から11番目、13番目の要素(このとおりの順序)。要素は指定された順序で返されます。範囲10 to 8は、範囲8 to 10と同じ要素を同じ順序で指定します。13番目の要素(位置12)は2回返されます。

  • $.friends[last-1, last, last, last] - 配列friendsの末尾の1つ前、末尾、末尾および末尾の要素(このとおりの順序)。末尾の要素は3回返されます。

  • $.friends[last to last-1, last, last] - 前述の例と同じです。範囲last to last-1は、範囲last-1 to lastと同じであり、末尾の1つ前から末尾の要素を返します。

  • $.friends[3].cars - 配列friendsの4番目の要素であるオブジェクトのフィールドcarsの値。ドット(.)は、4番目の要素が(フィールドcarsを持つ)オブジェクトであることを示します。

  • $.friends[3].* - 配列friendsの4番目の要素であるオブジェクトのすべてのフィールドの値。

  • $.friends[3].cars[0].year - 配列friendsの4番目の要素であるオブジェクトのフィールドcarsの値である配列の最初の要素であるオブジェクトのフィールドyearの値。

  • $.friends[3].cars[0]?(@.year > 2016) - 配列cars (配列friendsの4番目の要素であるオブジェクトのフィールド)の最初のオブジェクト。ただし、そのフィールドyearの値が2016よりも大きいか、2016よりも大きい数値に変換できるもの。"2017"などのyear値は、テストを満たす数値2017に変換されます。"recent"などのyear値は、一致せず、テストに失敗します。

  • $.friends[3].cars[0]?(@.year.number() > 2016) - 前と同じです。項目メソッドnumber()は数値に変換できる数値または文字列値のみを許可し、その動作は数値比較述語>によってすでに指定されています。

  • $.friends[3].cars[0]?(@.year.numberOnly() > 2016) - 前と同じですが、year値は数値である場合にかぎられます。項目メソッドnumberOnly()は、year値が文字列数字("2017"など)である車を除外します。

  • $.friends[3]?(@.addresses.city == "San Francisco") — 配列friendsの4番目の要素であるオブジェクト。ただし、そのaddressesフィールドの値が、フィールドcityの値が文字列"San Francisco"のオブジェクトであるもの。

  • $.friends[*].addresses?(@.city starts with "San ").zip - 住所cityの名前が"San "で始まる、friendsのすべてのaddressesの郵便番号。(この場合、フィルタは最後のパス・ステップではありません。)

  • $.friends[*].addresses?(@.city has substring "Fran").zip - 住所cityの名前に"Fran"が含まれる、friendsのすべてのaddressesの郵便番号。

  • $.friends[*].addresses?(@.city like "S_n%").zip - 住所cityの名前が"S"で、その後に任意の1文字、"n"、さらにゼロ個以上の任意の文字のシーケンスが続く、friendsのすべてのaddressesの郵便番号。アンダースコア(_)は1文字に一致し、パーセント(%)は複数の文字に一致します。

  • $.friends[*].addresses?(@.city like_regex "n +F").zip - 住所cityの名前に"n"とそれに続く少なくとも1つの空白文字が含まれる、friendsのすべてのaddressesの郵便番号。照合では大文字と小文字が区別され、city文字列の先頭に固定されることはありません。

  • $.friends[*].addresses?(@.city ci_regex "s.+o").zip - 住所cityの名前が"s"または"S"で始まり、"o"または"O"で終わる、friendsのすべてのaddressesの郵便番号。照合では大文字と小文字が区別されず(ci_)、city文字列全体が一致する必要があります(like_なし)。

  • $..zip - 任意のレベルのzipフィールドのすべての値。

  • $.friends[3]?(@.addresses.city == "San Francisco" && @.addresses.state == "Nevada") — 配列friendsの4番目の要素であるオブジェクト。ただし、cityが"San Francisco"の住所に対する一致があり、stateが"Nevada"の住所に対する一致もあるもの。

    ノート: 論理積になったフィルタ条件は、必ずしも同じオブジェクトに適用する必要はありません。このフィルタでは、cityがSan Franciscoのオブジェクトがあるかどうかと、stateがNevadaのオブジェクトがあるかどうかがテストされます。cityがSan FranciscoでstateがNevadaの両方を備えたオブジェクトがあるかどうかはテストされません。JSON_EXISTSでのフィルタの使用を参照してください。

  • $.friends[3].addresses?(@.city == "San Francisco" && @.state == "Nevada") — 配列friendsの4番目の要素であるオブジェクト。ただし、そのオブジェクトに、cityの"San Francisco"の一致と、stateの"Nevada"の一致があるもの。

    前述の例とは異なり、この場合の論理積のフィルタ条件(フィールドcityとstate)は、同じaddressesオブジェクトに適用されます。フィルタは、その外側にある特定のaddressesオブジェクトに適用されます。

  • $.friends[3].addresses?(@.city == $City && @.state == $State) - 前と同じですが、比較に使用される値はSQL/JSONの変数、$Cityおよび$Stateです。この変数値は、PASSING句(PASSING ... AS "City", ... AS "State")のSQLバインド変数CityおよびStateによって指定されます。比較に変数を使用すると、問合せの再コンパイルが回避されることでパフォーマンスが向上します。

関連項目

関連項目:

親トピック: SQL/JSONパス式の構文

16.2.2 SQL/JSONパス式の構文の緩和

暗黙的な配列のラップおよびアンラップを可能にするため、SQL/JSONパス式の基本的な構文は緩和されています。これは、データが発展して特定のJSON値のかわりにこのような値の配列が使用される(またはこの逆)場合、コード内のパス式を変更する必要がないことを意味します。例を示します。

SQL/JSONパス式の基本的な構文では、SQL/JSONパス式の基本的な構文を定義しています。サポートされている実際のパス式の構文では、この定義を次のように緩和します。

  • パス式のステップが配列を対象としている(予測している)が、実際のデータは配列を示していない場合、データは暗黙的に配列にラップされます。

  • パス式のステップが非配列を対象としている(予測している)が、実際のデータは配列を示している場合、配列は暗黙的にアンラップされます。

この緩和により、次の省略が可能になります。すなわち、[*]は、オブジェクト・アクセッサ.の前にあり、オブジェクト・フィールド名が後ろに続く場合は常に省略でき、本質的に何の変更も行われません。この逆もまた真です。[*]は、オブジェクト・アクセッサ.の前に常に挿入でき、本質的に何の変更も行われません。

つまり、特定のオブジェクトの配列の各要素のフィールドpropの値を表すオブジェクト・ステップ[*].propは.propとして省略でき、単一オブジェクトのprop値を表すかのように見えるオブジェクト・ステップ.propは、オブジェクト・アクセッサが適用された配列の各要素のprop値も表します。

これは重要な機能です。なぜなら、データが発展して特定のJSON値のかわりにこのような値の配列が使用される(またはこの逆)場合、コード内のパス式を変更する必要がないからです。

たとえば、フィールドtypeおよびnumberを伴う単一オブジェクトが値であるフィールドPhoneを持つオブジェクトがデータにもともと含まれるときに、このデータが発展して電話の配列を表す場合、単一の電話番号と一致するパス式$.Phone.numberを引き続き使用できます。パス式$.Phone.numberは、番号を選択して単一の電話オブジェクトと一致することも、各番号を選択して電話オブジェクトの配列と一致することもできます。

同様に、データに両方の種類の表現が混在する(単一の電話オブジェクトを使用するデータ・エントリ、および電話オブジェクトの配列を使用するエントリ、または両方を使用するエントリさえも存在する)場合、同じパス式を使用して、これらの異なる種類のエントリの電話情報にアクセスできます。

次に、SQL/JSONパス式の基本的な構文の項のパス式の例を、相当する式の説明とともに示します。

  • $.friends – 次のいずれかのフィールドfriendsの値。

    • (単一の)コンテキスト項目オブジェクト。

    • ($[*].friendsに相当)コンテキスト項目配列内の各オブジェクト。

  • $.friends[0].name - 次のオブジェクトのいずれかのフィールドnameの値。

    • コンテキスト項目オブジェクトのフィールドfriendsの値である配列の最初の要素。

    • ($.friends.nameに相当)コンテキスト項目オブジェクトのフィールドfriendsの値。

    • ($[*].friends.nameに相当)コンテキスト項目配列の各オブジェクトのフィールドfriendsの値。

    • ($[*].friends[0].nameに相当)コンテキスト項目配列の各オブジェクトのフィールドfriendsの値である各配列の最初の要素。

    コンテキスト項目はオブジェクトでもオブジェクトの配列でもかまいません。後者の場合、配列内の各オブジェクトはフィールドfriendsについて照合されます。

    フィールドfriendsの値はオブジェクトでもオブジェクトの配列でもかまいません。後者の場合、配列内の最初のオブジェクトが使用されます。

  • $.*[*].name – 次のオブジェクトのいずれかのフィールドnameの値。

    • コンテキスト項目オブジェクトのフィールドの配列値の要素。

    • ($.*.nameに相当)コンテキスト項目オブジェクトのフィールドの値。

    • ($[*].*.nameに相当)コンテキスト項目配列のオブジェクトのフィールドの値。

    • ($[*].*[*].nameに相当)コンテキスト項目配列のオブジェクトのフィールドの配列値の各オブジェクト。

関連項目

親トピック: SQL/JSONパス式の構文

16.2.3 パス式での否定

パス式での否定は、パスで一致したデータが複数である場合、混乱を招く可能性があります。いくつかの簡単な例を説明します。

negationフィルタ条件には、述語! (read "not")の後にフィルタ条件(括弧内には!( condition))が続きます。そのセマンティクスは、conditionが失敗する(falseを返す)場合は必ず成功します(trueを返します)。

SQL/JSON条件json_existsは、特定のJSONデータの存在をチェックします。また、json_valueなどのSQL問合せファンクションは、既存のJSONデータを検索して返します。述語!は、引数条件によって提示される存在がfalseであることを確認します。これは、非存在をチェックすることを意味します。

中置の等しくない比較述語は!=または<>と記述可能で、その2つの引数が異なる(trueを返す)か同じ(falseを返す)かをチェックします。

これはすべて簡単なことのように聞こえますが、パス式で一致したデータが複数ある場合は、複雑になることがあります...

次のドキュメントについて考えてみます。

{"customer" : "A",
 "locations" : [ {"country" : "France"} ]}

{"customer"  : "B",
 "locations" : [ {"country" : "Germany"} ]}

{"customer"  : "C",
 "locations" : [ {"country" : "France"}, {"country" : "Spain"} ]}

{"customer"  : "D",
 "locations" : [ {"country" : "Spain"} ]}

{"customer"  : "E",
 "locations" : []}

{"customer"  : "F"}

次のパス式について考えてみます。


-- Path 1: locations that include the country of France.
$.locations?( @.country == "France" )

-- Path 2: locations that include a country other than France.
$.locations?( @.country != "France" )

-- Path 3: locations that do NOT include the country of France.
$.locations?( !(@.country == "France") )

-- Path 4: locations with one or more countries, NONE of which is France.
$.locations?( exists@.country && !(@.country == "France") )

-- Path 5: locations with a country other than France or Germany.
$.locations?( (@.country != "France") || (@.country != "Germany") )

  • パス1は、顧客AおよびCのドキュメントを返します。それらのlocations配列には、値が"France"のフィールドcountryを持つ要素があるためです。

  • パス2は、顧客B、CおよびDのドキュメントを返します。それらのlocations配列には、値が"France"でないフィールドcountryを持つ要素(CおよびDの場合は"Spain"、Bの場合は"Germany")があるためです。顧客Eのドキュメントを返すパスはありません。そのlocations配列にそのような要素(countryがFranceかどうか)がないためです。そのlocations配列にはまったく要素がありませんまた、locationsフィールドがないため、どのパスも顧客Fのドキュメントを返しません。

  • パス3は、顧客B、DおよびEのドキュメントを返します。それらのlocations配列には、値が"France"であるフィールドcountryを持つ要素がないためです。パス3は、顧客AおよびCのドキュメントを返しません。それらのlocations配列には、値が"France"のフィールドcountryを持つ要素があるためです。また、locationsフィールドがないため、顧客Fのドキュメントは返されません。

    特に、パス2とパス3の結果が異なることに注意してください。France以外の国を含めることは、Franceという国を含めないことと同じではありません。パス2にはFranceではないcountryが必要ですが、パス3には値がFranceであるcountryが存在しないことが必要です。パス2にはCが含まれ、Eは除外されます。GermanyはFranceではなく、Eには国が存在しないためです。パス3にはEが含まれ、Cは除外されます。Eにはcountryがなく、CのlocationsにはFranceが含まれているためです。

  • パス4は、顧客BおよびDのドキュメントを返します。これはパス3と同じですが、フィールドcountryが存在する必要があり、顧客Eのドキュメントは除外されています。

  • パス5は、locationsフィールドのないFを除くすべての顧客のドキュメントを返します。!=テストは、比較するcountryフィールドがないため、顧客Eに対しては成功します。また、すべての国がFranceでもGermanyでもないため、countryフィールドを含むすべてのドキュメントが成功します。顧客Fのドキュメントにのみcountryフィールドがありません。

述語inを使用する次のパスも考慮してください。 

-- Path 6: locations that include France or Germany.
@.locations?( @.country in ("France", "Germany") )

-- Path 7: locations that do NOT include France or Germany.
@.locations?( !(@.country in ("France", "Germany")) )

-- Path 8: locations that have one or more countries, NONE of which is France or Germany.
@.locations?( exists(@.country)
              &&
              !(@.country in ("France", "Germany")) )

  • パス6は、顧客A、BおよびCのドキュメントを返します。locations配列には、値がinで示されるcountryフィールドのセット("France"、"Germany")があるためです — AおよびCに対する"France"、Bに対する"Germany"。

  • パス7では、FranceとGermanyの顧客のドキュメントが除外されます。これは、Spainのみにある顧客Dおよび空のlocations配列を持つ顧客Eのドキュメントを返します。locationsフィールドがないため、顧客Fのドキュメントは返されません。

  • パス8は、顧客Dのドキュメントのみを返します。顧客A、BおよびCのドキュメントは、FranceまたはGermanyに事業所があるため除外されます。顧客Eのドキュメントはcountryフィールドがないため除外され、顧客Fのドキュメントはlocationsフィールドがないため除外されます。

関連項目

親トピック: SQL/JSONパス式の構文

16.3 SQL/JSONパス式の項目メソッド

SQL/JSONパス式で使用可能なOracle項目メソッドを示します。ターゲットJSONデータに対する動作については、一般的な用語と各項目メソッドで説明します。

項目メソッドの一般的な動作

項目メソッドは、このメソッドで終了するパス式(の残り)が対象とするJSONデータに適用されます。メソッドは、目的のデータを変換するために使用されます。パス式に渡されたSQLファンクションまたは条件は、対象データのかわりとして変換済データを使用します。項目メソッドのアプリケーションがフィルタとして機能し、この結果セットから対象データを削除する場合があります。

引数の型が間違っているなどのなんらかの理由で項目メソッドの変換が失敗した場合、パスは照合されず(参照データがない状態)、エラーは発生しません。このことは、特に、パス式が渡されるSQLファンクションまたは条件のエラー句でこのようなエラーが処理されないことを意味します。

項目メソッドは、対象のJSONデータを(おそらく別の) JSONデータに常に変換します(このデータは必ずスカラーになります)。ただし、パス式(項目メソッドの有無にかかわらず)を使用する問合せは、SQLスカラー・データ型としてデータを返すことができます。これはjson_valueの問合せまたは同等のドット表記法の問合せの場合です。

  • SQL/JSONファンクションjson_query (またはjson_queryのセマンティクスを持つjson_table列の式)の戻り値は、常にSQLデータ型JSON、VARCHAR2、CLOBまたはBLOBのJSONデータです。対象データがJSON型である場合、デフォルトの戻りデータ型はJSONです。それ以外の場合はVARCHAR2です。

  • SQL/JSONファンクションjson_value (またはjson_valueのセマンティクスを持つjson_table列の式)の戻り値は、常にJSON型以外のSQLデータ型(スカラー型、オブジェクト型またはコレクション型)です。JSONデータは返されません。パス式はJSONデータを対象とし、項目メソッドは対象のJSONデータをJSONデータに変換しますが、json_valueは、JSONデータを必ずしもサポートしないデータ型のスカラーSQL値に結果のJSONデータを変換します。

  • 項目メソッドを使用するドット表記法問合せでは、RETURNING句を使用してjson_valueを暗黙的に適用します。この句は、項目メソッドの対象となる(場合によっては変換される) JSONデータに対してスカラーSQL型を指定します。このため、項目メソッドを使用するドット表記法問合せは、常にSQLスカラー値を返します。

配列への項目メソッドの適用

項目メソッドcount()、size()およびtype()を除き、項目メソッドが配列を対象とする場合、このメソッドは配列自体ではなく各配列要素に適用されます。これらのアプリケーションの結果は、配列のかわりに複数の値として返されます。つまり、一致の結果セットには、対象の配列ではなく、変換された各配列要素が含まれています。

(このことは、実質的に、オブジェクト・ステップに対して非配列が想定される場合の配列の暗黙のアンラップと同様です。)

たとえば、$.a.method()は、項目メソッドmethod()を配列aの各要素に適用してその要素を変換し、配列のかわりに使用します。

  • 戻り型としてSQLコレクション型(varrayまたはネストされた表)を指定するjson_value問合せの場合、そのコレクション型インスタンスが返されます。これは、コレクション型定義に関して型の不一致がある場合を除いて、各配列要素に項目メソッドを適用した結果のJSON配列に対応しています。

  • 他のSQL型を返すjson_value問合せの場合、SQLのNULLが返されます。これは、項目メソッドを配列要素にマッピングすると複数の戻り値が生成され、それがjson_valueの不一致を表すためです。

  • json_queryまたはjson_queryセマンティクスを持つjson_table列式に対して、ラッパー句を使用すると変換されたすべての配列要素値を配列として取得できます。たとえば、次のような問合せになります。 

    SELECT json_query('[ "alpha", 42, "10.4" ]', '$[*].stringOnly()'
                  WITH ARRAY WRAPPER)
  FROM dual;

    [ "alpha", "10.4" ]というJSON配列を返します。返されるSQLデータ型は、対象がJSON、VARCHAR2(4000)、CLOBまたはBLOBであるJSONデータと同じです。

これに関して、項目メソッドcount()、size()およびtype()は例外です。配列に適用する場合、これらの項目メソッドは、その要素を処理するかわりに配列をそのまま扱います。たとえば: 

SELECT json_value('[ 19, "Oracle", {"a":1}, [1,2,3] ]', '$.type()')
  FROM dual;

単一のVARCHAR2値'array'を返します。json_valueはデフォルトでVARCHAR2(4000)を返します。

類似した問合せですが、json_valueではなくjson_queryを使用すると、入力JSONデータに使用されるSQLデータ型(JSON、VARCHAR2(4000)、CLOBまたはBLOB)の、単一のJSON文字列"array"を返します。

ノート:

json_value (オブジェクト型またはコレクション型以外のSQL戻り型)で行われる内容と同じことが、簡単なドット表記法問合せでも行われます。ドット表記法構文に項目メソッドが存在すると、常にjson_queryではなくjson_valueのセマンティックスとなります。これは単一のスカラーSQL値を生成します(SQLのORDER BY、GROUP BY、比較、または結合操作で使用できます)。ただし、配列値に適用される項目メソッドは、json_valueのセマンティクスによって拒否される複数の値を生成します。SQLのNULLが返されます。

データ型変換の項目メソッド

データ型変換メソッドである項目メソッドは、binary()、binaryOnly()、boolean()、booleanOnly()、date()、dateTimeOnly()、dateWithTime()、idOnly()、number()、numberOnly()、double()、dsInterval()、float()、number()、numberOnly()、string()、stringOnly()、timestamp()、toBoolean()、toDateTime()およびymInterval()です。

前述したように、項目メソッドは常に対象のJSONデータを(おそらく別の) JSONデータに変換します。ただし、このメソッドがjson_value問合せ(またはSQLデータを返す別のファンクション)で使用されると、変換結果のJSONデータはSQL戻り値に変換されます。RETURNING句がある場合は、そのデータのSQL型を指定します。その句がない場合、各項目メソッドは特定のデフォルトのSQL型になります。たとえば、項目メソッドstring()のデフォルトのSQL型は、VARCHAR2(4000)です。

json_valueセマンティクスを持つ問合せでは、通常、データ型変換項目メソッドの対象となる値は、そのメソッドのデフォルトのSQLデータ型のSQL値として解釈されていると考えることができます。つまり、その値は、そのSQLデータ型を持つRETURNING句によって制御されたかのように処理されるということです。

たとえば、項目メソッドstring()は句RETURNING VARCHAR2(4000)を含むjson_valueと同じようにターゲットを解釈します。そのため、ブールJSON値は、string()によって"true"または"false"として扱われ、null値はstring()によって"null"として扱われ、数値はstring()によって正規の文字列形式の数値として扱われます。

名前に"only"が含まれていないデータ型変換メソッドは、可能であればJSON値を(場合によってはメソッドで指名された型ファミリに含まれない値でも)メソッドのJSON型に変換し、そのメソッドのデフォルトのSQL型の値として結果を解釈します。

"only"データ型変換メソッドは、メソッドによって指名された型ファミリに含まれるJSON値のみを変換します。その他の対象とされる値は除外されます。メソッドのデフォルトのJSON型に値を変換し、その結果をメソッドのデフォルトのSQL型の値として解釈します。

numberOnly()の場合、ファミリ型は数値(数値JSON型)であり、ファミリのデフォルトのJSONタイプはnumberであり、デフォルトのSQL型はNUMBERです。dateTimeOnly()の場合、デフォルトのファミリ型はtimestampであり、デフォルトのSQL型はTIMESTAMPです。

("only"メソッドで配列を対象とする場合、変換は通常どおり、各配列要素に適用されます。)

avg()などの集計メソッドは、対象となる値をメソッドのデフォルトの型に変換し、メソッドのデフォルトのSQL型として解釈します。avg()の場合、対象となる値の型number、floatおよびdoubleは、すべてJSON型のnumberに変換され、SQLのNUMBER値として解釈されます。

abs()などの非集計メソッドは、関連する型ファミリの範囲内での変換は実施されません。そのため、abs()は文字列"-3.14"をJSONの数値に変換しますが、対象となるJSONの浮動小数点値または倍精度値をそのまま残し、それぞれSQLのBINARY_FLOATまたはBINARY_DOUBLE値として解釈します。

表16-1 項目メソッドのデータ型変換

項目メソッド 入力のJSON言語型 出力のJSON言語型 SQL型 ノート

binary()

バイナリ(識別子と非識別子の両方)

binary

RAWまたはBLOB

なし。

binary()

string

binary

RAWまたはBLOB

いずれかの入力文字が16進数値でない場合はエラーになります。

binaryOnly()

バイナリ(識別子と非識別子の両方)

binary

RAWまたはBLOB

なし。

boolean()

ブール値

ブール値

BOOLEAN

なし。

boolean()

string

ブール値

BOOLEAN

入力が"true"または"false"でない場合はエラーになります

booleanOnly()

ブール値

ブール値

BOOLEAN

なし。

date()

date、timestampまたはtimestamp with time zone

date

DATE

JSON出力は、時間コンポーネントなしのUTCです。

date()

string

date

DATE

JSON出力は、時間コンポーネントなしのUTCです。

入力が時間コンポーネントなしのISO UTCでない場合はエラーになります。

dateTimeOnly()

date、timestampまたはtimestamp with time zone

timestamp

TIMESTAMP

なし。

dateWithTime()

date、timestampまたはtimestamp with time zone

date

DATE

小数秒なしのUTC。

dateWithTime()

string

date

DATE

小数秒なしのUTC。入力がISOでない場合はエラーになります。

double()

number、doubleまたはfloat

double

BINARY_DOUBLE

なし。

double()

string

double

BINARY_DOUBLE

入力が数値表現でない場合はエラーになります。

float()

number、doubleまたはfloat

float

BINARY_FLOAT

入力が範囲外の場合はエラーになります。

float()

string

float

BINARY_FLOAT

入力が数値表現でない場合はエラーになります。

idOnly()

バイナリ識別子

バイナリ識別子

RAW

なし。

number()

number、doubleまたはfloat

number

NUMBER

入力が範囲外の場合はエラーになります。

number()

string

number

NUMBER

入力が数値表現でない場合はエラーになります。

numberOnly()

number、doubleまたはfloat

number

NUMBER

なし。

string()

すべて

string

VARCHAR2またはCLOB

結果のSQL値は、出力のJSON言語文字列がUTF-8であっても、データベース文字セットになります。

stringOnly()

string

string

VARCHAR2またはCLOB

結果のSQL値は、入力および出力のJSON言語文字列がUTF-8であっても、データベース文字セットになります。

timestamp()

date、timestampまたはtimestamp with time zone

timestamp

TIMESTAMP

なし。

timestamp()

string

timestamp

TIMESTAMP

入力がISO UTCでない場合はエラーになります。

toBoolean()

ブール値

ブール値

BOOLEAN

なし。

toBoolean()

string

ブール値

BOOLEAN

入力文字列が"true"または"false"でない場合はエラーになります

toBoolean()

number、doubleまたはfloat

ブール値

BOOLEAN

ゼロはfalseに変換されます。それ以外のすべての数値はtrueに評価されます。

toDateTime()

date、timestampまたはtimestamp with time zone

timestamp

TIMESTAMP

なし。

toDateTime()

string

timestamp

TIMESTAMP

入力がISO UTCでない場合はエラーになります。

toDateTime()

number、doubleまたはfloat

timestamp

TIMESTAMP

数値は、1970-01-01からの秒数として解釈されます。負でない数値のみが一致します。

項目メソッドの説明

  • abs(): 対象となるJSONの数値の絶対値。SQLファンクションABSの使用に対応します。

  • atan(): 対象となるJSON数値(ラジアン単位)の三角法逆正接関数。SQLファンクションATANの使用に相当します。

  • avg(): 対象となるすべてのJSON数値の平均値。対象となる値が数値でない場合はエラーになります。SQLファンクションAVGの使用(オプションの動作なし)に相当します。これは集計メソッドです。

  • binary(): 対象となるJSON値(16進文字列またはJSONバイナリ値)のSQL RAW解釈。文字列の場合、SQLファンクションhextorawは、SQLのRAW値への変換に使用されます。この項目メソッドは、JSON型として格納されているJSONデータにのみ適用されます。

  • binaryOnly(): 対象となるJSON値のSQL RAW解釈。ただし、その値がJSONバイナリ値の場合にかぎられます。フィルタとして機能し、JSONのブール値に対してのみ照合可能です。(JSON型として格納されたJSONデータのみがJSONバイナリ値を保持できます)。

  • boolean(): 対象となるJSON値のSQL BOOLEAN解釈。

    ノート:

    リリース23cより前では、SQLのVARCHAR2(20)解釈を使用していました。VARCHAR2値の取得が必要な場合(互換性の理由など)は、SQLファンクションto_charで値をラップできます。

  • booleanOnly(): 対象となるJSONデータのSQL BOOLEAN解釈。ただし、そのデータがJSONのブール値(trueまたはfalse)である場合にかぎられます。それ以外の場合は一致しません。フィルタとして機能し、JSONのブール値の場合のみ一致が可能です。

    ノート:

    リリース23cより前では、SQLのVARCHAR2(20)解釈を使用していました。VARCHAR2値の取得が必要な場合(互換性の理由など)は、SQLファンクションto_charで値をラップできます。

  • ceiling(): 最も近い整数に切り上げられた対象となるJSONの数値。SQLファンクションCEILの使用に対応します。

  • concat(): 文字列引数(2つ以上)の連結。この項目メソッドは、パス式のRHSでのみ使用できます(それ以外の場合はエラーが発生します)。

  • cos(): 対象となるJSON数値(ラジアン単位)の三角法余弦関数。SQLファンクションCOSの使用に相当します。

  • cosh(): 対象となるJSON数値(ラジアン単位)の三角法双曲線余弦関数。SQLファンクションCOSHの使用に相当します。

  • count(): 対象のJSON値の数(型にかかわらず)。これは集計メソッドです。

  • date(): 対象となるJSON値のSQL DATE解釈。対象となる値は、(1)日付または日付時刻に対してサポートされているISO 8601形式のJSON string、または(2) (データがSQL型JSONの場合) date、timestampまたはtimestamp with time zone値であることが必要です。それ以外の場合は、一致しません。

    SQL DATE値には、時間コンポーネントがありません(ゼロに設定されます)。ただし、時間の切捨てが行われる前に、ISO 8601の日時文字列で表される値にタイムゾーン・コンポーネントがある場合は、値は最初にUTCに変換され、タイムゾーン情報が考慮されます。

    たとえば、JSON文字列"2021-01-01T05:00:00+08:00"は、UTC文字列"2020-12-31 00:00:00"に相当するSQL DATE値として解釈されます。

    結果としての日付は、データのタイム・ゾーンを忠実に反映します(対象と結果が同じ日付を表します)が、結果は単純な時間の切捨てによって生成されるものとは異なることがあります。(この動作は、Oracle SQLファンクションjson_scalarの動作と似ています)。

  • dateTimeOnly(): 対象となるJSON値のSQL TIMESTAMP解釈。対象となる値は、date、timestampまたはtimestamp with time zone値であることが必要です。(JSON型として格納されたJSONデータのみが、そのような値を保持できます)。

  • dateWithTime(): ISO 8601の日時形式の時間コンポーネントがSQLのDATEインスタンスに保存されることを除き、date()と同様です。

  • double(): 対象となるJSON文字列または数値のSQL BINARY_DOUBLEでの解釈。

  • dsInterval(): 対象となるJSON文字列のSQLのINTERVAL DAY TO SECONDでの解釈。対象となる文字列データは、サポートされているISO 8601継続時間形式のいずれかである必要があります。それ以外の場合、一致はありません。

  • exp(): 対象となるJSON数値の数学的指数関数。つまり、数学定数e (オイラー数、2.71828183...)を、対象となるJSONの数値で累乗したものです。SQLファンクションEXPの使用に相当します。

  • float(): 対象となるJSON文字列または数値のSQL BINARY_FLOATでの解釈。

  • floor(): 最も近い整数に切り捨てられた対象となるJSONの数値。SQLファンクションFLOORの使用に対応します。

  • idOnly(): 対象となるJSON値のSQL RAW解釈。フィルタとして機能し、フィールド$rawidまたは$oidの拡張オブジェクトから導出されたものとして内部的にタグ付けされたJSONバイナリ値に対してのみ照合を可能にします。(JSON型として格納されたJSONデータのみがJSONバイナリ値を保持できます)。

  • indexOf(): 指定されたJSON配列の指定されたJSON値と等しい最初の要素の位置(索引)。配列は最初の引数で、等価性をチェックする値は2番目の引数です。この項目メソッドは、パス式のRHSでのみ使用できます(それ以外の場合はエラーが発生します)。

    1つまたは2つのオプション引数も受け入れられます。最初の引数は、チェックする最初の要素の配列位置です(それより前の位置はスキップされます)。2つ目は、チェックする配列要素の最大数です。(たとえば、オプションの引数を使用すると、配列をループして一致する要素を配列順に見つけることができます)。

    • データがJSONデータ型の場合は、すべてのJSON言語の値が比較可能です。比較は正規のソート順に従って実施されます。

    • JSON型ではないデータの場合は、スカラーJSON値のみが比較可能です。非スカラー・データ値は無視されます。また、場所の特定のために指定したJSON値はスカラーであることが必要です(それ以外の場合はエラーが発生します)。

  • length(): 対象となるJSON文字列内の文字数。SQL NUMBERとして解釈されます。SQLファンクションLENGTHの使用に相当します。

  • listagg(): 対象となるJSON値の連結。この値は、文字列であることが必要です(それ以外の場合はエラーが発生します)。オプションのデリミタ文字列引数を受け入れます。これは対象となる連続する文字列の間に挿入されます。SQLファンクションLISTAGGの使用に相当します。これは集計メソッドです。

  • log(): 対象となるJSON数値の数学的対数関数。SQLファンクションLOGの使用に相当します。このメソッドは、オプションで数値引数(対数の底)を受け取ります。デフォルトの底は数学定数e (オイラー数、2.71828183...)です。これは、デフォルトでは自然対数が計算されることを意味します。

  • lower(): 対象となるJSON文字列内の文字に対応する小文字の文字列。SQLファンクションLOWERの使用に相当します。

  • maxDateTime(): すべての対象となるJSONの日付時刻の最大値です。最初に、項目メソッドdateWithTime()が複数の可能性がある値のそれぞれに暗黙的に適用されます。それらの最大値(単一のTIMESTAMP値)が返されます。日付時刻に変換できない対象となるJSON値は無視されます。

  • maxNumber(): 対象となるすべてのJSON数値の最大値です。項目メソッドnumber()が、場合によっては複数の各値に暗黙的にまず適用されます。それらの最大値(1つのNUMBER値)が返されます。数値に変換できない対象のJSON値は無視されます。

  • maxString(): 照合順序を使用した、対象となるすべてのJSON文字列の最大値。項目メソッドstring()が、場合によっては複数の各値に暗黙的にまず適用されます。これらの最大値(単一のVARCHAR2値)が返されます。文字列に変換できない対象のJSON値は無視されます。

  • minDateTime(): すべての対象となるJSONの日付時刻の最小値です。最初に、項目メソッドdateWithTime()が複数の可能性がある値のそれぞれに暗黙的に適用されます。それらの最小値(単一のTIMESTAMP値)が返されます。日付時刻に変換できない対象となるJSON値は無視されます。

  • minNumber(): 対象となるすべてのJSON数値の最小値です。項目メソッドnumber()が、場合によっては複数の各値に暗黙的にまず適用されます。それらの最小値(1つのNUMBER値)が返されます。数値に変換できない対象のJSON値は無視されます。

  • minString(): 照合順序を使用した、対象となるすべてのJSON文字列の最小値。項目メソッドstring()が、場合によっては複数の各値に暗黙的にまず適用されます。これらの最小値(単一のVARCHAR2値)が返されます。文字列に変換できない対象のJSON値は無視されます。

  • number(): 対象となるJSON文字列または数値のSQL NUMBERの解釈。

  • numberOnly(): JSONのブール値である場合、対象となるJSONデータのSQL NUMBERの解釈。それ以外の場合、一致はありません。フィルタとして機能し、JSONの数値の場合のみ一致が可能です。

  • pow(): 対象となるJSON数値の数学的べき乗関数。これにより、対象となるJSON数値が指定された指数(必須の数値引数)でべき乗されます。SQLファンクションPOWERの使用に相当します。

  • round(): ゼロに最も近い整数に丸められた、対象となるJSONの数値。SQLファンクションROUNDの使用(桁数を指定するオプションの動作なし)に相当します。

  • sin(): 対象となるJSONの数値(ラジアン単位)の三角法正弦関数。SQLファンクションSINの使用に相当します。

  • sinh(): 対象となるJSONの数値(ラジアン単位)の三角法双曲線正弦関数。SQLファンクションSINHの使用に相当します。

  • size(): 複数のJSON値が対象である場合、各対象値にsize()を適用した結果です。そうでない場合は、次のようになります。

    • 単一の対象値がスカラーの場合は1です。

    • 単一の対象値が配列の場合は、配列の要素数です。

    • 単一の対象値がオブジェクトの場合は1です。

    この項目メソッドは、json_query、json_valueおよびjson_tableとともに使用できます。配列のデータに適用される場合、配列要素に対する暗黙的な反復は発生しません。結果の値は配列要素の数に一致します。(暗黙的反復のルールに対する例外。)

  • stddev(): 対象となるJSON値の統計的標準偏差関数。この値は、数値であることが必要です(それ以外の場合はエラーが発生します)。SQLファンクションSTDDEVの使用に相当します。これは集計メソッドです。

  • string(): 対象となるスカラーのJSON値のSQL VARCHAR2(4000)の解釈。

  • stringOnly(): JSON文字列である場合、対象となるスカラーJSON値のSQL VARCHAR2(4000)の解釈。それ以外の場合、一致はありません。フィルタとして機能し、JSON文字列の場合のみ一致が可能です。

  • substr(): 対象となるJSON文字列の部分文字列です。SQLファンクションSUBSTRの使用に相当しますが、1ベースではなく0ベースです。対象となる文字列内の部分文字列の開始位置は必須引数です。部分文字列の最大長は、オプションの(2番目の)引数になります。

  • sum(): 対象となるすべてのJSON数値の合計値です。対象となる値が数値でない場合はエラーになります。SQLファンクションSUMの使用(オプションの動作なし)に相当します。

  • tan(): 対象となるJSONの数値(ラジアン単位)の三角法正接関数。SQLファンクションTANの使用に相当します。

  • tanh(): 対象となるJSONの数値(ラジアン単位)の三角法双曲線正接関数。SQLファンクションTANHの使用に相当します。

  • timestamp(): 対象となるJSON値のSQL TIMESTAMP解釈。対象となる文字列データは、(1)日付または日付時刻に対してサポートされているISO 8601形式のJSON string、または(2) (データがSQL型JSONの場合) date、timestampまたはtimestamp with time zone値であることが必要です。それ以外の場合は一致しません。<a id="fn_6" name="fn_6" href="#fn_6" onclick='footdisplay(6, "Applying item method timestamp() to a supported ISO 8601 string <ISO-STRING> has the effect of SQL sys_extract_utc(to_utc_timestamp_tz(脚注6

  • toBoolean(): 対象となるJSON値のSQL VARCHAR2(20)解釈。これは、メソッドboolean()と同じですが、対象となる値が数値であってもかまいません。その場合、ゼロはfalseに相当し、その他の数字はtrueに相当します。

  • toDateTime(): 対象となるJSON値のSQL TIMESTAMP解釈。対象となる文字列データは、(1)日付または日付時刻に対してサポートされているISO 8601形式のJSON string、(2)負でない数値、または(3) (データがSQL型JSONの場合) date、timestampまたはtimestamp with time zone値であることが必要です。それ以外の場合は一致しません。<a id="fn_7" name="fn_7" href="#fn_7" onclick='footdisplay(7, "Applying item method toDateTime() to a supported ISO 8601 string <ISO-STRING> has the effect of SQL sys_extract_utc(to_utc_timestamp_tz(脚注7

  • type(): 対象となるデータのJSON言語データ型ファミリの名前、またはそのファミリ・メンバーの名前。SQLのVARCHAR2(20)値として解釈されます。たとえば、数値型ファミリの場合、返される値は"double"、"float"または"number"です。「JSONデータ型の値の比較とソート」を参照してください。

    この項目メソッドは、json_query、json_valueおよびjson_tableとともに使用できます。配列のデータに適用される場合、配列要素に対する暗黙的な反復は発生しません。結果の値は"array"となります。(暗黙的反復のルールに対する例外。)

    • null値の場合、"null"。

    • ブール値(trueまたはfalse)の場合、"boolean"。

    • 数値の場合、"number"。

    • 文字列の場合、"string"。

    • 配列の場合、"array"。

    • オブジェクトの場合、"object"。

    • SQLのBINARY_DOUBLE値に対応する数値の場合、"double"。(JSON型のデータの場合のみ。)

    • SQLのBINARY_FLOAT値に対応する数値の場合、"float"。(JSON型のデータの場合のみ。)

    • SQLのRAW値に対応する値の場合、"binary"。(JSON型のデータの場合のみ。)

    • SQLのDATE値に対応する値の場合、"date"(JSON型のデータの場合のみ。)

    • SQLのTIMESTAMP値に対応する値の場合、"timestamp"。(JSON型のデータの場合のみ。)

    • SQLのTIMESTAMP WITH TIME ZONE値に対応する値の場合、"timestamp with time zone"。(JSON型のデータの場合のみ。)

    • SQLのINTERVAL DAY TO SECOND値に対応する値の場合、"daysecondInterval"。(JSON型のデータの場合のみ。)

    • SQLのINTERVAL YEAR TO MONTH値に対応する値の場合、"yearmonthInterval"。(JSON型のデータの場合のみ。)

  • upper(): 対象となるJSON文字列内の文字に対応する大文字の文字列。SQLファンクションUPPERの使用に相当します。

  • variance(): 対象となるJSON値の統計的分散関数。この値は、数値であることが必要です(それ以外の場合はエラーが発生します)。SQLファンクションVARIANCEの使用に相当します。これは集計メソッドです。

  • ymInterval(): 対象となるJSON文字列のSQLのINTERVAL YEAR TO MONTHでの解釈。対象となる文字列データは、サポートされているISO 8601継続時間形式のいずれかである必要があります。それ以外の場合、一致はありません。

項目メソッドのabs()、ceiling()、double()、floor()、size()およびtype()は、SQL/JSON標準の一部です。その他のメソッドはSQL/JSON標準へのOracleによる拡張です。該当するメソッドは、atan()、avg()、binary()、binaryOnly()、boolean()、booleanOnly()、concat()、cos()、cosh()、date()、dateTimeOnly()、dateWithTime()、double()、 dsInterval()、exp()、float()、idOnly()、indexOf()、length()、listagg()、log()、lower()、maxDateTime()、maxNumber()、maxString()、minDateTime()、minNumber()、minString()、number()、numberOnly()、pow()、round()、sin()、sinh()、stddev()、string()、stringOnly()、substr()、sum()、tan()、tanh()、timestamp()、toBoolean()、toDateTime()、upper()、variance()およびymInterval()です。

項目メソッドのavg()、count()、listagg()、maxNumber()、minNumber()、maxString()、minString()、stddev()、sum()およびvariance()は、集計項目メソッドです。各対象値に個別に作用するのではなく、対象値すべてに対して作用します。たとえば、パス式が数値に変換できる複数の値を対象としている場合、sum()はそれらの数値の合計を返します。

パス式が配列を対象としている場合、集計項目メソッドをその配列に適用すると、配列は単一の値として処理されます。配列要素が暗黙的に反復されることはありません。たとえば、count()は対象となる配列を1つの値としてカウントし、size()は配列要素のサイズではなく配列のサイズを返します。

集計項目メソッドを配列要素に適用する場合は、ワイルドカード*を使用して、それらの要素を明示的に反復処理する必要があります。たとえば、指定された文書のLineItemsフィールドの値が配列の場合、$.LineItems.count()は1を返しますが、$.LineItems[*].count()は配列要素の数を返します。

集計項目メソッドは、それが含まれるパス式(ドット表記法)と同様に、一度に1つのJSON文書に適用されます。パス式によってその文書内で対象となる複数の値を集計します。問合せは、各文書について1つの行を返します。複数の文書の情報を集計するのではなく、SQL集計関数と同様に、すべての文書に対して1つの行を返します。例16-1と例16-2を参照してください。

関連項目:

  • 『Oracle Database SQL言語リファレンス』のABS

  • 『Oracle Database SQL言語リファレンス』のATAN

  • 『Oracle Database SQL言語リファレンス』のAVG

  • 『Oracle Database SQL言語リファレンス』のCEIL

  • 『Oracle Database SQL言語リファレンス』のCOS

  • 『Oracle Database SQL言語リファレンス』のCOSH

  • 『Oracle Database SQL言語リファレンス』のEXP

  • 『Oracle Database SQL言語リファレンス』のLENGTH

  • 『Oracle Database SQL言語リファレンス』のLISTAGG

  • 『Oracle Database SQL言語リファレンス』のLOG

  • 『Oracle Database SQL言語リファレンス』のLOWER

  • 『Oracle Database SQL言語リファレンス』のPOWER

  • 『Oracle Database SQL言語リファレンス』のROUND (number)

  • 『Oracle Database SQL言語リファレンス』のSIN

  • 『Oracle Database SQL言語リファレンス』のSINH

  • 『Oracle Database SQL言語リファレンス』のSTDDEV

  • 『Oracle Database SQL言語リファレンス』のSUBSTR

  • 『Oracle Database SQL言語リファレンス』のSUM

  • 『Oracle Database SQL言語リファレンス』のTAN

  • 『Oracle Database SQL言語リファレンス』のTANH

  • 『Oracle Database SQL言語リファレンス』のVARIANCE

  • 『Oracle Database SQL言語リファレンス』のUPPER

項目メソッドおよびJSON_VALUEのRETURNING句

一部の項目メソッドは、対象のJSONデータをSQLのデータ型であるかのように解釈するため、RETURNING句のかわりにjson_valueとともに使用できます。また、列型を指定するかわりにjson_tableとともに使用できます。つまり、項目メソッドは、抽出されたJSONデータに返されるSQLデータ型を指定するために使用できます。

このような項目メソッドをjson_valueのRETURNING句またはjson_tableの列型指定とともに使用することもできます。抽出されたJSONデータに使用するSQLデータ型が項目メソッドおよびjson_value RETURNING句またはjson_table列タイプの両方によって制御されている場合はどうなりますか。

  • 2つのデータ型に互換性がある場合、RETURNING句または列のデータ型が使用されます。これらのために、VARCHAR2はVARCHAR2とCLOBの両方と互換性があります。

  • これらのデータ型に互換性がない場合、静的コンパイル時にエラーが発生します。

表16-2 型変換項目メソッドとRETURNINGの型の互換性

項目メソッド 互換性のあるRETURNING句のデータ型
string()、stringOnly()、minString()またはmaxString() VARCHAR2またはCLOB (string()がJSON null値に対してSQL NULLを返すことを除く)
number()、numberOnly()、avg()、sum()、count()、minNumber()またはmaxNumber() NUMBER
double() BINARY_DOUBLE
float() BINARY_FLOAT
date()

DATE (ゼロに設定された)切り捨てられた時間コンポーネントを含み、RETURNING DATE TRUNCATE TIMEに対応

JSON値がタイムゾーン情報を含むISO文字列である場合、表された日付時刻は、最初にUTCに変換されてからタイムゾーンが考慮されます。
dateWithTime() DATE (時間コンポーネントを含み、RETURNING DATE PRESERVE TIMEに対応)
timestamp()、toDateTime() TIMESTAMP
ymInterval() INTERVALYEARTOMONTH
dsInterval() INTERVALDAYTOSECOND
boolean()、booleanOnly()またはtoBoolean() VARCHAR2、BOOLEAN
binary() RAW

RETURNING句または列指定を使用すると、文字データの長さ、および数値データの精度とスケールを指定できます。これにより、対象データの比較のために項目メソッドで指定されるものよりも、抽出に対して正確にSQLデータ型を割り当てることができます。

たとえば、項目メソッドstring()およびRETURNING VARCHAR2(150)を使用する場合、戻されるデータのデータ型はVARCHAR2(150)で、VARCHAR2(4000)ではありません。

例16-1 各文書のフィールド値の集計

この例では、項目メソッドavg()を使用して、JSON文書のすべてのLineItems要素のQuantityフィールドの値を集計し、各文書の平均を個別の結果行として返します。 

SELECT json_value(po_document, 
                  '$.LineItems[*].Quantity.avg()')
  FROM j_purchaseorder;

例16-2 すべての文書のフィールド値の集計

この例では、SQLファンクションavgを使用して、すべてのJSON文書の明細項目Quantityの平均値を集計し、一連の文書全体の平均を単一行として返します。指定された文書のすべての明細項目の平均数量は、項目メソッドavg()を使用して計算されます。 

SELECT avg(json_value(po_document, 
                      '$.LineItems[*].Quantity.avg()'))
  FROM j_purchaseorder;

関連項目

親トピック: SQL/JSONパス式

16.4 比較における型

SQL/JSONパス式のフィルタ条件での比較は、コンパイル時に静的に型を指定します。比較のオペランドの有効な型が同じであると認識されない場合、型キャストでの調整が行われることがあります。

SQL/JSONパス式がJSONデータを対象としているため、比較のオペランドはJSON値です。標準のJSON値の厳密な型の比較は単純です。JSONデータ型である文字列、数値、null、オブジェクトおよび配列は、相互に排他的であり、比較できません。

ただし、JSON型の値は比較可能です(「JSONデータ型の値の比較とソート」を参照)。また、パス式では比較オペランドがSQLデータ型の値として解釈(基本的にキャスト)されることがあります。たとえば、number()などの一部の項目メソッドが使用されている場合などです。この項は、このような有効値の型チェックに対応しています。

このような型キャストは、次のいずれかの方法で防止できます。

  • "only"項目メソッドを明示的に使用します。たとえば、メソッドnumberOnly()を使用すると、数値への暗黙的な型キャストが回避されます。

  • TYPE (STRICT)句を(json_transform、json_value、json_transformまたはjson_existsで)使用します。これは、使用されるパス式全体で関連する"only"項目メソッドを適用するのと同じ効果があります。

SQLは静的に型を指定する言語のため、型はコンパイル時に決定されます。同じことがSQL/JSONパス式に適用され、特にフィルタ条件での比較にも適用されます。これは、評価方法に関係なく(索引、マテリアライズド・ビューおよびインメモリー・スキャンなどの機能を使用するかどうかに関係なく)、問合せに対して同じ結果が得られるという意味です。

これを実現するには、次のようにします。

  • 両方のオペランドの型が認識されており、同じである場合、型チェックが実行されます。

  • 両方のオペランドの型が不明の場合、コンパイル時にエラーが発生します。

  • 1つのオペランドの型が認識されていて、もう一方の型が不明な場合、後者のオペランドは前者の型にキャストされます。

    たとえば、$.a?(@.b.c == 3)で$a.b.cの型がコンパイル時に不明とします。パス式は、$.a?(@.b.c.number() == 3)としてコンパイルされます。実行時に、$a.b.cと一致するデータの数値へのキャストが試行されます。文字列値"3"は数値3にキャストされ、この比較を満たします。脚注8

  • 両方のオペランドの型は認識されており、これらが異なっている場合、一方の型のもう一方の型へのキャストが試行されます。詳細を次に示します。

次の組合せで使用される比較オペランドの調整の試行が、型キャストによって行われます。他のオペランドと互換性のある型にするために、型キャストの項目メソッドがオペランドのいずれかに暗黙的に適用されます。

  • doubleと比較される数値 — double()は暗黙的に通知に適用され、これをdouble値にします。

  • floatと比較される数値 — float()は暗黙的に数値に適用され、これをfloat値にします。

  • 日付と比較されるサポートされているISO 8601形式の文字列 — date()は暗黙的に文字列に適用され、これを日付値にします。このため、UTCタイムゾーン(協定世界時、ゼロのオフセット)はデフォルトとして使用され、文字列で指定された任意のタイムゾーンを考慮します。

  • タイムゾーンが含まれていないタイムスタンプと比較される、サポートされているISO 8601形式の文字列 — timestamp()が暗黙的に文字列に適用され、これをタイムスタンプ値にします。このため、UTCタイムゾーン(協定世界時、ゼロのオフセット)はデフォルトとして使用され、文字列で指定された任意のタイムゾーンを考慮します。

次の組合せで使用される比較オペランドは調整されず、コンパイル時のエラーが発生します。

  • number、doubleまたはfloatとnumber、doubleまたはfloat以外の型の比較。

  • ブールとブール以外の型の比較。

  • 文字列がサポートされているISO 8601形式である場合を除く、文字列と比較される日付またはタイムスタンプ。

  • dateと(サポートされるISO 8601形式の)文字列以外の日付ではない型の比較。

  • 文字列以外の非タイムスタンプ型(サポートされているISO 8601形式)と比較されるタイムスタンプ(タイムゾーンがある場合とない場合)。

  • タイムスタンプとタイムゾーン付きのタイムスタンプの比較。

  • JSON null以外の任意の型と比較されるJSON null型。

ノート:

JSONデータ型の値をSQLで比較する場合、比較する値のサイズ(SQL比較用にエンコードされた値)は32Kバイト未満である必要があります。そうでない場合は、エラーが発生します。実際には、この比較用にエンコードされたSQLサイズは、同じJSONデータのテキスト表現のサイズとほぼ同じです。

たとえば、この問合せでは、deptおよびnameフィールドのエンコードされたサイズは、それぞれ32K未満である必要があります。 

SELECT * 
  FROM emp t
  WHERE t.data.dept = 'SALES' ORDER BY t.data.name

この制限は、SQL句ORDER BYおよびGROUP BY、およびSQL値比較演算子(WHERE句の>など)の使用に適用されます。

より正確には、制限はSQL自体で行われる比較およびソートにのみ適用されます。JSON言語内で実行される比較またはソートには適用されません。つまり、json_transformやjson_existsなど、JSONのSQL演算子によって実行される比較またはソートにサイズ制限はありません。特に、この制限はSQL/JSONパス式での比較には適用されません。

関連項目

親トピック: SQL/JSONパス式


脚注の凡例

脚注1: 範囲指定内のtoは、非公式に配列スライス演算子と呼ばれることがあります。
脚注2: フィルタ条件またはフィルタ式は、非公式に述語と参照されることがあります。ただし、フィルタ条件は、実際には引数への述語の適用です。
脚注3: 単一の値リストのin条件は、単一の等価比較と同等です。たとえば、@.z in ("a")は@.z == "a"と同等です。値のないin条件(たとえば@.z in ())は、常に照合できません。
脚注4: 空の値リスト(値なしまたは変数)では、エラーは発生しませんが、一致することもありません。
脚注5: !=は、SQL/JSON標準比較述語<>のOracle別名です。
脚注6: 項目メソッドtimestamp()を、サポートされているISO 8601文字列<ISO-STRING>に適用すると、SQL sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>)の効果が得られます。
脚注7: 項目メソッドtoDateTime()を、サポートされているISO 8601文字列<ISO-STRING>に適用すると、SQL sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>)の効果が得られます。負でない数値は、1970-01-01からの秒数として解釈されます。
脚注8: このようなキャストを回避するには、項目メソッドnumberOnly(): $.a?(@.b.c.numberOnly() == 3)を明示的に適用します。文字列値"3"を含むデータは一致せず、フィルタで除外されます。