Топ-100
 

JSON_STORAGE_SIZE

RDBMS Type: MySQL
Topic

JSON_STORAGE_SIZE(json_val)

This function returns the number of bytes used to store the binary
representation of a JSON document. When the argument is a JSON column,
this is the space used to store the JSON document as it was inserted
into the column, prior to any partial updates that may have been
performed on it afterwards. json_val must be a valid JSON document or a
string which can be parsed as one. In the case where it is string, the
function returns the amount of storage space in the JSON binary
representation that is created by parsing the string as JSON and
converting it to binary. It returns NULL if the argument is NULL.

An error results when json_val is not NULL, and is not---or cannot be
successfully parsed as---a JSON document.

URL: https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

Example

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
-> ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
-> jcol,
-> JSON_STORAGE_SIZE(jcol) AS Size,
-> JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+-----------------------------------------------+------+------+
| jcol | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} | 47 | 0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)

mysql> UPDATE jtable SET jcol =
-> JSON_SET(jcol, "$.b", "a");
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT
-> jcol,
-> JSON_STORAGE_SIZE(jcol) AS Size,
-> JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+--------------------------------------------+------+------+
| jcol | Size | Free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} | 47 | 3 |
+--------------------------------------------+------+------+
1 row in set (0.00 sec)

mysql> UPDATE jtable
mysql> SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT
-> jcol,
-> JSON_STORAGE_SIZE(jcol) AS Size,
-> JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+------------------------------------------------+------+------+
| jcol | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} | 56 | 0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)

mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------+------+
| @j | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] | 45 |
+------------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------+------+
| @j | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] | 43 |
+----------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+---------------------------------------------+------+
| @j | Size |
+---------------------------------------------+------+
| [100, "json", [[10, 20, 30], 3, 5], 425.05] | 56 |
+---------------------------------------------+------+
1 row in set (0.00 sec)

mysql> 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 |
+----+----+----+----+
1 row in set (0.00 sec)