一、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_docone_or_allpath[, path] ...)

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

one:json_doc中包含任一路径

all:json_doc中包含所有路径

3.3 JSON_EXTRACT(json_docpath[, 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_doc1json_doc2)

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

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

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

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

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

 MySQL 8.0.17加入的函数

3.8 JSON_SEARCH(json_docone_or_allsearch_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_docpathval[, pathval] ...)

JSON_ARRAY_INSERT(json_docpathval[, pathval] ...)

五、查询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

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐