MySQL JSON 数组用于 WHERE IN 子句

1.目的

在某些情况下，我们希望在查询中使用 IN 子句来匹配 JSON 数组中的元素。

例如，我们有一个包含用户 ID 的 JSON 数组 [1, 2, 3]，我们希望查询所有这些用户的信息。在这种情况下，我们需要将 JSON 数组转换为 IN 子句的形式。

2.使用 JSON_TABLE 函数

MySQL 提供了 JSON_TABLE 函数来处理这种转换。

JSON_TABLE 函数接受一个 JSON 数组和一个列名，将 JSON 数组的每个元素作为一行返回。然后我们可以将返回的结果作为子查询，并在主查询中使用 IN 子句。

SELECT *
FROM users
WHERE id IN (
  SELECT id
  FROM JSON_TABLE('[1, 2, 3]', '$[*]' COLUMNS (id INT PATH '$'))
);
1
2
3
4
5
6

在这个示例中，我们有一个名为 users 的表，其中包含用户的信息。我们将 JSON 数组 [1, 2, 3] 传递给 JSON_TABLE 函数，并使用 COLUMNS 子句指定要返回的列。然后我们将返回的结果作为子查询，并在主查询中使用 IN 子句来匹配用户的 ID。

其中 JSON 数组可以是字面量，也可以是一个列（比如 t1.json_array_col，给定 t1 是在 FROM 子句中在 JSON_TABLE() 之前指定的的表），还可以是函数调用JSON_EXTRACT(t1.json_data, ‘$.post.comments’)。

注意 JSON_TABLE 是 MySQL 8.0 中的一个新函数，如果版本低于 8.0 将无法使用。

3.连表查询

如果使用的 MySQL 版本低于 8.0，也就是没有 JSON_TABLE 函数可以。那么我们可以将 JSON 数组用于 WHERE IN 子句，转变成连表查询，使用 JSON_CONTAINS 用于 WHERE 条件，达到相同的效果。

SELECT *
FROM users
JOIN
(SELECT JSON_ARRAY(1,2,3) array) t WHERE JSON_CONTAINS(t.array, CAST(users.id AS JSON))

// CAST(users.id AS JSON) 还可写作 JSON_ARRAY(users.id)
1
2
3
4
5
6

上面的 SQL 将选出用户表主键为 1 2 3 的记录，查询结果与前文在 IN 子句使用 JSON_TABLE 函数相同。

---

参考文献

MySQL 8.0 Reference Manual :: 12.17 JSON Functions
MySQL 8.0 Reference Manual :: 12.17.6 JSON Table Functions
MySQL: How to use JSON_ARRAY in WHERE…IN clause? - stackoverflow
MySQL JSON array to used in “IN” clause? - Stack Overflow