In MySQL, the JSON_STORAGE_FREE() function shows how much storage space was freed up after a JSON Document was updated.

For a JSON column value, it shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().

For a JSON document (or a string which can be parsed as one), this function returns 0.

Syntax

The syntax goes like this:

JSON_STORAGE_FREE(json_val)

Where json_val represents the JSON document for which to return the amount of bytes freed up after an update. This can be a column name. It can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

Example

We run a query:

SELECT Contents 
FROM Collections 
WHERE CollectionId = 4;

And get the following data:

+-------------------------------------+
| Contents                            |
+-------------------------------------+
| {"Name": "Homer", "Stupid": "True"} |
+-------------------------------------+

Let’s check the storage size of the Contents column, and see if any space has been freed up by an update.

SELECT 
  JSON_STORAGE_SIZE(Contents) Size,
  JSON_STORAGE_FREE(Contents) Free
FROM Collections
WHERE CollectionId = 4;

Result:

+------+------+
| Size | Free |
+------+------+
|   40 |    0 |
+------+------+

In this case, the data uses up 40 bytes of storage space, and no space has been freed by any updates.

But we can change that.

Let’s do an update.

UPDATE Collections
SET Contents = JSON_SET(Contents, "$.Stupid", 1)
WHERE CollectionId = 4;

Result:

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

Let’s run another query to see the updated data.

SELECT Contents 
FROM Collections 
WHERE CollectionId = 4;

Result:

+--------------------------------+
| Contents                       |
+--------------------------------+
| {"Name": "Homer", "Stupid": 1} |
+--------------------------------+

So the value "True" has been changed to 1.

Now let’s see how much space was freed up with that update.

SELECT 
  JSON_STORAGE_SIZE(Contents) Size,
  JSON_STORAGE_FREE(Contents) Free
FROM Collections
WHERE CollectionId = 4;

Result:

+------+------+
| Size | Free |
+------+------+
|   40 |    5 |
+------+------+

This result shows that a partial update of the JSON document occurred, and that this freed up 5 bytes of storage space. The result returned by JSON_STORAGE_SIZE() is unchanged by the partial update.

Partial updates are supported for updates using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().

Non-Partial Updates

The direct assignment of a value to a JSON column cannot be partially updated, and therefore this will result in no freed space being reported.

The same holds true for a JSON user variable.

Here’s an example to demonstrate.

First we set the variable:

SET @data = '{"Name": "Homer", "Stupid": "True"}';
SELECT 
  JSON_STORAGE_SIZE(@data) Size,
  JSON_STORAGE_FREE(@data) Free;

Result:

+------+------+
| Size | Free |
+------+------+
|   40 |    0 |
+------+------+

Now we update the variable using JSON_SET():

SET @data = JSON_SET(@data, "$.Stupid", 1);
SELECT 
  JSON_STORAGE_SIZE(@data) Size,
  JSON_STORAGE_FREE(@data) Free;

Result:

+------+------+
| Size | Free |
+------+------+
|   35 |    0 |
+------+------+

So in this case no space was freed up. However, also notice that the JSON_STORAGE_SIZE() function now reports the lower number of bytes (35) used to store the document.