MySQL的函数和运算符 - JSON 函数 - JSON 实用函数

林员外聊编程 2021-09-13

321

JSON 实用函数

本文介绍作用于 JSON 值或可解析为 JSON 值的字符串的实用函数。JSON_PRETTY() 以易于阅读的格式输出 JSON 值。JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 分别显示给定 JSON 值使用的存储空间容量和在部分更新后 JSON 列中剩余的空间容量。

● JSON_PRETTY(json_val)

提供 JSON 值的美观打印，类似于 PHP 和其他语言和数据库系统实现的 JSON 值。提供的值必须是 JSON 值或 JSON 值的有效字符串表示形式。此值中出现的多余空格和换行对输出没有影响。对于 NULL 值，函数返回 NULL。如果值不是 JSON 文档，或者不能被解析为 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 文档，或者是一个可以被解析为 JSON 值的字符串字面量或用户变量值，在这种情况下，函数返回 0。如果参数是一个 JSON 列值，并且按照前面的描述进行了更新，那么它将返回一个正的非零值，这样它的二进制表示比更新之前占用的空间更少。对于已更新的 JSON 列，其二进制表示与之前相同或更大，或者如果更新不能利用部分更新，则返回 0，如果参数为 NULL，则返回 NULL。

如果 json_val 不是 NULL，而且既不是有效的 JSON 文档，也不能成功地解析为一个有效的 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 文档的二进制表示的字节数。当参数是一个 JSON 列时，这个空间是用于存储插入到列中的 JSON 文档，并且在随后可能对其执行的任何部分更新之前的空间。json_val 必须是一个有效的 JSON 文档或一个可以被解析为 JSON 的字符串。在字符串的情况下，该函数返回将字符串解析为 JSON 并将其转换为二进制表示的存储空间量。如果参数为 NULL，则返回 NULL。

当 json_val 不是 NULL，并且不是或者不能被成功地解析为一个 JSON 文档时，会报错。

为了演示这个函数在使用 JSON 列作为参数时的行为，我们创建了一个名为 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)

官方网址：

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

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - JSON 函数 - JSON 实用函数

评论