How to calculate the storage size of a JSON (JavaScript Object Notation) value using the JSON_STORAGE_SIZE() function?

JSON_STORAGE_SIZE(json) is a MySQL built-in function that calculates the storage size of a JSON (JavaScript Object Notation) value or table column. For example:

SELECT
  JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
  JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
  JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
  JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
  -- +----+----+----+----+
  -- | A  | B  | C  | D  |
  -- +----+----+----+----+
  -- | 45 | 44 | 47 | 56 |
  -- +----+----+----+----+

SELECT JSON_STORAGE_SIZE('[100, "FYI", [1, 3, 5], 425.05]');
  -- +------------------------------------------------------+
  -- | JSON_STORAGE_SIZE('[100, "FYI", [1, 3, 5], 425.05]') |
  -- +------------------------------------------------------+
  -- |                                                   42 |
  -- +------------------------------------------------------+

Note that the storage size of a JSON column is not changed, if it is reduced after a partially updated with less data. For example:

CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES ('{"a": 1000, "b": "FYIcenter", "c": "[1, 3]"}');

SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size FROM jtable; 
  -- +----------------------------------------------+------+
  -- | jcol                                         | Size |
  -- +----------------------------------------------+------+
  -- | {"a": 1000, "b": "FYIcenter", "c": "[1, 3]"} |   46 |
  -- +----------------------------------------------+------+

UPDATE jtable SET jcol = JSON_SET(jcol, "$.b", "FYI");

SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size FROM jtable;
  -- +----------------------------------------+------+
  -- | jcol                                   | Size |
  -- +----------------------------------------+------+
  -- | {"a": 1000, "b": "FYI", "c": "[1, 3]"} |   46 |
  -- +----------------------------------------+------+

UPDATE jtable SET jcol = JSON_SET(jcol, "$.b", "FYIcenter.com");

SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size FROM jtable;
  -- +--------------------------------------------------+------+
  -- | jcol                                             | Size |
  -- +--------------------------------------------------+------+
  -- | {"a": 1000, "b": "FYIcenter.com", "c": "[1, 3]"} |   50 |
  -- +--------------------------------------------------+------+

In order to see the actual JSON value size, we can call JSON_STORAGE_SIZE() to obtain the free space in a JSON column and subtract it from the storage size. For example:

SELECT JSON_STORAGE_SIZE(jcol) AS StorageSize,
  JSON_STORAGE_FREE(jcol) AS FreeSpace,
  JSON_STORAGE_SIZE(jcol) - JSON_STORAGE_FREE(jcol) AS ActualSize 
  FROM jtable;
  -- +-------------+-----------+------------+
  -- | StorageSize | FreeSpace | ActualSize |
  -- +-------------+-----------+------------+
  -- |          50 |         0 |         50 |
  -- +-------------+-----------+------------+

UPDATE jtable SET jcol = JSON_SET(jcol, "$.b", "FYI");

SELECT JSON_STORAGE_SIZE(jcol) AS StorageSize,
  JSON_STORAGE_FREE(jcol) AS FreeSpace,
  JSON_STORAGE_SIZE(jcol) - JSON_STORAGE_FREE(jcol) AS ActualSize 
  FROM jtable;
  -- +-------------+-----------+------------+
  -- | StorageSize | FreeSpace | ActualSize |
  -- +-------------+-----------+------------+
  -- |          50 |        10 |         40 |
  -- +-------------+-----------+------------+

Reference information of the JSON_STORAGE_SIZE() function:

JSON_STORAGE_SIZE(json): int
  Calculates the storage size of a JSON (JavaScript Object Notation) 
  value or table column.

Arguments, return value and availability:
  json: Required. The JSON value or column to be examined.
  int: Return value. The number of types used by the JSON value.
  Available since MySQL 5.7.

Related MySQL functions:

• JSON_STORAGE_SIZE() - Storage Size of JSON Value
• JSON_STORAGE_FREE() - Free Space in JSON Value

⇒ JSON_TABLE() - Inline Table with JSON Array

⇐ JSON_SET() - Inserting/updating JSON Child Members

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References