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 は有効なJSONデータでなければなりません（IS JSON 制約を持つか、元々JSON型である必要があります）。
• array_step は単独では存在できず、json_field の後に続く必要があります。例：.json_field[1,2]。
• 各ノードの識別子の長さは128バイトを超えてはなりません。
• クエリの戻り値のサイズは4kを超えてはなりません。超えた場合、戻り値は 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 Pathでサポートされているメソッドを呼び出すこともできます。例：

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 to M」となり、例えば「3 to 1」、「2 to 4」、「last-1 to last-2」となります。例えば、元のデータが["1", "2", "3", "4", "5", "6", "7", "8", "9"]の場合、[3 to 1, 2 to 4, last-1 to 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  # fruits配列の最初の要素であるオブジェクトのnameフィールドの値

$.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配列の最初の要素のyearフィールドの値。

$.fruits[3].price[0]?(@.year > 2016)  # オブジェクトのfruits配列の4番目の要素のprice配列の最初の要素の値が、以下の条件を満たす場合：price配列の最初の要素の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データを（他の形式に）変換することができます。しかし、パス式（item_methodの有無にかかわらず）を使用したクエリは、JSONデータをサポートしないSQLデータ型のデータを返すことがあります。現在のバージョンでサポートされている関数メソッドは以下の表のとおりです。

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