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如下：