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

15 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パス式のフィルタ条件における比較では、コンパイル時に静的に型が指定されます。比較のオペランドの有効な型が同じであると認識されない場合、型キャストでの調整が行われることがあります。

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

15.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の場合のように値のシーケンスを戻す動作とみなすことも、複数の値を戻す動作とみなすこともできます。(ユーザーから見えるシーケンスが現れることはありません。)

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

関連項目

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

15.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パス式

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

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

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

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

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

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

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

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

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

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

    項目メソッドは、同じパス式内の残りの部分、かつファンクション・ステップに先行する部分が対象とするデータに適用されます。項目メソッドは、目的のデータを変換するために使用されます。引数としてパス式に渡されたSQLファンクションまたは条件は、対象データのかわりとして変換済データを使用します。

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

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

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

    パス式では、配列に対するJavaScript変換の場合のように、配列索引付けは0を基準として行われます(0、1、2、...)。範囲指定の形式はNからMです。NとMは配列索引であり、Nは厳密にM未満となります。脚注1NがM未満ではない場合は、問合せのコンパイル時にエラーが発生します。

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

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

  • 子孫ステップでは、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が、フィルタ条件を表します。

    • ( 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スカラー値。

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

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

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

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

          ノート:

          SQL LIKEの場合とは異なり、パス式の述語likeにはエスケープ文字がありません。また、likeパターンで文字`、つまりグレーブ・アクセント記号(U+0060)を使用しないことをお薦めします。バッククォートやバックティックとも呼ばれるこの文字は、将来使用するために予約されています。

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

          like_regexはパターン一致比較の例外です。パターンが空のJSON文字列("")と一致するためです。

        • eq_regexはlike_regexと同様ですが、次の2つの違いがあります。

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

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

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

      • 相対パス式の後にinが続き、その後に値リストが続きます。つまり、値が値リストにある値のいずれかになります。

      比較述語は==、<>、!=<a id="fn_2" name="fn_2" href="#fn_2" onclick='footdisplay(2, "!= is an Oracle alias for the SQL/JSON standard comparison predicate <>.")'>脚注2、<、<=、>=または>です(それぞれ等しい、等しくない、未満、以下、以上、より大きい)。

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

    • 値リストは、(の後に、1つ以上のスカラー値およびSQL/JSON変数をカンマ(,)で区切ったリストが続き、その後に)が続く形式です。

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

    例を示します。次の基準の両方が満たされると、フィルタ条件(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が満たされる。

比較の少なくとも一方をSQL/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検索索引を作成する必要があります。あまり強力ではない代替方法として、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番目の順です。

  • $.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の郵便番号。(この場合、フィルタは最後のパス・ステップではありません。)

  • $..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句のSQLバインド変数CITYおよびSTATEによって指定されます。比較に変数を使用すると、問合せの再コンパイルが回避されることでパフォーマンスが向上します。

関連項目

関連項目:

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

15.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パス式の構文

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

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

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

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

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

項目メソッドsize()およびtype()を除き、項目メソッドが配列を対象とする場合、このメソッドは配列自体ではなく各配列要素に適用されます。たとえば、$.a.method()は、項目メソッドmethod()を配列aの各項目に適用して変換し、配列のかわりに使用します。(このことは、実質的に、オブジェクト・ステップに対して非配列が想定される場合の配列の暗黙のアンラップと同様です。)一致の結果セットには、対象の配列ではなく、変換された配列要素が含まれます。

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

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

配列["alpha", "10.4"]が返されます(VARCHAR2値として)。

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

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

単一のVARCHAR2値'array'が返されます。

項目メソッドの説明

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

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

一部の項目メソッドは、対象の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の両方と互換性があります。

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

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

項目メソッド 互換性のあるRETURNING句のデータ型
string()またはstringOnly() VARCHAR2またはCLOB (string()がJSON null値に対してSQL NULLを返すことを除く)
number()またはnumberOnly() NUMBER
date() DATE
timestamp() TIMESTAMP
boolean()またはbooleanOnly() VARCHAR2

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

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

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

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

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

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

  • size(): 配列内の要素数。あるいはスカラーまたはオブジェクトの場合は1。この項目メソッドは、json_query、json_valueおよびjson_tableとともに使用できます。配列のデータに適用される場合、配列要素に対する暗黙的な反復は発生しません。結果の値は配列要素の数に一致します。(暗黙的反復のルールに対する例外。)

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

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

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

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

    • 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()は標準に含まれています。

関連項目

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

15.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 (年がすべて指定されていません)

関連項目:

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

15.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にキャストされ、比較が実行されます。脚注4

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

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

  • 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タイムゾーン(協定世界時、ゼロのオフセット)はデフォルトとして使用され、文字列で指定された任意のタイムゾーンを考慮します。

関連項目

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


脚注の凡例

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