JSONデータの部分更新

OceanBaseデータベースは、JSONデータの部分更新（JSONパーシャルアップデート）をサポートしています。JSONドキュメント内の特定のフィールドのみを変更する場合、この機能により、JSONドキュメント全体を完全に更新することなく、変更したい部分のみを更新できます。

使用上の制限

Json Partial Updateスイッチ

OceanBaseデータベースのJson Partial Update機能は、デフォルトで無効になっています。このスイッチはシステム変数log_row_value_optionsによって制御されます。詳細については、log_row_value_optionsを参照してください。

例：

• Json Partial Update機能を有効にします。

  • セッションレベル：

  SET log_row_value_options="partial_json";
  

  • グローバルレベル：

  SET GLOBAL log_row_value_options="partial_json";
  

• Json Partial Update機能を無効にします。

  • セッションレベル：

  SET log_row_value_options="";
  

  • グローバルレベル：

  SET GLOBAL log_row_value_options="";
  

• log_row_value_optionsの値を照会します。

  SHOW VARIABLES LIKE 'log_row_value_options';
  

  実行結果は次のとおりです：

  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | log_row_value_options |       |
  +-----------------------+-------+
  1 row in set
  

Partial Updateで使用可能なJson式

Json Partial Update機能のスイッチlog_row_value_optionsに加えて、特定の式を使用してJsonドキュメントを更新する必要があります。これにより、Json Partial Updateがトリガーされます。

現在、OceanBaseデータベースのMySQLモードでは、Partial Updateが可能なJson式は以下のとおりです：

• json_setまたはjson_replace：Jsonフィールドの値を更新するために使用します。
• json_remove：Jsonフィールドを削除するために使用します。

例：

1. テーブル json_test を作成します。

   CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON);
   

2. データを挿入します。

   INSERT INTO json_test VALUES(1, CONCAT('{"name": "John", "content": "', repeat('x',8), '"}'));
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   

3. Json列 j のデータをクエリします。

   SELECT j FROM json_test;
   

   実行結果は次のとおりです：

   +-----------------------------------------+
   | j                                       |
   +-----------------------------------------+
   | {"name": "John", "content": "xxxxxxxx"} |
   +-----------------------------------------+
   1 row in set
   

4. json_repalce を使用して、Json列 name フィールドの値を更新します。

   UPDATE json_test SET j = json_replace(j, '$.name', 'ab') WHERE pk = 1;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

5. 修正後のJson列 j のデータをクエリします。

   SELECT j FROM json_test;
   

   実行結果は次のとおりです：

   +---------------------------------------+
   | j                                     |
   +---------------------------------------+
   | {"name": "ab", "content": "xxxxxxxx"} |
   +---------------------------------------+
   1 row in set
   

6. json_set を使用して、Json列 name フィールドの値を更新します。

   UPDATE json_test SET j = json_set(j, '$.name', 'cd') WHERE pk = 1;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

7. 修正後のJson列 j のデータをクエリします。

   SELECT j FROM json_test;
   

   実行結果は次のとおりです：

   +---------------------------------------+
   | j                                     |
   +---------------------------------------+
   | {"name": "cd", "content": "xxxxxxxx"} |
   +---------------------------------------+
   1 row in set
   

8. json_remove を使用して、Json列 name のフィールド値を削除します。

   UPDATE json_test SET j = json_remove(j, '$.name') WHERE pk = 1;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

9. Json列 j のデータを変更した後の結果を照会します。

   SELECT j FROM json_test;
   

   実行結果は次のとおりです：

   +-------------------------+
   | j                       |
   +-------------------------+
   | {"content": "xxxxxxxx"} |
   +-------------------------+
   1 row in set
   

更新粒度

OceanBaseデータベースのJsonデータはLob形式で保存され、そのLobは基盤としてブロック単位で格納されます。そのため、Partial Updateを実行する際のデータ量の最小単位は1つのLobブロックとなります。Lobブロックが小さいほど、書き込むデータ量も少なくなります。このため、列作成時にLobブロックサイズを指定できるDDL構文も提供されています。

例：

CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '4k');

Chunk Sizeは無限に小さくすることはできません。小さすぎるとSELECT、INSERT、DELETEのパフォーマンスに影響します。一般的には、Jsonドキュメントの平均フィールドサイズに応じて設定することを推奨します。大部分のフィールドが小さい場合は、1Kに設定できます。OceanBaseデータベースはLob型の読み取りを最適化するため、4K未満のデータについては直接INROWで保存し、この場合はPartial Updateは行われません。Partial Updateは主に大規模なドキュメントの更新パフォーマンスを向上させるためのものであり、小規模なドキュメントでは全量更新の方がパフォーマンスが良い場合があります。

Rebuild

Json Partial Updateは、Json列の更新前後のデータ長に制限を設けません。新しい値の長さが古い値の長さ以下の場合、元の場所のデータを新しいデータで直接置き換えます。新しい値の長さが古い値の長さを超える場合、最後に新しいデータを追加します。OceanBaseデータベースではしきい値が設定されており、追加されるデータの長さが元のデータ長の30％を超えるとRebuildが実行されます。この場合、Partial Updateは行われず、データは完全に上書きされます。

JSON_STORAGE_SIZE 式を使用してJSONの実際のストレージ長を取得し、JSON_STORAGE_FREE を使用して追加のストレージオーバーヘッドを取得できます。

例：

1. Json Partial Updateを有効にします。

   SET log_row_value_options = "partial_json";
   

2. テストテーブル json_test を作成します。

   CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '1K');
   

3. テーブル json_test に1行のデータを挿入します。

   INSERT INTO json_test VALUES(10 , json_object('name', 'zero', 'age', 100, 'position', 'software engineer', 'profile', repeat('x', 4096), 'like', json_array('a', 'b', 'c'), 'tags', json_array('sql boy', 'football', 'summer', 1), 'money' , json_object('RMB', 10000, 'Dollers', 20000, 'BTC', 100), 'nickname', 'noone'));
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   

4. JSON_STORAGE_SIZE を使用してJson列のストレージサイズ（実際に使用されているストレージ容量）を確認し、JSON_STORAGE_FREE を使用してJson列から解放できるストレージ容量を推定します。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   実行結果は次のとおりです：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4335 |                    0 |
   +----------------------+----------------------+
   1 row in set
   

   Partial Updateが行われていないため、JSON_STORAGE_FREE の値は0です。

5. json_replace を使用してJson列 position フィールドの値を更新します。新しい値の長さは古い値の長さより短いです。

   UPDATE json_test SET j = json_replace(j, '$.position', 'software enginee') WHERE pk = 10;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

6. 再度 JSON_STORAGE_SIZE を使用してJson列のストレージサイズを確認し、JSON_STORAGE_FREE を使用してJson列から解放できるストレージ容量を推定します。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   実行結果は次のとおりです：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4335 |                    1 |
   +----------------------+----------------------+
   1 row in set
   

   Json列データを更新した後、新しいデータは古いデータよりも1バイト短くなるため、JSON_STORAGE_FREE の結果は1となります。

7. json_replace を使用してJson列の position フィールドの値を更新します。新しい値の長さは古い値の長さよりも長いです。

   UPDATE json_test SET j = json_replace(j, '$.position', 'software engineera') WHERE pk = 10;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

8. 再度 JSON_STORAGE_SIZE を使用してJson列のストレージサイズを確認し、JSON_STORAGE_FREE を使用してJson列で解放可能なストレージ容量を推定します。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   実行結果は次のとおりです：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4355 |                   19 |
   +----------------------+----------------------+
   1 row in set
   

   Json列データをAppend更新した後、JSON_STORAGE_FREE の長さは19となり、これはRebuild後に19バイトを解放できることを意味します。