MySQL JSON_STORAGE_FREE

The MySQL JSON_STORAGE_FREE function returns the amount of storage space freed after a JSON column has been updated by JSON_SET, JSON_REPLACE, or JSON_REMOVE. This function is useful for optimizing storage usage when performing partial updates on JSON columns.

Syntax

JSON_STORAGE_FREE(json_val)

Where:

json_val is the JSON value or JSON string that represents the updated JSON column.

The function returns the number of bytes of storage space that were freed by the update.

Example

UPDATE customers
SET address = JSON_SET(address, '$.city', 'New York')
WHERE id = 1;

SELECT JSON_STORAGE_FREE(address) FROM customers WHERE id = 1;

In this example, the JSON_STORAGE_FREE() function is used to calculate the amount of storage space freed after the address JSON column is updated. The update replaces the city value with New York. The function returns 2 bytes, which is the amount of space saved by removing the old city value.

Use cases

Optimize storage usage when performing partial updates on JSON columns.
Monitor the storage usage of JSON columns.
Calculate the amount of storage space reclaimed by a database cleanup operation.

The JSON_STORAGE_FREE function is a valuable tool for tracking and managing the storage consumption of JSON data in MySQL. By using this function, you can optimize your database performance, reduce storage costs, and improve the overall efficiency of your applications.