MySQL JSON_STORAGE_SIZE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_STORAGE_SIZE() function to get the storage size of a JSON document.

Introduction to MySQL JSON_STORAGE_SIZE() function

The JSON_STORAGE_SIZE() function returns the storage size in bytes of a JSON document. It can be useful for estimating the storage requirements of JSON data.

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

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

In this syntax:

json_val: This is the JSON value for which you want to get the storage size. The json_value must be a valid JSON document or a string that can be parsed as a valid JSON document.

The JSON_STORAGE_SIZE() function returns an integer that represents the number of bytes of a JSON value. The function returns NULL if the json_val is NULL.

The function will raise an error if the json_val is not NULL and cannot be parsed as a JSON document.

MySQL JSON_STORAGE_SIZE() function examples

Let’s take some examples of using the JSON_STORAGE_SIZE() function.

1) Using the MySQL JSON_STORAGE_SIZE() function with a JSON object

The following example uses the JSON_STORAGE_SIZE() function to get the size of a JSON object:

SELECT 
  JSON_STORAGE_SIZE(
    '{"name":"John","age":30,"city":"New York"}'
  ) size;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| size |
+------+
|   51 |
+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the function returns the storage size required for the provided JSON object, which is 51 bytes.

2) Using the JSON_STORAGE_SIZE() function with an array

The following example uses the JSON_STORAGE_SIZE() function to get the size of a JSON array:

SELECT 
  JSON_STORAGE_SIZE('[1, 2, 3]') size;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| size |
+------+
|   14 |
+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using the JSON_STORAGE_SIZE() function with a table data

We’ll use the employees table from the sample database:

First, create a new table called employee_results:

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

Second, insert data from the employees table into the employees_results table:

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

Third, get the size of the data column using the JSON_STORAGE_SIZE() function:

SELECT 
  id, 
  data, 
  JSON_STORAGE_SIZE(data) size 
FROM 
  employee_results;Code language: SQL (Structured Query Language) (sql)

Output:

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

Summary

Use the JSON_STORAGE_SIZE() function to get the storage size in bytes of a JSON document

Was this tutorial helpful?