最近做项目遇到一个问题,在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_param
where id = 6
2.第二步:替换双引号
由于查询出来的path表达式带引号,带引号的表达式直接执行会报错,因此使用REPLACE函数去掉双引号即可。
select REPLACE(
JSON_SEARCH(
value,
'one',
'WORKFLOW_APPROVAL',
null,
'$**.name'),
'"',
''
)
from hx_maintain_param
where 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_param
where id = 6
4.第四步:使用JSON_REPLACE函数转为UPDATE语句进行更新
最终的语句:
UPDATE hx_maintain_param
SET 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,一个不懈努力的程序猿,希望本文能对你有所裨益