MySQL :: MySQL 8.4 Reference Manual :: 14.17.8 JSON Utility Functions

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / JSON Utility Functions

14.17.8 JSON 实用函数

本节记录了对 JSON 值或可以解析为 JSON 值的字符串进行操作的实用函数。JSON_PRETTY() 将输出一个易于阅读的 JSON 值。JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 分别显示给定 JSON 值所占用的存储空间和在一个JSON列中进行部分更新后剩余的存储空间。

JSON_PRETTY(json_val)

提供了类似于 PHP 和其他语言和数据库系统实现的 JSON 值的美化打印。供给的值必须是一个 JSON 值或一个有效的字符串表示形式的 JSON 值。该值中的额外空格和换行符对输出没有影响。如果是NULL值，函数返回NULL。如果值不是一个 JSON 文档或不能被解析为一个，则函数失败并出现错误。

输出的格式遵循以下规则：

每个数组元素或对象成员都在单独的一行上，相比其父级增加一级缩进。
每个缩进级别添加两个前导空格。
分隔单个数组元素或对象成员的逗号在将要分隔这两个元素或成员的换行符之前打印。
对象成员的键和值之间使用冒号后跟一个空格(': ')分隔。
空对象或数组将在单行上打印。没有在开启和关闭括号之间打印空格。
字符串标量和键名中的特殊字符使用JSON_QUOTE()函数所用的相同规则进行转义。

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文档或一个字符串，可以被解析为一个—以字面值或用户变量的值形式—in which case the function returns 0. 如果该参数是描述前述更新方式的JSON列值，它们的二进制表示形式占用空间小于之前更新前的空间，该函数返回一个正整数；否则，如果更新后该值的二进制表示形式与之前相同或更大，或者更新不能利用部分更新，该函数返回0；如果参数是NULL，该函数返回NULL。

如果json_val不是NULL，并且也不是一个有效的JSON文档或不能成功解析为一个，那么将出现错误。

在这个示例中，我们创建了一个包含JSON列的表，然后插入了一行包含JSON对象的记录：

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：

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（不进行其他更改）:

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，如下所示：

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()执行更新：

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：

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 文档的二进制表示形式所占用的字节数。 lorsque the argument 是一个JSON列，这是将 JSON 文档插入到列中的空间，prior to any partial updates that may have been performed on it afterwards。json_val必须是一个有效的 JSON 文档或可以被解析为一个字符串。在这种情况下，它返回由将该字符串解析为 JSON 并转换为二进制所占用的存储空间。如果参数是NULL，函数返回NULL。

当json_val不是NULL且不能被成功解析为 JSON 文档时，将出现错误。

为了illustrate this function's behavior when used with a JSON column as its argument，我们创建一个名为jtable的表，其中包含一个JSON列jcol，将 JSON 值插入到表中，然后使用JSON_STORAGE_SIZE()获取该列所占用的存储空间，如下所示：

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，正如预期。

接下来，我们对表进行一个UPDATE，该操作应该导致jcol中存储的文档部分更新，然后将结果测试如下所示：

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()返回的结果未被部分更新影响。

支持使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()进行的部分更新。将值直接赋值给JSON列不能部分更新；在执行这样的更新后，JSON_Storage_SIZE()总是显示用于存储新设置值的存储空间：

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 文档的存储空间：

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 字面量，这个函数总是返回当前存储空间使用情况：

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)

PREV HOME UP NEXT