MySQL JSON_STORAGE_SIZE() Function

In MySQL, the JSON_STORAGE_SIZE() function returns the number of bytes used to store the binary representation of a JSON document.

`JSON_STORAGE_SIZE()` Syntax

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

JSON_STORAGE_SIZE(json)

Parameters

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

Return value

In MySQL, the JSON_STORAGE_SIZE() function returns the number of bytes used to store the binary representation of a JSON document. It can accept a string JSON literal, or a JSON column as a parameter.

If the parameter is a string JSON literal, the JSON_STORAGE_SIZE() function returns the number of bytes occupied by the JSON value parsed from the JSON literal.

If the parameter is a JSON column, the JSON_STORAGE_SIZE() function returns the storage space occupied by the JSON document inserted into the column. Absolutely, this number may change with future updates.

If the parameter is NULL, the JSON_STORAGE_SIZE() 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_SIZE()` Examples

JSON numbers

SELECT
    'Size' AS `Json`,
    JSON_STORAGE_SIZE('0') AS `0`,
    JSON_STORAGE_SIZE('1') AS `1`,
    JSON_STORAGE_SIZE('1000') AS `1000`,
    JSON_STORAGE_SIZE('100000') AS `100000`;

+------+---+---+------+--------+
| Valu | 0 | 1 | 1000 | 100000 |
+------+---+---+------+--------+
| Size | 3 | 3 |    3 |      5 |
+------+---+---+------+--------+

JSON strings

SELECT
    'Size' AS `Json`,
    JSON_STORAGE_SIZE('"a"') AS `a`,
    JSON_STORAGE_SIZE('"Hello World"') AS `Hello World`;

+------+---+-------------+
| Json | a | Hello World |
+------+---+-------------+
| Size | 3 |          13 |
+------+---+-------------+

JSON boolean

SELECT
    'Size' AS `Json`,
    JSON_STORAGE_SIZE('true') AS `true`,
    JSON_STORAGE_SIZE('false') AS `false`;

+------+------+-------+
| Json | true | false |
+------+------+-------+
| Size |    2 |     2 |
+------+------+-------+

JSON null

SELECT
    'Size' AS `Json`,
    JSON_STORAGE_SIZE('null') AS `null`;

+------+------+
| Json | null |
+------+------+
| Size |    2 |
+------+------+

Example: Arrays and Objects

SELECT
    'Size' AS `Json`,
    JSON_STORAGE_SIZE('[1, 2]') AS `[1, 2]`,
    JSON_STORAGE_SIZE('[1, 2, 3]') AS `[1, 2, 3]`,
    JSON_STORAGE_SIZE('{"x": 1}') AS `{"x": 1}`,
    JSON_STORAGE_SIZE('{"x": 1, "y": 2}') AS `{"x": 1, "y": 2}`;

+------+--------+-----------+----------+------------------+
| Json | [1, 2] | [1, 2, 3] | {"x": 1} | {"x": 1, "y": 2} |
+------+--------+-----------+----------+------------------+
| Size |     11 |        14 |       13 |               21 |
+------+--------+-----------+----------+------------------+

JSON column

This example demonstrates how to use the JSON_STORAGE_SIZE() function calculate the space occupied by a JSON column.

First, let’s create a table test_json_storage_size:

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

Then, let’s insert 1 row for testing:

INSERT INTO test_json_storage_size
VALUES ('{"x": 1, "y": 2}');

Then, let’s use the JSON_STORAGE_SIZE() function calculate how much storage space occupied by json_col:

SELECT
    json_col,
    JSON_STORAGE_SIZE(json_col)
FROM
    test_json_storage_size;

+------------------+-----------------------------+
| json_col         | JSON_STORAGE_SIZE(json_col) |
+------------------+-----------------------------+
| {"x": 1, "y": 2} |                          21 |
+------------------+-----------------------------+

Next, let’s modify the JSON column:

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

Finally, let’s take a look at how much storage space is occupied:

SELECT
    json_col,
    JSON_STORAGE_SIZE(json_col)
FROM
    test_json_storage_size;

+--------------------------+-----------------------------+
| json_col                 | JSON_STORAGE_SIZE(json_col) |
+--------------------------+-----------------------------+
| {"x": 1, "y": 2, "z": 3} |                          29 |
+--------------------------+-----------------------------+

We get that the JSON_STORAGE_SIZE() return value has changed because the value of the JSON column has been changed.

JSON_STORAGE_SIZE() Syntax