MySQL查询小工具，替换json数组字符串中对象的属性值

科技 06-18 来源： Seven的代码实验室

最近做项目遇到一个问题，在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;