JSON_PRETTY(json_val)

提供类似于 PHP 及其他语言和数据库系统实现的 JSON 值的美化打印。提供的值必须是 JSON 值或 JSON 值的有效字符串表示形式。该值中存在的多余空格和换行符对输出没有影响。对于 NULL 值，该函数返回 NULL。如果该值不是 JSON 文档，或者无法将其解析为 JSON 文档，函数将报错。Provides pretty-printing of JSON values similar to that implemented in PHP and by other languages and database systems. The value supplied must be a JSON value or a valid string representation of a JSON value. Extraneous whitespaces and newlines present in this value have no effect on the output. For a NULL value, the function returns NULL. If the value is not a JSON document, or if it cannot be parsed as one, the function fails with an error.

此函数的输出格式遵循以下规则：Formatting of the output from this function adheres to the following rules:

mysql> SELECT JSON_PRETTY('123'); # scalar
+--------------------+
| JSON_PRETTY('123') |
+--------------------+
| 123                |
+--------------------+

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------+
| JSON_PRETTY("[1,3,5]") |
+------------------------+
| [
  1,
  3,
  5
]      |
+------------------------+

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------+

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]

JSON_STORAGE_FREE(json_val)

对于一个 JSON 列值，此函数显示在使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 原地更新后，其二进制表示中释放了多少存储空间。参数也可以是有效的 JSON 文档或可以解析为 JSON 的字符串——无论是作为字面值还是作为用户变量的值——在这种情况下，函数返回 0。如果参数是如前所述已更新的 JSON 列值，使其二进制表示所占空间小于更新前，则返回一个正的非零值。对于已更新的 JSON 列，其二进制表示与之前一样或更大，或者更新未能利用部分更新的情况，函数返回 0；如果参数为 NULL，则返回 NULL。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.

如果 json_val 不是 NULL，并且既不是有效的 JSON 文档，也无法成功解析为 JSON 文档，则会产生错误。If json_val is not NULL, and neither is a valid JSON document nor can be successfully parsed as one, an error results.

在这个示例中，我们创建一个包含 JSON 列的表，然后插入一行包含 JSON 对象的数据：In this example, we create a table containing a JSON column, then insert a row containing a JSON object:

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)

现在我们使用 JSON_SET() 更新列值，以便执行部分更新；在这种情况下，我们将 c 键所指向的值（数组 [true, false]）替换为占用空间较小的值（整数 1）：Now we update the column value using JSON_SET() such that a partial update can be performed; in this case, we replace the value pointed to by the c key (the array [true, false]) with one that takes up less space (the integer 1):

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)

连续部分更新对这部分空闲空间的影响是累积的，如本例所示，使用 JSON_SET() 减少键 b 的值所占空间（且不进行其他更改）：The effects of successive partial updates on this free space are cumulative, as shown in this example using JSON_SET() to reduce the space taken up by the value having key b (and making no other changes):

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)

在不使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 的情况下更新列意味着优化器无法就地执行更新；在这种情况下，JSON_STORAGE_FREE() 返回 0，如下所示：Updating the column without using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0, as shown here:

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)

JSON 文档的部分更新只能在列值上执行。对于存储 JSON 值的用户变量，该值总是被完全替换，即使更新是使用 JSON_SET() 执行的：Partial updates of JSON documents can be performed only on column values. For a user variable that stores a JSON value, the value is always completely replaced, even when the update is performed using JSON_SET():

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)

对于 JSON 字面量，此函数总是返回 0：For a JSON literal, this function always returns 0:

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

JSON_STORAGE_SIZE(json_val)

此函数返回用于存储 JSON 文档二进制表示所使用的字节数。当参数是一个 JSON 列时，这表示用于存储 JSON 文档的空间，存储内容与插入到列中的内容相同，在此之后可能进行的部分更新不会影响该存储空间。json_val 必须是一个有效的 JSON 文档或可以解析为 JSON 的字符串。如果它是字符串，则该函数返回通过将字符串解析为 JSON 并转换为二进制所创建的 JSON 二进制表示的存储空间大小。如果参数为 NULL，则返回 NULL。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.

当 json_val 不为 NULL，并且不是 JSON 文档或无法成功解析为 JSON 文档时，会产生错误。An error results when json_val is not NULL, and is not—or cannot be successfully parsed as—a JSON document.

为了说明当将 JSON 列作为参数使用此函数时的行为，我们创建一个名为 jtable 的表，其中包含一个 JSON 列 jcol，将 JSON 值插入表中，然后使用 JSON_STORAGE_SIZE() 获取该列使用的存储空间，如下所示：To illustrate this function's behavior when used with a JSON column as its argument, we create a table named jtable containing a JSON column jcol, insert a JSON value into the table, then obtain the storage space used by this column with JSON_STORAGE_SIZE(), as shown here:

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)

根据 JSON_STORAGE_SIZE() 的输出，插入到该列的 JSON 文档占用 47 字节。我们还使用 JSON_STORAGE_FREE() 检查了之前对该列进行的任何部分更新释放的空间；由于尚未进行任何更新，因此其值为 0，这是预期的。According to the output of JSON_STORAGE_SIZE(), the JSON document inserted into the column takes up 47 bytes. We also checked the amount of space freed by any previous partial updates of the column using JSON_STORAGE_FREE(); since no updates have yet been performed, this is 0, as expected.

接下来，我们对该表执行一个 UPDATE 操作，这应该会对存储在 jcol 中的文档进行部分更新，然后按如下所示测试结果：Next we perform an UPDATE on the table that should result in a partial update of the document stored in jcol, and then test the result as shown here:

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)

在前一个查询中，JSON_STORAGE_FREE() 返回的值表明对 JSON 文档执行了部分更新，并且这释放了用于存储它的 3 个字节空间。JSON_STORAGE_SIZE() 返回的结果在部分更新中保持不变。The value returned by JSON_STORAGE_FREE() in the previous query indicates that a partial update of the JSON document was performed, and that this freed 3 bytes of space used to store it. The result returned by JSON_STORAGE_SIZE() is unchanged by the partial update.

对于使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 进行的更新，支持部分更新。直接将值赋给 JSON 列不能进行部分更新；在进行此类更新后，JSON_STORAGE_SIZE() 始终显示新设置值所使用的存储空间：Partial updates are supported for updates using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). The direct assignment of a value to a JSON column cannot be partially updated; following such an update, JSON_STORAGE_SIZE() always shows the storage used for the newly-set value:

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)

JSON 用户变量不能部分更新。这意味着此函数始终显示当前用于存储用户变量中 JSON 文档的空间：A JSON user variable cannot be partially updated. This means that this function always shows the space currently used to store a JSON document in a user variable:

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)

对于 JSON 字面量，此函数始终返回当前使用的存储空间：For a JSON literal, this function always returns the current storage space used:

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)