Collections:
JSON_STORAGE_SIZE() - Storage Size of JSON Value
How to calculate the storage size of a JSON (JavaScript Object Notation) value using the JSON_STORAGE_SIZE() function?
✍: FYIcenter.com
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_TABLE() - Inline Table with JSON Array
⇐ JSON_SET() - Inserting/updating JSON Child Members
2023-12-11, 201🔥, 0💬
Popular Posts:
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...