零、前言
从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()的别名使用,但现在已经被弃用了,将在将来的版本中被删除。