# 12.18.8 JSON 实用函数

本节介绍作用于 JSON 值或可解析为 JSON 值的字符串的实用程序函数。JSON_PRETTY()以易于阅读的格式打印出 JSON 值。JSON_STORAGE_SIZE()JSON_STORAGE_FREE()分别显示给定 JSON 值使用的存储空间量和一个JSON部分更新后的列。

  • JSON_PRETTY(*json_val*)

    提供类似于 PHP 和其他语言和数据库系统实现的 JSON 值的漂亮打印。提供的值必须是 JSON 值或 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 文档或一个可以被解析为 1 的字符串——作为文字值或作为用户变量的值——在这种情况下,函数返回 0。它返回一个正的非零值,如果论证是一个JSON如前所述已更新的列值,因此其二进制表示占用的空间比更新前要少。为一个JSON已更新的列使其二进制表示与以前相同或更大,或者如果更新无法利用部分更新,则返回 0;它返回空值如果参数是空值.

    如果*json_val*不是空值,并且既不是有效的 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键(数组[真假]) 与占用空间较小的一个(整数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 并将其转换为二进制而创建的。它返回空值如果参数是空值.

    出现错误时*json_val*不是空值,并且不是(或无法成功解析为)JSON 文档。

    为了说明此函数在与 a 一起使用时的行为JSON列作为它的参数,我们创建一个名为包含一个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。

    接下来我们执行一个更新在应该导致存储在中的文档的部分更新的表上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)