JSON_STORAGE_SIZE

Description

This function returns the number of binary bytes required to store a JSON document. When the parameter is a JSON column, it is used to store the JSON document. Partial updates to JSON do not change the return value of this function.

Syntax

JSON_STORAGE_SIZE(json_val)

Description

The json_val parameter must be a valid JSON document or a string that can be parsed into one. If json_val is a string, the function parses the string into JSON, converts it to binary, and returns the number of binary bytes required to store it.

If json_val is not NULL and cannot be successfully parsed into a JSON document, an error is returned.

If the parameter is NULL, NULL is returned.

Examples

CREATE TABLE jtbl (jcol JSON);
Query OK, 0 rows affected (0.000 sec)

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

SELECT jcol,JSON_STORAGE_SIZE(jcol) AS Size FROM jtbl;
+-----------------------------------------------+------+
| jcol                                          | Size |
+-----------------------------------------------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   41 |
+-----------------------------------------------+------+
1 row in set (0.001 sec)

SET @jn = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.000 sec)

SELECT @jn, JSON_STORAGE_SIZE(@jn) AS Size;
+------------------------------------+------+
| @jn                                | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] |   38 |
+------------------------------------+------+
1 row in set (0.001 sec)

SET @jn = JSON_SET(@jn, '$[1]', "json");
Query OK, 0 rows affected (0.000 sec)

SELECT @jn, JSON_STORAGE_SIZE(@jn) AS Size;
+----------------------------------+------+
| @jn                              | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] |   36 |
+----------------------------------+------+
1 row in set (0.001 sec)