mysql解析json字符串数组(mysql json数组查询)-昊阳知识网

最近做项目遇到一个问题，在mysql数据库表中有一个json格式的字符串类型字段，里面存的是对象集合，我要把id=6的那条记录里的value值里面的对象数组的对象属性name的值是WORKFLOW_APPROVAL的对象的router属性值由A改成B。

听起来有点绕，就是要更新一串json格式的字符串里面的某个对象元素的某个属性值。

JSON_SEARCH函数

这个问题的解题方案依赖于JSON_SEARCH函数。我们看一下这个函数的用法。

JSON_SEARCH(json_doc，one_or_all，search_str [，escape_char [，path] …])

通过JSON_SEARCH函数，返回符合查询条件的key对应的JSON路径所组成的数组，若不存在，则返回NULL

使用下面的几种情况则返回NULL

若json_doc，search_str，或path参数中任何一个为NULL，则返回NULL

文档中不存在path

search_str未找到

下面的情况则会报错

json_doc为不合法的JSON文档

path为不合法的路径表达式

one_or_all参数非one或all

参数解析：

json_doc：要查询的Json文档，比如我的需求中的value字段

one_or_all：查询的终止条件

one：搜索在第一个匹配项后终止，并返回一个路径字符串。未定义则首先考虑哪个匹配。

all：搜索返回所有匹配的路径字符串，因此不包括重复的路径。如果有多个字符串，它们将自动包装为一个数组。数组元素的顺序是不确定的。

search_str：要搜索的参数值，比如我需求中的WORKFLOW_APPROVAL

escape_char：指定转义符。escape_char参数指定时要求必须是常量（为空或者一个字符），当escape_char参数为NULL或者不存在的情况下，系统默认使用\作为转义字符。

path指定的范围始终是要搜索或对其进行操作的文档，并以前导$字符表示。路径分支由句点字符.分隔。数组中的单元格由[N]表示，和正常访问数组元素格式相同，例如$.x代表JSON文档中x对应的值，$[1].y代表JSON文档中第二位数据元素中y所对应的值。

注：在search_str中，通配符%和_可以如同在LIKE上一样运行，其中%用于匹配多个字符（包括0），_则仅可匹配一个字符。

具体使用的使用方式可参考官方文档示例：https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

实现步骤

第一步：查找WORKFLOW_APPROVAL所在数组中的索引号

首先要找到json数组中name是WORKFLOW_APPROVAL的索引号。

select JSON_SEARCH( value, ‘one’, ‘WORKFLOW_APPROVAL’, null, ‘$**.name’)from hx_maintain_paramwhere id = 6

2.第二步：替换双引号

由于查询出来的path表达式带引号，带引号的表达式直接执行会报错，因此使用REPLACE函数去掉双引号即可。

select REPLACE( JSON_SEARCH( value, ‘one’, ‘WORKFLOW_APPROVAL’, null, ‘$**.name’), ‘”‘, ” )from hx_maintain_paramwhere id = 6

3.第三步：替换为router属性

如果将$[35].name这个表达式放到JSON_REPLACE函数中，则表示替换的是name属性对应的值，但我要替换的是router属性的值，因此还需要使用REPLACE函数把name替换为router。

select REPLACE( REPLACE( JSON_SEARCH( value, ‘one’, ‘WORKFLOW_APPROVAL’, null, ‘$**.name’), ‘”‘, ” ), ‘name’, ‘router’ )from hx_maintain_paramwhere id = 6

4.第四步：使用JSON_REPLACE函数转为UPDATE语句进行更新

最终的语句：

UPDATE hx_maintain_paramSET value = JSON_REPLACE( value, REPLACE( REPLACE( JSON_SEARCH( value, ‘one’, ‘WORKFLOW_APPROVAL_PASS’, null, ‘$**.name’), ‘”‘, ” ), ‘name’, ‘router’ ), ‘/manageAudit/manageAuditMyList/detail?auditId=:auditId&title=:title&state=:state&auditState=:auditState&category=:category&secondCategory=:secondCategory’ )where id = 6;

我是Seven，一个不懈努力的程序猿，希望本文能对你有所裨益