Mysql Json 数据类型&函数

一、mysql中的JSON类型

MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type

1.2 json中的path表示

MySQL 通过字符串的 Path 描述帮助我们映射到对应的数据。和 JavaScript 中对象的操作比较类似，通过 . 获取下一级的属性，通过 [] 获取数组元素。

不一样的地方在于需要通过 $ 表示本身，这个也比较好理解。另外就是可以使用 * 和 ** 两个通配符，比如 .* 表示当前层级的所有成员的值，[*] 则表示当前数组中所有成员值。** 类似 LIKE 一样可以接前缀和后缀，比如 a**b 表示的是以 a 开头，b结尾的路径。

SET @j = '[{"a": 1, "b": 2, "c": {"d": 4}},{"a":2},{"abb":3}]';

select JSON_EXTRACT(@j, '$[0].a'), JSON_EXTRACT(@j, '$[*].a');

mysql中对json的函数总结：

二、创建json值的函数

2.1 JSON_ARRAY([val[, val] ...])

入参为n个值，出参为n个值组成的json数组

2.2 JSON_OBJECT([key, val[, key, val] ...])

入参为n个键值对，出参为这些键值对组成的json对象

2.3 JSON_QUOTE(string)

将一个string字符串引用为json值，通过在字符串两侧包裹'"'双引号，并转移字符串中的转义字符。该函数通常用于创建一个json文档中的合法json字符串。

2.4  CAST(value AS JSON)

也可以通过强制转换将其他类型的字段转换为json类型：

2.5 JSON_ARRAYAGG(col_or_expr) [over_clause] 及 JSON_OBJECTAGG(key, value) [over_clause]

两种聚合函数也可以生成json值，其中JSON_ARRAYAGG将数据列中包含的元素包装成json array返回

JSON_OBJECTAGG入参，为两个列名，第一个列值为key，第二个列值为value。返回为key：value生成的键值对。

三、检索json的函数

3.1 JSON_CONTAINS(target, candidate[, path])

检测目标json对象（target）中是否包含候选json对象（candidate）。如果传入path参数，则检测target的具体path位置是否包含候选json对象。判断是否包含的规则如下：

1）目标json对象和候选json对象都是标量，则当两个标量的可比较【拥有相同的JSON_TYPE】且相同时，目标json对象包含候选json对象。

2）目标json对象和候选json对象都是json数组，则当候选json对象的每个元素都被目标对象的某些元素包含时，目标json对象包含候选json对象

3）目标json对象是json数组，候选json对象不是json数组，则当候选对象被目标对象的某些元素包含时，目标json对象包含候选json对象

4）目标json对象和候选json对象都是json object。则当候选对象的key都被目标对象包含，切关联的值被目标对象key关联的值包含时，目标json对象包含候选json对象。

5）目标json对象不是json数组，候选json对象是json数组，目标json对象不可能包含候选json对象。

3.2 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

检测json_doc中是否包含制定路径。

one：json_doc中包含任一路径

all：json_doc中包含所有路径

3.3 JSON_EXTRACT(json_doc, path[, path] ...)

返回json中相应路径的文档，返回所有相应路径的文档，如果有多个包装成json array返回

3.4 column->path

JSON_EXTRACT的语法糖，如果JSON_EXTRACT只有两个参数，即只有一个path，可以使用这个语法糖代替

select wg->'$[*][0].a' as wa from table;

select wg->'$[0].a' as wa from table;

3.5 column->>path

在MySQL 5.7.13+，还可以用"->>"表示去掉抽取结果的"号，下面三种效果是一样的：

• JSON_UNQUOTE( JSON_EXTRACT(column, path) )
• JSON_UNQUOTE(column -> path)
• column->>path

3.6  JSON_KEYS(json_doc[, path])

获取json文档在指定路径下的所有键值，返回一个json array

3.7 JSON_OVERLAPS(json_doc1, json_doc2)

比较两个JSON是否有相同元素。也就是是否有交集。有则返回1，没有返回0

部分匹配的情况不能算有相同元素。

两个JSON对象比较时，两者至少有一个相同name的key和相同对应value，则返回1。

两个标量比较时，则比较值是否相等。

标量和数组比较时，则判断标量是否和数组某元素相等。数据类型不同时不算相等。

 MySQL 8.0.17加入的函数

3.8 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

根据某字符串，返回在目标JSON中匹配的value的路径表达式，search_str和JSON中的value相等时算匹配。

此函数能查询子元素信息。

第一个参数json_doc是目标JSON。

第二个参数one_or_all可以选择one或者all。one表示返回一个匹配的值的路径。all表示返回所有路径。

参数search_str是要搜索的字符串。其中可以用%(百分号)代表任意多个字符，_(下划线)代表任意一个字符。

参数escape_char是转义字符。默认是\。写成空字符串或NULL时，也默认为\。

参数path是路径表达式，如果写了path，匹配结果需在路径表达式下进行。

3.9 value MEMBER OF(json_array)

判断value是否被包含在某JSON数组中。包含则返回1，否则返回0。

数据格式不同时不算包含。

value可以用其他表达式替代。

SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');

四、更改json的函数

 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

五、查询json属性的函数

JSON_DEPTH(json_doc)

JSON_LENGTH(json_doc[, path])

JSON_TYPE(json_val)

JSON_VALID(val)

引用：

MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type

MySQL :: MySQL 8.0 Reference Manual :: 12.18 JSON Functions