mysql关于json类型的数据查询

Question

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？
期望的查询结果：

大概就是希望能查询出包含多个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 下去。

Answer 1

您可以使用 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 及以上版本的支持。