12 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項目メソッドについて説明します。
• ISO 8601の日時のサポート
  国際標準化機構(ISO)の標準8601は、日付と時間を表すために国際的に受け入れられている方法について説明しています。Oracle Databaseでは、ISO 8601の日付と時刻の形式の多くをサポートしています。
• 比較における型
  SQL/JSONパス式のフィルタ条件における比較では、コンパイル時に静的に型が指定されます。比較のオペランドの有効な型が同じであると認識されない場合、型キャストでの調整が行われることがあります。

12.1 SQL/JSONパス式の概要

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

JSONは、JavaScript値の表記法です。JSONデータがデータベースに格納されている場合、XMLデータ用のXQueryまたはXPath式とある程度似たパス式を使用してこのデータを問い合せることができます。SQL/XMLでXQuery式を使用してXMLデータに対するSQLのアクセスが許可されるのと同じように、Oracle Databaseでは、SQL/JSONパス式を使用して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の場合のように値のシーケンスを戻す動作とみなすことも、複数の値を戻す動作とみなすこともできます。(ユーザーから見えるシーケンスが現れることはありません。)

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

12.2 SQL/JSONパス式の構文

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

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

• 基本的なSQL/JSONパス式の構文
  SQL/JSONパス式の基本的な構文を示します。コンテキスト項目、0(ゼロ)または複数のオブジェクト・ステップまたは配列ステップ、コンテキスト項目の性質に応じてオプションの関数ステップの順で構成されます。例を示します。
• SQL/JSONパス式の構文の緩和
  暗黙的な配列のラップおよびアンラップを可能にするため、SQL/JSONパス式の基本的な構文は緩和されています。これは、データが発展して特定のJSON値のかわりにこのような値の配列が使用される(またはこの逆)場合、コード内のパス式を変更する必要がないことを意味します。例を示します。

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

SQL/JSONパス式の基本的な構文を示します。コンテキスト項目、0(ゼロ)または複数のオブジェクト・ステップまたは配列ステップ、コンテキスト項目の性質に応じてオプションの関数ステップの順で構成されます。例を示します。

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

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

• SQL/JSONの基本的なパス式(ここでは、単にパス式と呼ぶ)は単純な絶対パス式であり、オプションでフィルタ式が付加されます。

  オプションのフィルタ式を使用できるのは、SQL条件json_existsでパス式が使用される場合のみです。フィルタ式の後にステップを続けることはできません。(たとえば、$.a?(@.b == 2).cは許可されません。)

• 単純な絶対パス式は、パス式のコンテキスト項目(つまり、照合するJSONデータ)を表すドル記号($)から始まります。このデータは、SQL/JSONファンクションに引数として渡されたSQL式の評価結果です。

  ドル記号の後、0(ゼロ)個以上のパス・ステップが続きます。各ステップは、コンテキスト項目がJSONオブジェクトを表すかJSON配列を表すかに応じて、オブジェクト・ステップまたは配列ステップにできます。単純なパス式の最後のステップに、オプションの関数ステップを1つ指定できます。

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

• 配列ステップは、左大カッコ([)の後ろに、すべての配列要素を表すアスタリスク(*)ワイルドカードまたはカンマで区切られた1つ以上の特定の配列索引または範囲指定のどちらかが続き、その後ろに右大カッコ(])が付いた形式です。パス式では、配列に対するJavaScript変換の場合のように、配列索引付けは0を基準として行われます(0、1、2、...)。範囲指定の形式はNからMであり、NおよびMは配列索引であり、Nは必ずMより小さい値である必要があります。(NがMより小さい値でない場合、問合せのコンパイル時にエラーが発生します。)アスタリスクと配列索引または範囲指定の両方を使用すると、エラーが発生します。

  索引または範囲指定を使用する場合、一括して指定される配列要素は、反復せずに昇順で指定する必要があり、そうでない場合はコンパイル時にエラーが発生します。たとえば、[3, 1 to 4]、[4, 2]、[2, 3 to 3]および[2, 3, 3]のぞれぞれでエラーが発生します。最初の2つでエラーが発生するのは順序が昇順でないためで、残りの2つでエラーが発生する理由は、要素番号3(索引付けが0(ゼロ)を基準にしているため、4つ目の要素)が繰り返されているためです。

  同様に、照合の結果として生じる配列値内の要素は昇順であり、反復は行われません。パス式にアスタリスクが使用される場合、すべての配列要素が配列順序で戻されます。

• 単一の関数ステップはオプションです。存在する場合、これは単純な絶対パス式の最後のステップになります。したがって、1つ存在する場合はフィルタ式の直前に表示されます。これはドット(.)であり、後にSQL/JSON項目メソッドが続きます。メソッドには左カッコ(()と右カッコ())が付きます。これらのカッコの中にホワイトスペースを入れることができます(このようなホワイトスペースは重要ではありません)。項目メソッドは、同じパス式内の先行する部分が対象とするデータに適用されます。この部分は、目的のデータを変換するために使用されます。パス式に渡された関数または条件は、対象データのかわりとして変換済データを使用します。

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

• フィルタ条件では、その引数に述語(ブール型関数)が適用されます。次のいずれかが該当します。この例の、cond、cond1およびcond2が、フィルタ条件を表します。

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

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

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

  • ! ( cond ): condの否定を表し、condが満たされてはならないことを意味します。

  • exists (、単純な相対パス式、): 対象となるデータが存在します。

  • 比較(次のいずれか)

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

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

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

    比較述語は、==、<>、!=<a id="fn_1" name="fn_1" href="#fn_1" onclick='footdisplay(1, "!= is an Oracle alias for the SQL/JSON standard comparison predicate <>.")'>^脚注1、<、<=、>=または>です。

    SQL/JSON変数は、ドル記号($)の後に、json_existsのPASSING句内にバインドされるSQL識別子の名前が続きます。

  このように、フィルタ条件で使用できる述語は、&&、||、!、exists、==、<>、!=、<、<=、>=および>です。

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

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

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

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

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

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

  • 条件aが満たされる。

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

• 単純な相対パス式は、アット・マーク(@)の後に0(ゼロ)個以上のパス・ステップが続きます。アット・マークは、パス式のカレント・フィルタ項目(フィルタに先行する(周囲の)パス式の部分に一致するJSONデータ)を表します。単純なパス式は、パス式がコンテキスト項目に対して照合されるのと同じように、カレント・フィルタ項目に対して照合されます。

• 単純なパス式には、単純な絶対パス式と単純な相対パス式があります。(前者は$で始まり、後者は@で始まります。)

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

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

• $.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番目の順です。

• $.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 > 2014) – 配列cars(配列friendsの4番目の要素であるオブジェクトのフィールド)の最初のオブジェクト。ただし、そのフィールドyearの値が2014よりも大きいもの。

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

• $.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オブジェクトに適用されます。

関連項目:

ISO 8601 (ISOの日付形式の詳細)

12.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に相当)コンテキスト項目配列のオブジェクトのフィールドの配列値の各オブジェクト。

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

SQL/JSONパス式で使用できるOracle項目メソッドについて説明します。

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

次の項目メソッドの説明で、対象となるJSON値が特定のSQLデータ型の値として解釈される場合があると記述されています。これは、そのSQLデータ型を含むjson_value RETURNING句で制御されたかのように処理されることを意味します。

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

名前に"only"が含まれるデータ型の変換メソッドは、"only"が含まれない名前の対応するメソッドと同じです。前者は、特定の型であるJSON値(numberなど)のみを関連のSQLデータ型(NUMBERなど)に変換する点が異なります。名前に"only"が含まれないメソッドは、任意のJSON値を指定したSQLデータ型に変換できます。

使用できる項目メソッドは、次のとおりです。

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

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

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

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

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

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

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

• length(): 対象となるJSON文字列内の、SQL NUMBERとして解釈される文字数。

• lower(): 対象となるJSON文字列内の文字に対応する小文字の文字列。

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

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

• size(): 配列内の要素数。あるいはスカラーまたはオブジェクトの場合は1。この項目メソッドは、json_query、json_valueおよびjson_tableとともに使用できます。

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

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

• timestamp(): 対象となるJSON文字列のSQL TIMESTAMPの解釈。対象となる文字列データは、サポートされているISO 8601形式のいずれかである必要があります。それ以外の場合、一致はありません。<a id="fn_2" name="fn_2" href="#fn_2" onclick='footdisplay(2, "Applying item method timestamp() to a supported ISO 8601 string <ISO-STRING> has the effect of SQL sys_extract_utc(to_utc_timestamp_tz(^脚注2

• type(): SQL VARCHAR2(20)値として解釈される、対象となるデータのJSONデータ型の名前。この項目メソッドは、json_query、json_valueおよびjson_tableとともに使用できます。

  • nullの値の場合、"null"。

  • trueまたはfalseの値の場合、"boolean"。

  • 数値の場合、"number"。

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

  • 配列の場合、"array"。

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

• upper(): 対象となるJSON文字列内の文字に対応する大文字の文字列。

項目メソッドboolean()、booleanOnly()、date()、length()、lower()、number()、numberOnly()、string()、stringOnly()、timestamp()およびupper()は、SQL/JSON標準に対するOracleの拡張機能です。その他の項目メソッドのabs()、ceiling()、double()、floor()、size()およびtype()は標準に含まれています。

注意:

• 配列に項目メソッドを適用した場合、このメソッドは実際には配列要素それぞれに適用されます。たとえば、$.a.method()は、項目メソッドmethod()を配列aの各項目に適用して変換します。aのかわりに、値が変換された結果の配列が照合に使用されます。

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

  SELECT json_query('[19, "Oracle", null, true, false, {"a":1},[1,2,3]]',
                    '$[*].type()'
                    WITH ARRAY WRAPPER)
    FROM dual;

  次の配列値が戻されます。

  ["number","string","null","boolean","boolean","object","array"]

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

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

このような項目メソッドをjson_valueのRETURNING句またはjson_tableの列型指定とともに使用することもできます。

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

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

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

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

12.4 ISO 8601日付および時間のサポート

国際標準化機構(ISO)の標準8601は、日付と時間を表すために国際的に受け入れられている方法について説明しています。Oracle Databaseでは、ISO 8601の日付と時刻の形式の多くをサポートしています。

国際標準化機構(ISO)の標準8601は、日付と時間を表すために国際的に受け入れられている方法について説明しています。適切なOracle Databaseの日付と時刻の値として、最も一般的なISO 8601日時形式の文字列を操作できます。サポートされているISO 8601形式は、基本的に数字のみで言語に依存しない明確な形式です。

これは日付および時間に許可されている構文です。

• 日付(のみ): YYYY-MM-DD

• 日付と時刻: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

各要素の意味は次のとおりです。

• YYYYは、4桁の10進数で年を指定します。

• MMは、00から12までの2桁の10進数で月を指定します。

• DDは、00から31までの2桁の10進数で日を指定します。

• hhは、00から23までの2桁の10進数で時間を指定します。

• mmは、00から59までの2桁の10進数で分を指定します。

• ss[.s[s[s[s[s]]]]]は、00から59までの2桁の10進数で秒を指定します。オプションで、小数点を1から6桁の10進数で追加します(秒の端数部分を表します)。

• Zは、UTC時間(タイムゾーン0)を指定します。(–00:00ではなく、+00:00で指定することもできます。)

• (+|-)hh:mmは、UTCとの差異でとタイムゾーンを指定します。(+または–のいずれか1つを指定する必要があります。)

時間の値の場合、タイムゾーン部分はオプションです。これが指定されていない場合、UTC時間が使用されます。

他のISO 8601の日時構文はサポートされていません。具体的には、次のとおりです。

• ハイフンで始まるマイナスの日付(例: –2018–10–26T21:32:52) (BCE1年より前の日付)はサポートされていません。

• ハイフンおよびコロンの区切り文字が必要です。いわゆる基本形式YYYYMMDDThhmmssはサポートされていません。

• 序数の日付(年と通算日、カレンダ週と曜日番号)はサポートされていません。

• 年に4桁より大きい桁数を使用することは、サポートされていません。

サポートされている日付および時間は、次のとおりです。

• 2018–10–26T21:32:52

• 2018-10-26T21:32:52+02:00

• 2018-10-26T19:32:52Z

• 2018-10-26T19:32:52+00:00

• 2018-10-26T21:32:52.12679

サポートされていない日付と時間は、次のとおりです。

• 2018-10-26T21:32 (時間が指定された場合、そのすべての部分が存在する必要があります)

• 2018-10-26T25:32:52+02:00 (時間部分は25で、範囲外です)

• 18-10-26T21:32 (年がすべて指定されていません)

関連項目:

• ISO 8601標準

• WikipediaのISO 8601

12.5 比較における型

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

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

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

onlyが付く項目メソッドのいずれかを使用して、明示的にこのような型キャストを回避できます。たとえば、メソッドnumberOnly()を使用すると、数値への暗黙的な型キャストが回避されます。

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

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

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

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

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

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

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

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

• double型以外の非数値型と比較される数値。

• 数値以外の非double型と比較されるdouble型。

• double型と比較される文字列。

• 非ブール型と比較されるブール値。'true'または'false'の文字列値(または'tRUe'などの大/小文字のバリアント)との比較の場合を除きます。

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

• 文字列以外の非日付型と比較される日付。

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

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

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

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

• ブールと比較される'true'または'false'の文字列値(または'tRUe'などの大/小文字のバリアント) - boolean()は暗黙的に文字列に適用され、これをブール値にします。

• 日付と比較されるサポートされているISO 8601形式の文字列 — date()は暗黙的に文字列に適用され、これを日付値にします。(存在する任意のタイムゾーン・コンポーネントは削除されます。)

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

---

脚注の凡例

脚注1: !=は、SQL/JSON標準比較述語<>のOracle別名。
脚注2: 項目メソッドtimestamp()をサポートされているISO 8601文字列<ISO-STRING>に適用すると、SQL sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>)の効果があります。
脚注3: ここでこのようなキャストを回避するには、項目メソッドnumberOnly(): $.a?(@.b.c.numberOnly() == 3)を適用します。文字列値"3"を含むデータは一致せず、フィルタで除外されます。