MySQL JSON_STORAGE_FREE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_STORAGE_FREE() function to obtain the storage size of a JSON column that was freed after it was updated.

Introduction to MySQL JSON_STORAGE_FREE function

The JSON_STORAGE_FREE function is used to get the space (in bytes) of a JSON column that was freed in its binary presentation after it was updated in place by the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function.

Here’s the syntax of the JSON_STORAGE_FREE() function:

JSON_STORAGE_FREE(json_val)Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_val is a valid JSON document or a string that can be a valid JSON document.

The JSON_STORAGE_FREE() function returns a positive, nonzero value if the JSON column that has been updated takes up less space than it did before the update.

If the JSON column takes the same or more space than before it was updated, the JSON_STORAGE_FREE() function will return 0.

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

MySQL JSON_STORAGE_FREE function example

We’ll use the employees table from the sample database for the demonstration.

First, create a table called employee_results:

DROP TABLE IF EXISTS employee_results;

CREATE TABLE employee_results(
    id INT PRIMARY KEY,
    data JSON
);Code language: SQL (Structured Query Language) (sql)

The employee_results has two columns:

id: This is the primary key column.
data: This is the JSON column that will store JSON data.

Second, insert data into the employee_results table from the data from the employees table using the INSERT INTO SELECT statement:

INSERT INTO employee_results(id, data) 
SELECT 
  employeeNumber, 
  JSON_OBJECT(
    'firstName', firstName, 'lastName', lastName, 'email', email
  ) 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Third, query data from the employee_results table:

SELECT * FROM employee_results;Code language: SQL (Structured Query Language) (sql)

Output:

+------+-----------------------------------------------------------------------------------------------+
| id   | data                                                                                          |
+------+-----------------------------------------------------------------------------------------------+
| 1002 | {"email": "[email protected]", "lastName": "Murphy", "firstName": "Diane"}         |
| 1056 | {"email": "[email protected]", "lastName": "Patterson", "firstName": "Mary"}     |
| 1076 | {"email": "[email protected]", "lastName": "Firrelli", "firstName": "Jeff"}      |
...Code language: SQL (Structured Query Language) (sql)

Fourth, update the last name of the employee number 1056 from Patterson to Doe:

UPDATE 
  employee_results 
SET 
  data = JSON_SET(data, '$.lastName', 'Doe') 
WHERE 
  id = 1056;Code language: SQL (Structured Query Language) (sql)

Finally, get the space that was freed by the JSON_SET update:

SELECT 
  JSON_STORAGE_FREE(data) 
FROM 
  employee_results 
WHERE 
  id = 1056;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+
| JSON_STORAGE_FREE(data) |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the space that was freed from the update is 6 bytes.

Summary

Use the MySQL JSON_STORAGE_FREE() function to obtain how much space of a JSON column that was freed after it was updated.

Was this tutorial helpful?