JSON値のクエリと変更

OceanBaseデータベースは、JSON値のクエリと参照をサポートしており、パス表現を使用して一部のJSONドキュメントを抽出したり、JSONドキュメントの一部の内容を変更したりすることができます。

JSON値のインポート

OceanBaseデータベースは、以下の2つの方法でJSON値を照会およびインポートすることをサポートしています：

• 「-\>」記号を使用して、JSONデータ内のダブルクォーテーションマーク付きキー値をインポートします。

• 「-\>\>」記号を使用して、JSONデータ内のシングル引用符付きキー値をインポートします。

例：

obclient> SELECT c->"$.name" AS name FROM jn WHERE g <= 2;
+---------+
| name    |
+---------+
| "Fred"  |
| "Wilma" |
+---------+
2 rows in set

obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

obclient> SELECT JSON_UNQUOTE(c->'$.name') AS name
 FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

JSONファイルには階層があるため、JSON関数はパス式を使用して一部のJSONドキュメントを抽出したり、JSONドキュメントの一部の内容を変更したりする必要があります。また、ドキュメント内での操作位置を指定することもできます。JSON関数の詳細については、JSON関数を参照してください。

OceanBaseデータベースでは、「先頭の$文字＋記号セレクタ」というパス構文を使用してアクセスしているJSONドキュメントを表します。記号セレクタの種類は以下のとおりです：

• 「.」記号は、アクセスするキー名を表します。パス式では、引用符なしの名前（例えばスペース）は無効であるため、キー名は必ずダブルクォーテーションマークで指定する必要があります。

  例：

  obclient> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
  +---------------------------------------------------------+
  | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
  +---------------------------------------------------------+
  | "Aztalan"                                               |
  +---------------------------------------------------------+
  1 row in set
  

• 「\[N\]」記号は、選択された配列のパスの後ろに配置され、配列の位置Nの値にアクセスすることを表します。ここで、Nは非負の整数です。配列の位置は0から始まる整数です。pathが配列値を選択していない場合、path[0]はpathと同じ計算値を持ちます。

  例：

  obclient> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set
  

• 「\[M to N\]」記号は、配列値の部分集合または範囲を指定するために使用されます。つまり、位置Mの値から始まり、位置Nの値で終了します。

  例：

  obclient> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
  +----------------------------------------------+
  | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
  +----------------------------------------------+
  | [2, 3, 4]                                    |
  +----------------------------------------------+
  1 row in set
  

• パス式には、*または\**ワイルドカードを含むこともできます。説明は以下のとおりです：

  • .[*]は、JSONオブジェクト内のすべてのメンバーの値を表します。

  • [*]は、JSON配列内のすべての要素の値を計算することを表します。

  • prefix**suffixは、命名前接辞で始まり、命名後接辞で終わるすべてのパスを表します。前接辞部分は必須ではありませんが、後接辞部分は必須です。「\*」または「\**」を使用して任意のパスを記述することは許可されていません。

  説明

  ドキュメントに存在しないパス（存在しないデータとして計算される）は、NULLとして計算されます。

JSON値の変更

OceanBaseデータベースは、DMLステートメントを使用して完全なJSON値を変更することも、UPDATE ステートメントでJSON_SET()、JSON_REPLACE()、またはJSON_REMOVE()関数を使用して一部のJSON値を操作することもサポートしています。

例：

// 全てのデータを挿入
INSERT INTO json_tab(json_info) VALUES ('[1, {"a": "b"}, [2, "qwe"]]);

// 一部のデータを挿入
UPDATE json_tab SET json_info=JSON_ARRAY_APPEND(json_info, '$', 2) WHERE id=1;

// 全てのデータを更新
UPDATE json_tab SET json_info='[1, {"a": "b"}]';

// 一部のデータを更新
UPDATE json_tab SET json_info=JSON_REPLACE(json_info, '$[2]', 'aaa') WHERE id=1;

// 一部のデータを削除
DELETE FROM json_tab WHERE id=1;

// 関数を使用して一部のデータを更新
UPDATE json_tab SET json_info=JSON_REMOVE(json_info, '$[2]') WHERE id=1;

JSONパス構文

パスは、パス範囲と1つ以上のパス分岐から構成されます。JSON関数で使用されるパスについては、範囲は現在検索中または他の方法で操作されているドキュメントであり、先頭の$文字で表されます。

パスセグメントは、ピリオド（.）文字で区切られます。配列内の要素は [N] で表され、ここでNは非負の整数です。キー名は、ダブルクォーテーションマークで囲まれた文字列または有効なECMAScript識別子でなければなりません。

パス式（例えばJSONテキスト）は、ascii、utf8、またはutf8mb4文字セットを使用してエンコードする必要があります（他の文字エンコーディングは暗黙的にutf8mb4に強制されます）。

完全な構文は以下のとおりです：

pathExpression: //パス式
    scope[(pathLeg)*]   //範囲は先頭文字 $ で記述されます

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'