MySql查询和更新json字段中某个key的value

MySql查询和更新json字段中某个key的value

1.假设用户表user中有个列user_info字段为json属性，公司表company中有个列company_info字段为json属性，现在用company表中的company_info中的depart_info中的role属性更新user表中user_info中的depart_info中的role属性：

下面的表字段仅仅作为举例使用：

CREATE TABLE `user` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '全局唯一id',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名字',
  `description` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '描述',
  `user_ifo` json NOT NULL COMMENT '用户信息字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `company` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '全局唯一id',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名字',
  `description` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '描述',
  `company_ifo` json NOT NULL COMMENT '公司信息字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

update user t1
join company t
on t.id=t1.id
and json_unquote(json_extract(t.company_info, "$.depart_info.role")) IS NOT NULL
SET t1.user_info=json_replace(t1.user_info,'$.depart_info.role', 
json_unquote(json_extract(t.company_info, "$.depart_info.role")));

这里几个重要的sql json处理函数：
json_extract，解析json，提取对应key的value值
json_unquote，去除引号
json_replace，将json中对应key的value替换为第三个参数。