JSONデータ型のクエリ

OceanBaseデータベースでは、ドット記法とJSON関数を使用してJSONデータにアクセスできます。OceanBaseデータベースでは、JSONデータを保存する際には、基本的にJSON基本型を直接使用することを推奨します。

Dot Notationを使用したJSONデータへのアクセス

Dot Notation構文は本質的には、テーブルエイリアスに続いてJSON列名が記述され、さらにその後に1つ以上のフィールド名がピリオド（.）で区切られる形式です。

table_alias.json_col_name.json_field

#または

.json_field followed by array_step.(field_name)

Dot Notation構文をSQLのクエリパラメータとして直接使用する際の制約は以下の通りです：

• table_alias はテーブルのエイリアスでなければなりません。
• json_col_name は、IS JSON制約を持つか、それ自体がJSON型であるなど、有効なJSONデータである必要があります。
• array_step は単独では存在できず、json_field の後に続く必要があります。例：.json_field[1,2]。
• 各ノードの識別子の長さは128バイトを超えてはなりません。
• クエリの返却値のサイズは4KBを超えないようにしてください。超過した場合、返却値は NULL となります。

以下の例では、po はテーブル j.purchaseorder のエイリアス、po_doc はJSON列または（IS JSONキーワード制約を持つ VARCHAR2、BLOB、および CLOB 型）、PONumber はJSON DATAのフィールド名です。

SELECT po.po_doc.PONumber FROM j_purchaseorder po;

Dot Notation構文は関数のパラメータとしても使用でき、JSONパスでサポートされるメソッドを呼び出すこともできます。例：

SELECT SUBSTR(po.po_doc.PONumber.string(),2,2) FROM j_purchaseorder po;

JSONパスを使用したJSONデータへのアクセス

JSONファイルは階層構造を持つため、パス表現を使用して一部のJSONドキュメントを抽出したり、JSONドキュメントの一部の内容を変更したり、またはそのドキュメント内で操作する位置を指定する必要があります。 JSONパスは、JSON関数と条件を用いてJSONデータと照合し、0個以上のマッチまたはそれを満たすJSON値を選択します。JSONパスではワイルドカードや配列範囲を使用でき、マッチングは大文字と小文字を区別します。

JSONパス構文

JSONパスの基本的な構文は、コンテキスト記号（$）から始まり、その後に0個以上のオブジェクト、配列、および再帰ステップが続きます。各ノードの後にはフィルター式を付けることも、機能ノードを付けることもできます。 JSONパス（パス式とも呼ばれる）には、絶対パス式または相対パス式が含まれます。

• 絶対パス式は「$」で始まり、その後に0個以上のノードが続きます（例えば、$.filed_a.field_b の「$」、「field_a」、「field_b」はすべてノードです。）
• 相対パス式は「@」記号であり、絶対パス式と似ていますが、フィルター式では相対パス式のみを使用します。

基本的なパス式では、単一の機能ノード（Function Step）はオプションであり、通常はitem_methodの後に続きます。存在する場合、それはパス式の最後のステップです。 オブジェクトノード（Object Step）はピリオド（.）であり、その後にフィールドまたはワイルドカード（*）が続き、単一またはすべてのフィールドを表します。

配列ノード（Array Step）は左括弧（[）の後にアスタリスクワイルドカード（*）が続くものであり、すべての配列要素、または1つ以上の特定の配列インデックス、またはカンマ『,』で区切られた指定された範囲を表し、最後に右括弧（]）で終了します。

パス式において、配列インデックス（Array Indexing）は単一の配列位置を指定します。配列インデックスは整数リテラル（0, 1, 2,...）です。配列位置とインデックスは0から始まり、最初の配列要素のインデックスは0（位置0を指定）です。インデックスlastを使用して、任意のサイズの非空配列の最後の要素を参照できます。配列インデックスはlast - Nの形式でも使用できます。ここで、『-』はマイナス記号を表します。Nは「配列数 - 1」以下の整数リテラル（0, 1, 2,...）です。範囲は「N ～ M」であり、例えば「3 ～ 1」、「2 ～ 4」、「last-1 ～ last-2」となります。また、元のデータ["1", "2", "3", "4", "5", "6", "7", "8", "9"]において、[3 ～ 1, 2 ～ 4, last-1 ～ last-2, 0, 0]が選択されたオブジェクトである場合、結果は["2", "3", "4", "3", "4", "5", "7", "8", "1", "1"]となります。

再帰ノード（Descendant Step）は、連続する2つのピリオド（..）の形式であり、その後にフィールドが続きます。これは、前のノードとマッチするオブジェクトまたは配列に再帰的に下降し、収集したすべてのフィールド値を返します。例：

obclient> SELECT JSON_QUERY('{ "a" : { "b" : { "z" : 1 }, "c" : [ 5, { "z" : 2 } ], "z" : 3 }, "z" : 4 }', '$.a..z' WITH ARRAY WRAPPER) FROM DUAL;
+----------------------------------------------------------------------------------------+
| JSON_QUERY('{"A":{"B":{"Z":1},"C":[5,{"Z":2}],"Z":3},"Z":4}','$.A..Z'WITHARRAYWRAPPER) |
+----------------------------------------------------------------------------------------+
| [3,1,2]                                                                                |
+----------------------------------------------------------------------------------------+
1 row in set

フィルター式（Filter Expression、略称：Filter）は疑問符（?）であり、その後に丸括弧（(））で囲まれたフィルター条件が続きます。フィルター条件が満たされる場合、true返します。 フィルター条件（Filter Condition）は述語（ブール関数）をパラメータとして使用します。フィルター条件は以下の形式で使用できます。ここで、各cond、cond1、cond2はフィルター条件を表します。

• (cond): 括弧はグループ化に使用され、フィルター条件condを単一のユニットとして、その前または後の他のフィルター条件と区切ります。
• cond1 && cond2：andはcond1とcond2の両方の条件が満たされることを要求します。
• cond1 || cond2：cond1またはcond2のいずれか一方、または両方が満たされることを要求します。
• ! (cond)：condの否定、つまりcondが満たされないことを意味します。
• exists(その後に相対パス式が続く)：ターゲットデータが現在指定された条件を満たしていること。
• comparisonの形式は以下のとおりです：
  • 相対パス式の後に比較述語、次にJSONスカラー値またはJSON変数が続きます。
  • JSONスカラー値またはJSON変数の後に比較述語、次に相対パス式が続きます。
  • 1つのJSONスカラー値の後に比較述語が続き、その後に別のJSONスカラー値が続きます。
• 接続述語は&&、||、!をサポートします。比較述語は>、>=、<、<=、==、!=をサポートします。文字列の比較述語はhas substring、starts withをサポートします。その他の比較述語like、like_regex、eq_regexなどは現在サポートされていません。

JSONパスを使用してJSONデータにアクセスする関連する例は以下のとおりです。

$.fruits #オブジェクトのfruitsフィールド値

$.fruits[0]  #配列オブジェクトの最初の要素

$.fruits[0].name  #オブジェクトのnameフィールド値。このオブジェクトはfruits配列の最初の要素です

$.fruits[*].name  #各fruits配列オブジェクトのnameフィールド値

$.*[*].name  #このオブジェクトに含まれるすべての配列オブジェクトのnameフィールド値

$.fruits[3, 7 to 10, 12]  #fruits配列の4番目、8番目から11番目、そして13番目の要素。要素は昇順で指定する必要があります。

$.fruits[3].price  #オブジェクトのfruits配列の4番目の要素のpriceフィールド値

$.fruits[3].*  #オブジェクトのfruits配列の4番目の要素の値

$.fruits[3].price[0].year  #オブジェクトのfruits配列の4番目の要素のprice配列の1番目の要素のyearフィールド値。

$.fruits[3].price[0]?(@.year > 2016)  #オブジェクトのfruits配列の4番目の要素のprice配列の1番目の要素の値。以下の条件を満たす場合：price配列の1番目の要素のyearの値を数値に変換でき、かつ「> 2016」を満たす場合

$.fruits[3].price[0]?(@.year.number() > 2016)  #前のものと同じです。yearフィールド値タイプにnumber()メソッドが備わっている必要があります（文字列と配列はどちらも数値を返します。例えば、2017、「2017」など、このメソッドはどちらも2017を返します）、かつこのメソッドの戻り値が2016より大きい場合

$.fruits[3].price[0]?(@.year.numberOnly() > 2016)  #前のものと同じですが、yearフィールド値タイプのメソッドnumberOnly()のみを使用し、year値が文字列数値（例えば「2017」）である場合を除外します。

$.fruits[3]?(@.produce.city == "San Francisco")  #オブジェクトのfruits配列の4番目の要素。前提として、それにはproduceフィールドがあり、その値はオブジェクトであり、そのcityフィールドの値は文字列「San Francisco」であること。

JSONパスは、緩やかな構文もサポートしています。緩やかな構文と厳密な構文の違いは、配列の処理にあります。緩やかなモードでアクセスするJSONデータは、自動的に配列型のカプセル化が追加されるか、データのカプセル化が解除されます。

例えば、緩やかなモードの$.fruitsは、厳密なモードの$.fruitsまたは$[*].fruitsに相当します。緩やかなモードの$.fruits[0].nameは、厳密なモードの$.fruit[0].nameまたは$.fruit.name、$[*].fruits.name、$[*].fruits[0].nameに相当します。

JSONパスの関数メソッド（item_method）

JSONパスの関数メソッド（item_method）は、パス式の最後のステップとして、ターゲットのJSONデータを（他のものになる可能性がある）JSONデータに変換できます。ただし、パス式（item_methodの有無にかかわらず）を使用したクエリは、JSONデータをサポートしないSQLデータ型のデータを返すことがあります。現行バージョンでサポートされている関数メソッドは以下の表のとおりです。

JSONデータ型に加えて、JSON値の作成、クエリ、変更などの操作に使用できる一連のSQL関数もあります。詳細については、JSON関数を参照してください。