OceanBase AI Database supports updating only a partial portion of a JSON document. When you need to modify only specific fields within a JSON document, this feature allows you to update only the changed parts without having to fully refresh the entire JSON document.
Limitations
- The current version does not support partial update of JSON data. Even if the
partial_jsonoption is enabled in thelog_row_value_optionsvariable, a full update is performed instead of a partial update when updating a JSON document.
Json Partial Update Switch
The JSON partial update feature of OceanBase AI Database is disabled by default. The switch is controlled by the system variable log_row_value_options. For more information, see log_row_value_options.
Example:
Enable the JSON partial update feature.
- Session level:
SET log_row_value_options="partial_json";- Global level:
SET GLOBAL log_row_value_options="partial_json";Disable the JSON partial update feature.
- Session level:
SET log_row_value_options="";- 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 that can be used for partial update
In addition to the log_row_value_options parameter for enabling JSON partial update, you must also use specific expressions to update the JSON document for this feature to take effect.
The following table describes the JSON expressions that support partial update in OceanBase AI Database:
- json_set or json_replace: Updates the value of a JSON field.
- json_remove: Deletes a JSON field.
Notice
- Ensure that the left operand of the
SETassignment clause and the first parameter of the JSON expression are the same and both refer to a JSON column in the table. For example, inj = json_replace(j, '$.name', 'ab'), both the parameter on the left side of the equal sign and the first parameter of the JSON expressionjson_replaceon the right side arej. - JSON partial update is triggered only when the data of the current JSON column is stored as
outrow. Whether to store asoutroworinrowis controlled by thelob_inrow_thresholdparameter during table creation.lob_inrow_thresholdconfigures theINROWthreshold. When the LOB data size exceeds this threshold, it will be converted toOUTROWstorage in the LOB Meta table. The default value is 4 KB.
Example:
Create the
json_testtable.CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON);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 affectedQuery the data in column
jof the JSON type.SELECT j FROM json_test;The return result is as follows:
+-----------------------------------------+ | j | +-----------------------------------------+ | {"name": "John", "content": "xxxxxxxx"} | +-----------------------------------------+ 1 row in setUse
json_replaceto update the value of thenamefield 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: 0Query the modified data in the
jcolumn as a JSON string.SELECT j FROM json_test;The return result is as follows:
+---------------------------------------+ | j | +---------------------------------------+ | {"name": "ab", "content": "xxxxxxxx"} | +---------------------------------------+ 1 row in setUse
json_setto update the value of thenamefield 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: 0Query the data in column
jafter modification.SELECT j FROM json_test;The return result is as follows:
+---------------------------------------+ | j | +---------------------------------------+ | {"name": "cd", "content": "xxxxxxxx"} | +---------------------------------------+ 1 row in setUse
json_removeto remove the value of thenamefield 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: 0Query the data in column
jafter modification.SELECT j FROM json_test;The return result is as follows:
+-------------------------+ | j | +-------------------------+ | {"content": "xxxxxxxx"} | +-------------------------+ 1 row in set
Update granularity
The JSON data in OceanBase AI Database is stored as LOB data, and the underlying storage of LOB data in OceanBase AI Database is block-based storage. Therefore, the minimum amount of data updated in each partial update is one LOB block. The smaller the LOB block size, the less data is written. For this reason, a DDL statement for setting the LOB block size is also provided, which can be specified when creating a column.
Example:
CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '4k');
The chunk size cannot be set too small, as this will impact the performance of SELECT, INSERT, and DELETE operations. It is generally recommended to set it based on the average field size in the JSON document. If most fields are small, you can set it to 1 KB. To optimize read performance for LOB-type data, OceanBase AI Database stores data smaller than 4 KB directly in INROW format, which prevents partial updates from being performed in such cases. Partial updates are primarily intended to improve the performance of updating large documents; for smaller documents, a full update actually yields better performance.
Rebuild
JSON partial update does not impose any restrictions on the length of data before and after an update for a JSON column. If the length of the new value is less than or equal to the length of the old value, the original data at that position is directly replaced with the new data. When the length of the new value is greater than the length of the old value, the new data is appended at the end. OceanBase AI Database sets a threshold. When the length of the appended data exceeds 30% of the original data length, a rebuild is performed. In this case, a partial update is not performed; instead, the data is overwritten in its entirety.
You can use the JSON_STORAGE_SIZE expression to obtain the actual storage length of JSON data and the JSON_STORAGE_FREE expression to obtain the additional storage overhead.
Example:
Enable JSON partial update.
SET log_row_value_options = "partial_json";Create a test table named
json_test.CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '1K');Insert a row of data into the
json_testtable.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 affectedQuery the storage size of a JSON column (the actual occupied storage space) using
JSON_STORAGE_SIZE, and estimate the storage space that can be released for the JSON column usingJSON_STORAGE_FREE.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 setSince no partial update has been performed, the value of
JSON_STORAGE_FREEis 0.Use
json_replaceto update the value of thepositionfield 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 return result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0Query the JSON column storage size again using
JSON_STORAGE_SIZE, and estimate the storage space that can be released for the JSON column usingJSON_STORAGE_FREE.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 setAfter the JSON column data is updated, since the new data is one byte shorter than the old data, the result of
JSON_STORAGE_FREEis 1.Use
json_replaceto update the value of thepositionfield in a JSON column, where the new value is longer than the old value.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: 0Query the storage size of the JSON column again using
JSON_STORAGE_SIZE, and estimate the amount of storage space that can be released for the JSON column usingJSON_STORAGE_FREE.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 setAfter appending data to the JSON column, the
JSON_STORAGE_FREElength is 19, indicating that 19 bytes can be released after rebuilding.