Update partial JSON data

OceanBase Database supports JSON partial update. You can use this feature to modify specific fields in a JSON document without updating the entire JSON document.

Limitations

Variable for enabling and disabling JSON partial update

The JSON partial update feature is disabled by default in OceanBase Database. You can set the system variable log_row_value_options to enable and disable the feature. For more information, see log_row_value_options.

Here are some examples:

• Enable JSON partial update.

  • At the session level:

  SET log_row_value_options="partial_json";
  

  • At the global level:

  SET GLOBAL log_row_value_options="partial_json";
  

• Disable JSON partial update.

  • At the session level:

  SET log_row_value_options="";
  

  • At the global level:

  SET GLOBAL log_row_value_options="";
  

• Query the value of log_row_value_options.

  SHOW VARIABLES LIKE 'log_row_value_options';
  

  The return result is as follows:

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

JSON expressions for triggering partial update

After you enable JSON partial update by setting log_row_value_options, you need to update a JSON document with a specific expression to trigger JSON partial update.

The JSON expressions for triggering partial update in OceanBase Database in MySQL mode are as follows:

• json_set or json_replace: updates the value of a JSON field.
• json_remove: removes a JSON field.

Here is an example:

1. Create a table named json_test.

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

2. Insert data.

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

   The return result is as follows:

   Query OK, 1 row affected
   

3. Query data in the JSON column j.

   SELECT j FROM json_test;
   

   The return result is as follows:

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

4. Use json_replace to update the value of the name field in the JSON column.

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

   The return result is as follows:

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

5. Query the new data in the JSON column j.

   SELECT j FROM json_test;
   

   The return result is as follows:

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

6. Use json_set to update the value of the name field in the JSON column.

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

   The return result is as follows:

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

7. Query the new data in the JSON column j.

   SELECT j FROM json_test;
   

   The return result is as follows:

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

8. Use json_remove to remove the value of the name field from the JSON column.

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

   The return result is as follows:

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

9. Query the new data in the JSON column j.

   SELECT j FROM json_test;
   

   The return result is as follows:

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

Update granularity

OceanBase Database stores JSON data as LOB data, which is stored in chunks at the bottom layer. Therefore, the minimum data amount of each partial update is one LOB chunk. The smaller the LOB chunk is, the smaller amount of data will be written each time. For this purpose, OceanBase Database provides the DDL syntax for setting the LOB chunk size. You can use the syntax when you create a column.

Here is an example:

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

The chunk size cannot be infinitely small. If it is too small, the performance of SELECT, INSERT and DELETE operations is affected. Generally, you can set the chunk size based on the average field size of a JSON document. If most fields are small, you can set it to 1K. To optimize the reading performance of LOB data, OceanBase Database stores data within 4 KB in INROW mode. For such data, JSON partial update will not be triggered. The main purpose of JSON partial update is to improve the update performance for large documents. For small documents, a full update usually offers better performance.

Rebuild

JSON partial update has no limits on the data length of a JSON column before and after the update. If the data length of the new value is smaller than or equal to the length of the old value, the data in the original position is directly replaced with the new data. If the data length of the new value is greater than the length of the old value, the new data is appended to the end. When the length of the appended data exceeds 30% of the original data length, OceanBase Database will rebuild the data. At this time, JSON partial update will not be performed, and full data will be overwritten.

You can use the JSON_STORAEG_SIZE expression to get the actual storage length of a JSON column, and use JSON_STORAGE_FREE to obtain the additional storage overhead.

Here is an example:

1. Enable JSON partial update.

   SET log_row_value_options = "partial_json";
   

2. Create a test table named json_test.

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

3. Insert a row of data into the json_test table.

   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'));
   

   The return result is as follows:

   Query OK, 1 row affected
   

4. Use JSON_STORAGE_SIZE to query the storage size, that is, the actual storage space, of the JSON column. Use JSON_STORAGE_FREE to estimate the storage space that the JSON column can release.

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

   The return result is as follows:

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

   The value of JSON_STORAGE_FREE is 0, because partial update is not performed.

5. Use json_replace to update the position field in the JSON column with a value shorter than the old one.

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

   The return result is as follows:

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

6. Use JSON_STORAGE_SIZE to query the storage size of the JSON column again. Use JSON_STORAGE_FREE to estimate the storage space that the JSON column can release.

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

   The return result is as follows:

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

   After the data in the JSON column is updated, because the new value is one byte less than the old value, the result of JSON_STORAGE_FREE is 1.

7. Use json_replace to update the position field in the JSON column with a value longer than the old one.

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

   The return result is as follows:

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

8. Use JSON_STORAGE_SIZE to query the storage size of the JSON column again. Use JSON_STORAGE_FREE to estimate the storage space that the JSON column can release.

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

   The return result is as follows:

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

   After the data in the JSON column is updated in append mode, the result of JSON_STORAGE_FREE is 19, indicating that 19 bytes can be released after the rebuild operation.