Mysql 中 Json、JsonArray 相关操作

场景：

        对Mysql 数据库中存储的 Json 、JsonArray 字段中指定字段做 like 模糊查询，在度娘的答案中辗转了许久，发现类似的提问很多，但很多都是千篇一律，牛头不对马嘴的无效复制文，因为项目需要，结合度娘某些类似答案思路和自己的多次尝试，找到了 目前 有效的模糊搜索方法，记录与此，供自己和大家食用。

      如有问题或其他更好的解决  JsonArray 模糊查询指定属性的方法，欢迎留言交流。

2024-12-18 补充 更新记录JsonArray 方法

问题：

      解决Mysql 中  json、JsonArray 类型字段中指定属性的模糊查询和更新问题

解决方法：

    话不多说，sql很简单，直接上代码：

一、查询

   1. 解决json类型字段的模糊查询：

     存储的数据格式：{"type": "10", "mobile": "13545678900", "countryCode": "86"}

select * from a  where mobile_json->'$.mobile' like '%135%'

1.1 解决接送类型字段的精确查询

数据存储格式：{"type": "10", "mobile": "13545678900", "countryCode": "86"}

select * from a where mobile_json-> '$.mobile' = 13545678900

 2. 解决 JsonArray 类型字段的模糊查询：

   存储的数据格式: [{"type": "10", "mobile": "13545678900", "countryCode": "86"}]

select * from a where mobile_json->'$[*].mobile' like '%135%'

2.1 解决 JsonArray 类型字段的精确查询：

 存储的数据格式: [{"type": "10", "mobile": "13545678900", "countryCode": "86"}]

select * from a where JSON_CONTAINS(mobile_json,JSON_OBJECT('mobile', "13545678900"))

2.2 JsonArray 多层级精确查询

字段名：mobile_json

存储的数据格式: [{"type": "10", "mobile": "13545678900", "countryCode": "86", role:[{"id":"1", name: "角色一"}, {"id":"2", name: "角色二"}]}]

select * from a where JSON_EXTRACT(mobile_json,'$[0].role[0].name') = '角色1'

这个有个弊端就是层级必须事先明确，而且是固定的，暂时未找到通用的方法，后面找到再来修改。

以上解决方法，分别是针对 json 和 json 数组类型指定属性的查询方法，其中区别就是，json 数组的模糊查询方法中 $.mobile 改成 $[*].mobile 表示查询所有。

二、更新

1. JsonArray中指定位置中追加属性，目前只找到更新指定层级的，所有层级的没实现

update table_name set json_arr = JSON_SET(json_arr, '$[0].id', MD5(UUID())), update_date = NOW(), update_by = 'U11' where json_arr->'$[*].id' is null and del = 10;

上述是往json_arr 的第一个元素中追加属性id，值为 uuid() MD5密文

2. Json更新属性

update table_name set json_data = JSON_SET(json_data , '$.id', '1111'), update_date = NOW(), update_by = 'U11' where id = "aaaa";

如有问题或其他更好的方法，以及帮助到你，欢迎大家留言交流。