MySql中Json字段的查询

建个表,加点数据

CREATE TABLE `log`  (
  `id` int NOT NULL,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `createTime` timestamp NULL DEFAULT NULL,
  `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERT INTO `log` VALUES (1, 'fdgdfgdfg', '2021-12-30 10:52:52', '{"name":"黯然gg", "age":"男","position":"Java 工程师"}');
INSERT INTO `log` VALUES (2, '十多个水电费', '2021-12-30 10:53:32', '{"name":"黯然mm","position":"前端 工程师"}');

 

查询:

语法:字段名->'$.JSON属性名'

select * from log where data->'$.name' = '黯然gg';

模糊查询一样

select * from log where data->'$.name' like concat('%','黯然g','%');

附加说明:

背景

在我的实际业务处理中,会存在一种情况:

json字段中的属性不一致,有的json有这个属性,有的json没有这个属性,在用没有的这个属性去查询的时候查不到没有这个属性的json的数据

问题

例如:

就上面的sql,应该发现了吧,数据1有age,数据2没有age

当我模糊查询age的时候,如果模糊的值为空的时候,我想要的数据是不管这个json里有没有这个age的属性,我都要显示出来。但是实际没显示,如下

select * from log where data->'$.age' like concat('%','','%');

怎么办,再找办法处理呗。

解决办法

函数

有个判断json中Key是否存在的函数——json_contains_path(json_doc, one_or_all, paths)

下面就来解释一下这个函数的用法:

1.返回值:对于这种判断类型的函数返回的一般都是true or false 或者 1 和 0。这个函数也如此,在mysql返回的是1和0。

2.参数分析:json_doc顾名思义就是json数据;paths是指要找的key,可以传入多个的key参数;one_or_all指一个值是one表示找出paths参数中的任意一个,all表示找出全部。

如:(只演示one,all不演示了)

select *,json_contains_path(data,'one','$.age') '是否包含' from log;

方案

根据上面这个函数来处理。

SELECT
	*,
	json_contains_path( DATA, 'one', '$.age' ) '是否包含' 
FROM
	log 
WHERE
CASE
		WHEN ( json_contains_path( DATA, 'one', '$.age' ) != 0 ) THEN
	( DATA -> '$.age' LIKE CONCAT( '%', '', '%' ) ) ELSE TRUE 
END

 

case when then有个需要注意的点,then后面不能有and  如果有多个判断的话只能把and放到case前面,

我业务查询代码如下,mybatis的:

typeValue是Map类型的。

$.是我在Service中拼接的,因为我发现在mybatis里不好拼接,我菜,没整出来。

            <if test="typeValue != null and typeValue.size() > 0">
                <foreach item="value" index="key" collection="typeValue.entrySet()" separator="">
                    AND case when (json_contains_path(ca.CA_TYPE_VAL,'one',#{key}) != 0)  then ( ca.CA_TYPE_VAL->#{key} like CONCAT('%', #{value}, '%') ) else true end
                </foreach>
            </if>

 

最终生成的sql如下:

版权声明:
作者:黯然gg
链接:https://3zi.cn/archives/656
来源:阿龙爱吃肉
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录