Update JSON data

OceanBase Database supports partial updates of JSON data. This feature allows you to update only the specific fields in a JSON document without having to update the entire document.

Limitations

JSON partial update

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

Here are some examples:

• Enable the JSON partial update feature.

  • At the session level:

  SET log_row_value_options="partial_json";
  

  • At the global level:

  SET GLOBAL log_row_value_options="partial_json";
  

• Disable the JSON partial update feature.

  • 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 result is as follows:

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

JSON expressions that can be used for partial updates

In addition to the log_row_value_options system variable, you must use specific JSON expressions to trigger a JSON partial update.

The following JSON expressions can be used for partial updates in OceanBase Database in MySQL mode:

• json_set or json_replace: used to update the value of a JSON field.
• json_remove: used to delete a JSON field.

Here are some examples:

1. Create a json_test table.

   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 result is as follows:

   Query OK, 1 row affected
   

3. Query the data of the j JSON column.

   SELECT j FROM json_test;
   

   The result is as follows:

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

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

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

   The result is as follows:

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

5. Query the modified data of the j JSON column.

   SELECT j FROM json_test;
   

   The 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 j JSON column.

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

   The result is as follows:

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

7. Query the modified data of the j JSON column.

   SELECT j FROM json_test;
   

   The result is as follows:

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

8. Use json_remove to delete the value of the name field in the j JSON column.

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

   The result is as follows:

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

9. Query the modified data of the j JSON column.

   SELECT j FROM json_test;
   

   The result is as follows:

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

Update granularity

In OceanBase Database, JSON data is stored in LOB storage. LOB data is stored in chunks. Therefore, the minimum amount of data that can be updated in a partial update is one LOB chunk. The smaller the LOB chunk, the smaller the amount of data written. You can set the size of a LOB chunk by using a DDL statement when you create a column.

Here is an example:

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

The size of a LOB chunk cannot be arbitrarily small. If the size is too small, it will affect the performance of SELECT, INSERT, and DELETE operations. We recommend that you set the chunk size based on the average size of the JSON document fields. If most fields are small, you can set the chunk size to 1 KB. OceanBase Database optimizes LOB read operations by storing data smaller than 4 KB in INROW storage. In this case, partial updates are not performed. Partial updates are mainly used to improve the performance of updating large documents. For small documents, full updates are more efficient.

Rebuild

Json Partial Update does not impose any restrictions on the data length before and after the update. If the length of the new value is less than or equal to the length of the old value, the original data at the specified position is directly replaced with the new data. If the length of the new value exceeds the length of the old value, the new data is appended to the end. OceanBase Database sets a threshold: if the length of the appended data exceeds 30% of the original data length, a rebuild is triggered. In this case, instead of performing a partial update, a full overwrite is executed.

You can use the JSON_STORAGE_SIZE expression to obtain the actual storage length of the JSON data and the JSON_STORAGE_FREE expression to estimate 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 result is as follows:

   Query OK, 1 row affected
   

4. Query the storage size of the JSON column (actual occupied storage space) using JSON_STORAGE_SIZE and estimate the storage space that can be released using JSON_STORAGE_FREE.

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

   The result is as follows:

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

   Since no partial update has been performed, the value of JSON_STORAGE_FREE is 0.

5. Use json_replace to update the value of the position field in the JSON column, where the length of the new value is less than the length of the old value.

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

   The result is as follows:

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

6. Query the storage size of the JSON column again using JSON_STORAGE_SIZE and estimate the storage space that can be released using JSON_STORAGE_FREE.

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

   The result is as follows:

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

   After the JSON column data is updated, since the new data is one byte shorter than the old data, the value of JSON_STORAGE_FREE is 1.

7. Use json_replace to update the value of the position field in the JSON column, where the length of the new value is greater than the length of the old value.

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

   The result is as follows:

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

8. Query the storage size of the JSON column again using JSON_STORAGE_SIZE and estimate the storage space that can be released using JSON_STORAGE_FREE.

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

   The result is as follows:

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

   After the JSON column data is appended, the value of JSON_STORAGE_FREE is 19, indicating that 19 bytes can be released after the rebuild.