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:
    ']'