| json_search | 按给定字符串关键字搜索json，返回匹配的路径 | json_search('["10","2","1"]','all','1') | "$[2]" |
| -- | 查询路径2 | json_search('{"a":"123","b":{"c":"d","e":"f"}}','all','f') | "$.b.e" |
| json_merge | 合并json数组或对象 | json_merge('["10","2"]','["10","9"]') | ["10", "2", "10", "9"] |
| JSON_MERGE_PRESERVE | 合并json数组或对象,代替json_merge用途 | JSON_MERGE_PRESERVE ('["10","2"]','["10","9"]') | ["10", "2", "10", "9"] |
| json_remove | 删除json数据 | json_remove('{"a":"123","b":"123"}','$.a') | {"b": "123"} |
| -- | 删除数组中值 | json_remove('["10","2","1","6"]','$[2]') | ["10", "2", "6"] |
| -- | 删除当数组中存在某一个值时执行删除(注意, 当没有该值时, 这个查询的返回值为null) | json_remove('["10","2","1","6"]',json_unquote(json_search('["10","2","1","6"]','all','1'))) | ["10", "2", "6"] |
| -- | 删除当数组中存在某一个值时执行删除(安全的操作, 只要数据量不大于99999就没问题) | json_remove('["10","2","1","6"]',IFNULL(json_unquote(json_search('["10","2","1","6"]','all','11')),'$[9999999]')) | ["10", "2", "1", "6"] |
| json_keys | 提取json中的键值为json数组 | json_keys('{"a":"123","b":{"c":"d","e":"f"}}') | ["a", "b"] |
| json_length | 返回json的长度 | json_length('["10","2","10","9"]') | 4 |
| -- | 如果为复杂的对象结构, 这个长度依旧为根部key的个数 | json_length('{"a":"123","b":{"c":"d","e":"f"}}') | 2 |
| json_unquote | 去除json字符串的引号，将值转成string类型 | | |
| json_extract | 提取json值 | json_extract(openid,REPLACE(json_unquote(json_search(openid,'all','2')),'app_id','openid')) | "oKTQ41OqjJ848_SPe7wbTZO9RmOE" | openid 字段的数据包 '[{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]' |
| json_replace | 替换值（只替换已经存在的旧值） | 实际测试如果值不存在, 并不会导致原有数据丢失 | | |
| json_set | 只要目标值不为null,不为数组即可设置 | | json_set(data,"$.address","Guangzhou") | |