Топ-100
 

JSON_STORAGE_FREE

RDBMS Type: MySQL
Topic

JSON_STORAGE_FREE(json_val)

For a JSON column value, this function shows how much storage space was
freed in its binary representation after it was updated in place using
JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). The argument can also be
a valid JSON document or a string which can be parsed as one---either
as a literal value or as the value of a user variable---in which case
the function returns 0. It returns a positive, nonzero value if the
argument is a JSON column value which has been updated as described
previously, such that its binary representation takes up less space
than it did prior to the update. For a JSON column which has been
updated such that its binary representation is the same as or larger
than before, or if the update was not able to take advantage of a
partial update, it returns 0; it returns NULL if the argument is NULL.

If json_val is not NULL, and neither is a valid JSON document nor can
be successfully parsed as one, an error results.

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.38 sec)

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

mysql> SELECT * FROM jtable;
+----------------------------------------------+
| jcol |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM jtable;
+--------------------------------+
| jcol |
+--------------------------------+
| {"a": 10, "b": "wxyz", "c": 1} |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
| 14 |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
| 16 |
+-------------------------+
1 row in set (0.00 sec)

mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------+------+
| @j | Free |
+----------------------------------+------+
| {"a": 10, "b": "wxyz", "c": "1"} | 0 |
+----------------------------------+------+
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
| 0 |
+------+
1 row in set (0.00 sec)