mysql关于json类型的数据查询

img


CREATE TABLE `demo`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `obj` json NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `demo` VALUES (1, '[{\"id\": \"1\", \"name\": \"张三\"}]');
INSERT INTO `demo` VALUES (2, '[{\"id\": \"1\", \"name\": \"张三\"}, {\"id\": \"2\", \"name\": \"李四\"}]');
INSERT INTO `demo` VALUES (3, '[{\"id\": \"2\", \"name\": \"李四\"}]');
INSERT INTO `demo` VALUES (4, '[{\"id\": \"2\", \"name\": \"李四\"}, {\"id\": \"3\", \"name\": \"王五\"}]');
INSERT INTO `demo` VALUES (5, '[{\"id\": \"1\", \"name\": \"张三\"}, {\"id\": \"3\", \"name\": \"王五\"}]');
INSERT INTO `demo` VALUES (6, '[{\"id\": \"3\", \"name\": \"王五\"}]');
INSERT INTO `demo` VALUES (7, '[{\"id\": \"4\", \"name\": \"赵六\"}]');
INSERT INTO `demo` VALUES (8, '[{\"id\": \"1\", \"name\": \"张三\"}]');

表中obj字段是json类型，json数组中可能有1个或多个，我想查询obj中id in ("3","4")这样的，怎么写SQL？
期望的查询结果：

img

大概就是希望能查询出包含多个id的数据，例如id含3和4的。（id是String型，带双引号）

id in ("3","4") 只是一个示例，可能要查询的id不只有2个，可能很多。

我可以写出只查询一个id的情况：


SELECT * FROM demo 
WHERE JSON_CONTAINS(JSON_EXTRACT(obj, '$[*].id'), JSON_ARRAY("3"))
or JSON_CONTAINS(JSON_EXTRACT(obj, '$[*].id'), JSON_ARRAY("4"))

有没有更好的写法，我不想这样一直 追加 or 下去。

您可以使用 MySQL 8.0.4 及以上版本提供的 JSON_TABLE 函数来实现查询 id 在 3、4、5....... 中的数据，示例如下：


SELECT *
FROM demo
CROSS JOIN JSON_TABLE(obj, '$[*]'
  COLUMNS (
    id varchar(100) PATH '$.id'
  )
) AS j
WHERE j.id IN ('3', '4');

JSON_TABLE 函数将 JSON 数组转换为行，并将 id 作为一列返回，然后使用 CROSS JOIN 将其与原表进行关联，最后筛选出 id 在 3、4 中的数据。

注意：使用 JSON_TABLE 函数需要 MySQL 8.0.4 及以上版本的支持。

使用正则表达式解决：

SELECT * FROM demo WHERE obj REGEXP "\"id\": \"(?:3|4)\"";

img

可以追加其他id，比如 追加id为10的：

SELECT * FROM demo WHERE obj REGEXP "\"id\": \"(?:3|4|10)\"";

img

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
可以使用JSON_TABLE函数解析json数组，然后做联结和筛选。

以下是查询id包含"3"和"4"的数据的SQL语句：

SELECT demo.* FROM demo
JOIN JSON_TABLE(demo.obj, '$[*]' COLUMNS (id VARCHAR(10) PATH '$.id')) jt on TRUE
WHERE jt.id IN ('3', '4')
GROUP BY demo.id
HAVING COUNT(DISTINCT jt.id) = 2;

解析说明：

  1. 使用JSON_TABLE函数将json数组解析成类似表格的形式，每个元素占一行，并取出id字段。
  2. 使用JOIN联结解析后的表格和原表demo。
  3. 使用WHERE筛选出id为"3"或"4"的记录。
  4. 使用GROUP BY和HAVING过滤出同时包含id为"3"和"4"的记录。
  5. 使用SELECT返回完整的demo表的记录。

注意，在使用JSON_TABLE函数时，需要指定COLUMNS来映射json数组的字段到临时表中。本例中，我们只需要取出id字段，因此只指定了一个"id VARCHAR(10) PATH '$.id'"。

希望能对你有所帮助！
如果我的回答解决了您的问题，请采纳！

用like，然后注意"可以作为转义字符转义原本的"

如果你要同时查询的id值个数不多的话，你可以不用in，直接用like查询
如果你在数据库中直接查询使用以下sql语句

select * from demo where obj like '%"id": "3"%' or '%"id": "4"%'

如果你在代码中，则需要对引号转义下：

select * from demo where obj like "%\"id\": \"3\"%" or "%\"id\": \"4\"%"

如果你需要匹配多个，则使用REGEXP 关键字匹配

select * from demo where obj REGEXP "\"id\": \"(?:3|4)\""

正则匹配

SELECT * FROM 表名 WHERE obj REGEXP '"id":"(?:3|4)"'

参考GPT和自己的思路，您可以使用MySQL提供的JSON函数来查询包含多个id的数据，比如使用JSON_CONTAINS函数来判断是否包含某个id，再通过AND和OR来组合条件。

例如，要查询包含id为"3"和"4"的数据，可以使用如下SQL语句：

SELECT * FROM demo 
WHERE JSON_CONTAINS(obj, '{"id": "3"}')
  AND JSON_CONTAINS(obj, '{"id": "4"}');

这样就可以查询出包含id为"3"和"4"的数据了。如果要查询包含id为"3"或"4"的数据，可以使用OR关键字来组合条件：

SELECT * FROM demo 
WHERE JSON_CONTAINS(obj, '{"id": "3"}')
  OR JSON_CONTAINS(obj, '{"id": "4"}');

这样就可以查询出包含id为"3"或"4"的数据了。