12 SQL/JSONパス式

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

トピック:

• SQL/JSONパス式の概要
  Oracle Databaseでは、SQL/JSONパス式を使用したJSONデータへのSQLアクセスが提供されます。
• SQL/JSONパス式の構文
  SQL/JSONパス式は、パスの部分を選択するためにSQL/JSONファンクションおよび条件によって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つ目の要素)が繰り返されているためです。

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

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

  注意:

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

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

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

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

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

  • date(): 対象となるJSON文字列に対応するSQL DATE値。文字列データは、ISOの日付形式のいずれかである必要があります。

  • double(): 対象となるJSON文字列または数値に対応する、SQL BINARY_DOUBLEの数値。

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

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

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

  • number(): 対象となるJSON文字列または数値に対応する、SQL NUMBERの値。

  • string(): 対象となるJSON値の文字列表現。この表現は、SQL/JSONファンクションのRETURNING句で戻り型VARCHAR2で使用されるものと同じです。(ブール値は文字列"true"または"false"で、null値は文字列"null"で、数値は正準な形式で表されます。)文字列表現へのシリアライズ中に発生したエラーは無視されます。

  • timestamp(): 対象となるJSON文字列に対応するSQL TIMESTAMP値。文字列データは、ISOの日付形式のいずれかである必要があります。

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

  date()、length()、lower()、number()、string()、timestamp()およびupper()の項目メソッドは、SQL/JSON規格に対するOracleの拡張機能です。その他の項目メソッドは規格に含まれます。

• フィルタ式(フィルタと短縮される)は、疑問符(?)の後カッコ(())で囲まれたフィルタ条件を記述します。条件が満たされるとフィルタが満たされ、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スカラー値。

    比較述語は、==、!=、<、<=、>=または>です。

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