Update of a part of a JSON document

seekdb supports the update of a part of a JSON document (JSON partial update). This feature allows you to update only the modified fields in a JSON document without updating the entire document.

Limitations

Enable the JSON partial update feature

By default, the JSON partial update feature is disabled in seekdb. 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 returned result is as follows:

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

Supported JSON expressions for partial updates

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

The following JSON expressions can be used for partial updates in seekdb:

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

tip

1. Make sure that the left operand of the SET clause and the first parameter of the JSON expression are the same and are JSON columns in the table. For example, in j = json_replace(j, '$.name', 'ab'), the left operand of the equals sign and the first parameter of the json_replace function are both j.
2. The JSON partial update feature is triggered only when the JSON column is stored in outrow format. The storage format (outrow or inrow) is determined by the lob_inrow_threshold parameter specified when the table is created. The lob_inrow_threshold parameter specifies the INROW threshold. If the size of LOB data exceeds this threshold, the data is stored in outrow format in the LOB meta table. The default value is 4 KB.

Here are some examples:

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

   Query OK, 1 row affected (0.002 sec)

3. Query the data in the j column.

   SELECT j FROM json_test;

   The returned result is as follows:

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

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

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

   The returned result is as follows:

   Query OK, 1 row affected (0.003 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

5. Query the modified data in the j column.

   SELECT j FROM json_test;

   The returned result is as follows:

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

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

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

   The returned result is as follows:

   Query OK, 1 row affected (0.003 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

7. Query the modified data in the j column.

   SELECT j FROM json_test;

   The returned result is as follows:

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

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

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

   The returned result is as follows:

   Query OK, 1 row affected (0.003 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

9. Query the modified data in the j column.

   SELECT j FROM json_test;

   The returned result is as follows:

   +-------------------------+
   | j                       |
   +-------------------------+
   | {"content": "xxxxxxxx"} |
   +-------------------------+
   1 row in set (0.000 sec)

Update granularity

In seekdb, JSON data is stored in LOB format. LOB data is stored in blocks. Therefore, the minimum amount of data updated in a partial update is one LOB block. The smaller the LOB block, the smaller the amount of data written. You can specify the LOB block size when you create a column.

Here is an example:

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

The LOB block size cannot be too small. If it is too small, it will affect the performance of SELECT, INSERT, and DELETE operations. Generally, the LOB block size should be set based on the average size of JSON fields. If most fields are small, you can set the LOB block size to 1 KB. For data smaller than 4 KB, seekdb stores it in INROW format to optimize LOB read performance. In this case, partial updates are not performed. Partial updates are mainly used to improve the performance of large document updates. For small documents, full updates are more efficient.

Rebuild

Json Partial Update does not impose any restrictions on the length of data before and after updates in a JSON column. If the length of the new value is less than or equal to the length of the old value, the new data directly replaces the original data at the specified position. If the length of the new value exceeds the length of the old value, the new data is appended to the end. seekdb sets a threshold: when 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 return result is as follows:

   Query OK, 1 row affected (0.003 sec)

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 return 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 JSON_STORAGE_SIZE value is 4335 and the JSON_STORAGE_FREE value 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 return result is as follows:

   Query OK, 1 row affected (0.003 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

6. Query the storage size of the JSON column 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 return result is as follows:

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4334 |                    0 |
   +----------------------+----------------------+
   1 row in set (0.000 sec)

   After the JSON column data is updated, since the new data is one byte shorter than the old data, the JSON_STORAGE_SIZE value is 4334 and the JSON_STORAGE_FREE value remains 0.

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

   Query OK, 0 rows affected (0.001 sec)
   Rows matched: 1  Changed: 0  Warnings: 0

8. Query the storage size of the JSON column 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 return result is as follows:

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

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