零、前言

从MySQL 5.7.8版本开始,MySQL支持一个原生的JSON类型。JSON值并不是以字符串形式存储的,取而代之的是内部的二进制格式( internal binary format),从而可以快速读取到JSON中的各个元素。 存储在JSON列中的JSON documents,当其被创建(insert)或者更新(update)的时候,会自动验证格式的正确性。当JSON格式不正确时,会抛出一个错误。JSON documents会在创建时被标准化,所以可以使用形如=, <, <=,>, >=, <>, !=,  <=>的比较操作符来比较大小。

一、JSON函数

  • 创建JSON: JSON_ARRAY(), JSON_MERGE(), JSON_OBJECT(),相关文档

  • 搜索JSON:JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(), JSON_SEARCH(),相关文档

  • 修改JSON:JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), JSON_UNQUOTE(),相关文档

  • 属性查询: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE(), JSON_VALID(),相关文档

二、部分函数简写

在MySQL 5.7.9以后,你可以使用column->path来简写JSON_EXTRACT(column, path)函数,这就像一个别名一样,可以用在SQL语句的任何地方,包括WHERE, ORDER BY, 和 GROUP BY条件。可以使用在 SELECT, UPDATE, DELETE, CREATE TABLE等任何语句中。表达式的左边必须是列标识符(不能是个别名)。表达式的右边,是一个引用的JSON路径表达式,MySQL会根据这个表达式,搜索出对应的值。


三、后续新增的JSON函数

MySQL 5.7.22版本添加了以下新函数:

1.两个聚合函数JSON_ARRAYAGG() 和 JSON_OBJECTAGG()

  • JSON_ARRAYAGG()把一个列或者是表达式作为它的参数,然后把它们聚合成一个JSON数组。该表达式可以是任意MySQL数据类型,并不是必须要JSON类型。

    mysql> SELECT o_id, attribute, value FROM t3;
    +------+-----------+-------+
    | o_id | attribute | value |
    +------+-----------+-------+
    |    2 | color     | red   |
    |    2 | fabric    | silk  |
    |    3 | color     | green |
    |    3 | shape     | square|
    +------+-----------+-------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes 
         > FROM t3 GROUP BY o_id;
    +------+---------------------+
    | o_id | attributes          |
    +------+---------------------+
    |    2 | ["color", "fabric"] |
    |    3 | ["color", "shape"]  |
    +------+---------------------+
    2 rows in set (0.00 sec)
  • JSON_OBJECTAGG()把两个列或者是表达式解释为一个key和一个value。只返回一个JSON对象。

    mysql> SELECT o_id, attribute, value FROM t3;
    +------+-----------+-------+
    | o_id | attribute | value |
    +------+-----------+-------+
    |    2 | color     | red   |
    |    2 | fabric    | silk  |
    |    3 | color     | green |
    |    3 | shape     | square|
    +------+-----------+-------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) FROM t3 GROUP BY o_id;
    +------+----------------------------------------+
    | o_id | JSON_OBJECTAGG(attribute, name)        |
    +------+----------------------------------------+
    |    2 | {"color": "red", "fabric": "silk"}     |
    |    3 | {"color": "green", "shape": "square"}  |
    +------+----------------------------------------+
    1 row in set (0.00 sec)


2.JSON工具函数JSON_PRETTY()

它会输出一个易于阅读的JSON格式,每一个JSON对象中的成员或者是JSON数组中的值,都会被一行一行打印;JSON中的子对象,会相对父级缩进2个字符位置。

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"]');
+-----------------------------------------------------------------------------------------------------------+
| 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"
] |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.JSON工具函数JSON_STORAGE_SIZE()

在任何部分更新之前,它以字节的形式返回一个JSON文档的二进制表示的占用的存储空间大小。

这个功能还适用于以字符串形式表示的有效的(可以格式化成JSON的那种)JSON document。对于这样的值,JSON_STORAGE_SIZE()会返回其以JSON二进制形式表示时的对应的大小。如果字符串无效(不能被格式化成JSON),函数就会报错;如果字符串是null,函数就会返回null。


4.符合RFC 7396标准的JSON合并工具 JSON_MERGE_PATCH()

它将两个JSON对象合并成一个对象,合并规则如下,以下情况下,结果中会包含对应的key:

  • 在第一个对象里存在,而在第二个对象里不存在的key

  • 在第二个对象里存在,而在第一个对象里不存在的key,而且value不为JSON空值的

  • 如果一个key在两个对象里都存在,那将会取第二个对象里的值(如果第二个对象值为null,那对应的key就会不显示)

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false]                               |
+---------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------------+
| {"id": 47, "name": "x"}                         |
+-------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
+------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
+------------------------------------------+
| {"id": 47}                               |
+------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',
     >     '{ "a": 3, "c":4 }');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+-----------------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                                  |
+-----------------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
     >     '{ "a": 5, "d":6 }');
+-------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
+-------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
+-------------------------------------------------------------------------------+

你还可以根据以上规则,把这个函数用作删除特定的key

mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a": 1}                                         |
+--------------------------------------------------+

下面这个例子表明函数以递归的方式运行;也就是说,成员的值不局限于标量,而可以是JSON documents。

mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
+----------------------------------------------------+
| {"a": {"x": 1, "y": 2}}                            |
+----------------------------------------------------+

 现在,JSON_MERGE() 已经被重命名为 JSON_MERGE_PRESERVE(),JSON_MERGE()可以继续作为JSON_MERGE_PRESERVE()的别名使用,但现在已经被弃用了,将在将来的版本中被删除。

赞助本站,网站的持续发展离不开你们的支持!一分也是爱ヾ(◍°∇°◍)ノ゙
 本文链接: ,花了好多脑细胞写的,转载请注明链接喔~~
登陆
      正在加载评论