mysql的json函数与实例_MySQL的常用JSON函数

1. JSON_SEARCH(col, type, val)

JSON_SEARCH(col ->> '$[*].key', type, val)

col： JSON格式的字段名

key：要搜索的col字段的key，如果字段没有key可以省略，例如单纯的json字符串['1', '2', '3', '4']

type：可以为'one'或者'all'，'one'表示搜索到一个即返回，'all'表示搜索完再返回

val：要搜索的字段值，字符串格式，支持模糊搜索

返回值：如果col字段的key中含有val值，则返回第一个出现该值的索引；如果col字段的key中不含有val值，则返回null

注：如果col字段为单值JSON，不需要加[*]，只有当其为数组JSON时才需要加

2. JSON_CONTAINS(col, '{key:val}')

col：JSON格式的字段名

key：要搜索的col字段的key，字符串格式

val：要搜索的字段值，字符串格式

返回值：如果col字段的key中含有val值，则返回1；如果col字段的key中不含有val值，则返回0

这两个函数都可以检索JSON格式的字段中是否含有指定的值，下面举例说明

teams表，有4个字段，其中leaders是JSON格式，表示团队主管

id name create_time leaders

1 小象 2018-10-05 [{"userId": "001", "displayName": "张三"}, {"userId": "005", "displayName": "李四"}, {"userId": "008", "displayName": "刘鹏"}]

2 新人类 2018-05-15 null

3 芒果 2017-08-22 [{"userId": "002", "displayName": "王五"}, {"userId": "005", "displayName": "李四"}]

4 三叶草 2018-01-10 [{"userId": "001", "displayName": "张三"}, {"userId": "014", "displayName": "王大锤"}]

5 火车头 2017-12-12 null

现在要检索张三作为团队主管的团队

SELECT * FROMteamsWHERE JSON_SEARCH(leaders ->> '$[*].displayName', 'one', '%张三%') IS NOT NULL;SELECT * FROMteamsWHERE JSON_CONTAINS(leaders, '{"displayName":"张三"}') = 1;

以上两种方法都查出结果

id name create_time leaders

1 小象 2018-10-05 [{"userId": "001", "displayName": "张三"}, {"userId": "005", "displayName": "李四"}, {"userId": "008", "displayName": "刘鹏"}]

4 三叶草 2018-01-10 [{"userId": "001", "displayName": "张三"}, {"userId": "014", "displayName": "王大锤"}]