JSONデータ型のクエリ

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

ドット記法によるJSONデータへのアクセス

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

table_alias.json_col_name. json_field

#または

.json_field followed by array_step.(field_name)

SQLにおいてドット記法を直接クエリパラメータとして使用する場合の制約事項は以下の通りです：

• table_alias はテーブルのエイリアスでなければなりません。
• json_col_name は有効なJSONデータである必要があります（IS 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;

ドット記法の構文は関数のパラメータとしても使用でき、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配列の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 Pathはリラックス構文もサポートしています。リラックス構文と厳密構文の違いは、配列の処理方法にあります。リラックスモードでは、アクセスされるJSONデータは自動的に配列型のラッピングが追加されたり、ラッピングが外されたりします。

例えば、リラックスモードの $.fruits は厳密モードの $.fruits または $[*].fruits に相当します。リラックスモードの $.fruits[0].name は厳密モードの $.fruit[0].name または $.fruit.name、$[*].fruits.name、$[*].fruits[0].name に相当します。

JSON Pathの関数メソッド（item_method）

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

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