12.18.6 JSON 实用函数
本节记录了针对 JSON 值或可解析为 JSON 值的字符串进行操作的实用函数。JSON_PRETTY() 以易于阅读的格式打印出 JSON 值。JSON_STORAGE_SIZE() 显示给定 JSON 值所使用的存储空间。
- JSON_PRETTY(json_val)提供 JSON 值的美化打印,类似于 PHP 和其他语言和数据库系统实现的功能。提供的值必须是 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"
]
添加于MySQL 5.7.22。
- JSON_STORAGE_SIZE(json_val) 此函数返回存储JSON文档二进制表示所使用的字节数。当参数为
JSON
列时,这是存储JSON文档所用的空间。json_val
必须是一个有效的JSON文档或可以解析为JSON文档的字符串。如果参数为NULL
,则函数返回NULL
。
当json_val
不为NULL
且不是JSON文档或无法成功解析为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
-> FROM jtable;
+-----------------------------------------------+------+
| jcol | Size |
+-----------------------------------------------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} | 47 |
+-----------------------------------------------+------+
1 row in set (0.00 sec)
根据JSON_STORAGE_SIZE()
的输出,插入到列中的JSON文档占用了47个字节。在更新后,该函数显示了新设置的值所使用的存储空间:
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
-> FROM jtable;
+------------------------------------------------+------+
| jcol | Size |
+------------------------------------------------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} | 56 |
+------------------------------------------------+------+
1 row in set (0.00 sec)
这个函数还可以显示当前用于存储用户变量中 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)
这个函数是在MySQL 5.7.22中添加的。