MySQL JSON_STORAGE_FREE() Function

In MySQL, the JSON_STORAGE_FREE() function returns the space freed after a JSON column has been updated by JSON_SET(), or JSON_REPLACE(), or JSON_REMOVE().

JSON_STORAGE_FREE() Syntax

Here is the syntax of the MySQL JSON_STORAGE_FREE() function:

JSON_STORAGE_FREE(json)

Parameters

json

Required. A JSON document. It can be a string JSON literal, or a JSON column.

Return value

In MySQL, the JSON_STORAGE_FREE() function returns the space freed after a JSON column has been updated by JSON_SET(), or JSON_REPLACE(), or JSON_REMOVE(). It can accept a string JSON literal or a JSON column as a parameter.

If the parameter is a string, the JSON_STORAGE_FREE() function returns 0.

If the parameter is a JSON column, the JSON_STORAGE_FREE() function returns as follows:

• If the column is partially updated JSON_SET(), or JSON_REPLACE(), or JSON_REMOVE(), it returns the space freed.
• If the column has not been updated, or is partially updated by other methods , it returns 0.
• It returns 0 if the content of the column became larger after the update.

If the parameter is NULL, the JSON_STORAGE_FREE() function returns NULL.

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.

JSON_STORAGE_FREE() Examples

This example shows the usage of JSON_STORAGE_FREE(), and where it differs from JSON_STORAGE_SIZE().

First, let’s create a table test_json_storage_free:

DROP TABLE IF EXISTS test_json_storage_free;
CREATE TABLE test_json_storage_free (
    json_col JSON NOT NULL
);

Then, let’s insert 1 row for testing:

INSERT INTO test_json_storage_free
VALUES ('{"x": 1, "y": "abcd"}');

Then, let’s take a look at at returned values ​​of JSON_STORAGE_SIZE() and JSON_STORAGE_FREE():

SELECT
    json_col,
    JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE,
    JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE
FROM
    test_json_storage_free;

+-----------------------+-------------------+-------------------+
| json_col              | JSON_STORAGE_SIZE | JSON_STORAGE_FREE |
+-----------------------+-------------------+-------------------+
| {"x": 1, "y": "abcd"} |                26 |                 0 |
+-----------------------+-------------------+-------------------+

We can see that the JSON_STORAGE_FREE() function returned 0 and the JSON_STORAGE_SIZE() function returned occupied storage space.

Next, let’s modify the value of the JSON column:

UPDATE test_json_storage_free
SET json_col = '{"x": 1, "y": "abcd", "z": 3}';

Then, let’s take a look at the return values ​​of JSON_STORAGE_SIZE() and JSON_STORAGE_FREE():

SELECT
    json_col,
    JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE,
    JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE
FROM
    test_json_storage_free;

+-------------------------------+-------------------+-------------------+
| json_col                      | JSON_STORAGE_SIZE | JSON_STORAGE_FREE |
+-------------------------------+-------------------+-------------------+
| {"x": 1, "y": "abcd", "z": 3} |                34 |                 0 |
+-------------------------------+-------------------+-------------------+

We can see that the returned value of JSON_STORAGE_SIZE() has changed because the contents of the json_col column have changed. But the JSON_STORAGE_FREE() function still returned 0 because the json_col column was not updated by JSON_SET(), JSON_REPLACE() or JSON_REMOVE().

Then, let’s remove member z from the column json_col using JSON_REMOVE():

UPDATE test_json_storage_free
SET json_col = JSON_REMOVE(json_col, '$.z');

Then, let’s tabke a look the result:

SELECT
    json_col,
    JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE,
    JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE
FROM
    test_json_storage_free;

+-----------------------+-------------------+-------------------+
| json_col              | JSON_STORAGE_SIZE | JSON_STORAGE_FREE |
+-----------------------+-------------------+-------------------+
| {"x": 1, "y": "abcd"} |                34 |                 8 |
+-----------------------+-------------------+-------------------+

Here, since we updated the json_col column with JSON_REPLACE(), so JSON_STORAGE_FREE() returned 8.