JSONの一部データの更新

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

制限事項

JSONパーシャル更新スイッチ

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

例：

• JSONパーシャル更新機能を有効にします。

  • セッションレベル：

  SET log_row_value_options="partial_json";
  

  • グローバルレベル：

  SET GLOBAL log_row_value_options="partial_json";
  

• JSONパーシャル更新機能を無効にします。

  • セッションレベル：

  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機能を有効にするためには、JSON Partial Update機能スイッチlog_row_value_optionsに加えて、特定の式を使用してJSONドキュメントを更新する必要があります。

現在、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は主に大規模なドキュメントの更新パフォーマンスを向上させるためのものであり、小規模なドキュメントの場合は完全更新の方がパフォーマンスが良い場合があります。

リビルド

JSON部分更新は、JSON列の更新前後のデータ長に制限を設けません。新しい値の長さが古い値の長さ以下の場合、元の位置のデータを新しいデータに直接置き換えます。新しい値の長さが古い値の長さを超える場合、最後に新しいデータを追加します。OceanBaseデータベースではしきい値が設定されており、追加されたデータの長さが元のデータ長の30％を超えるとリビルドが実行されます。この場合、部分更新は行われず、完全な上書き書き込みが行われます。

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

例：

1. JSON部分更新を有効にします。

   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
   

   部分更新が行われていないため、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列データの追加更新後、JSON_STORAGE_FREE の長さは19であり、リビルド後に19バイトを解放できることを示します。