SQL按关联ID连接两个JSON列 匹配排列实现管辖项目查询

场景说明 #

现有writers和projects两张表，其中writers表包含两个JSON类型字段：

• supervising：存储主管管辖的下属作者ID列表
• projects：存储作者负责的项目ID列表
  主管本身不承担写作任务，仅通过下属作者关联对应负责的项目，需要实现两类关联查询。

示例表结构与测试数据 #

writers表 #

| id | name | supervising (JSON)  | projects (JSON)   |
|:-- |:-----| :-------------------| :-----------------|
| 1  | John | ["3","4","5","6"]   | null              |
| 2  | Bill | ["7","8","9","10"]  | null              |
| 3  | Andy | null                | ["1","2"]         |
| 4  | Hank | null                | ["3","4","5"]     |
| 5  | Alex | null                | ["6","7","8"]     |
| 6  | Joe  | null                | ["9","10"]        |
| 7  | Ken  | null                | ["11","12","13"]  |
| 8  | Zach | null                | ["14","15","16"]  |
| 9  | Walt | null                | ["17","18"]       |
| 10 | Mike | null                | ["19","20","21"]  |

字段说明：

• writers.supervising存储被管辖作者的writers.id值：John管辖Andy、Hank、Alex、Joe；Bill管辖Ken、Zach、Walt、Mike
• writers.projects存储对应作者负责的projects.id值，例如Andy负责Boston、Chicago项目

projects表 #

| id | title    |
|:-- |:---------|
| 1  | Boston   |
| 2  | Chicago  |
| 3  | Cisco    |
| 4  | Seattle  |
| 5  | North    |
| 6  | West     |
| 7  | Miami    |
| 8  | York     |
| 9  | Tainan   |
| 10 | Seoul    |
| 11 | South    |
| 12 | Tokyo    |
| 13 | Carlisle |
| 14 | Fugging  |
| 15 | Turkey   |
| 16 | Paris    |
| 17 | Midguard |
| 18 | Fugging  |
| 19 | Madrid   |
| 20 | Salvador |
| 21 | Everett  |

待实现需求 #

1. 查询指定主管管辖的所有作者负责的全部项目ID与项目名称，例如查询id=1的John管辖范围内的所有项目，预期返回id1-10对应的10条项目记录
2. 校验指定主管是否管辖某一特定项目，例如校验John是否管辖id=13的Carlisle项目，预期返回0行

之前编写的SQL存在语法错误，核心问题是没有正确处理JSON数组的关联匹配，以下是MySQL环境下的可直接运行的正确实现：

正确实现方案 #

核心逻辑分两步：先匹配主管管辖的下属作者，再匹配下属作者负责的项目，根据MySQL版本不同可以选择两种写法：

• MySQL 8.0及以上：用JSON_TABLE把JSON数组展开为行做关联，逻辑清晰性能更好
• MySQL 5.7：用JSON_CONTAINS直接做匹配条件，兼容低版本

1. 查询主管管辖的所有项目 #

MySQL 8.0+ 写法

SELECT DISTINCT p.id, p.title
FROM writers supervisor
-- 展开主管的管辖列表，拿到所有下属作者ID
JOIN JSON_TABLE(
  supervisor.supervising,
  '$[*]' COLUMNS (sub_id INT PATH '$')
) sub ON 1=1
-- 关联下属作者的基础信息
JOIN writers subordinate ON subordinate.id = sub.sub_id
-- 展开下属作者的负责项目列表，拿到所有项目ID
JOIN JSON_TABLE(
  subordinate.projects,
  '$[*]' COLUMNS (proj_id INT PATH '$')
) proj ON 1=1
-- 关联项目表拿项目名称
JOIN projects p ON p.id = proj.proj_id
WHERE supervisor.id = 1 -- 替换为要查询的主管ID
ORDER BY p.id;

执行返回结果和预期完全一致：

| id | title   |
|:-- |:--------|
| 1  | Boston  |
| 2  | Chicago |
| 3  | Cisco   |
| 4  | Seattle |
| 5  | North   |
| 6  | West    |
| 7  | Miami   |
| 8  | York    |
| 9  | Tainan  |
| 10 | Seoul   |

MySQL 5.7 兼容写法

SELECT DISTINCT p.id, p.title
FROM writers supervisor
JOIN writers subordinate 
  ON JSON_CONTAINS(supervisor.supervising, CAST(subordinate.id AS JSON), '$')
JOIN projects p
  ON JSON_CONTAINS(subordinate.projects, CAST(p.id AS JSON), '$')
WHERE supervisor.id = 1
ORDER BY p.id;

2. 校验主管是否管辖指定项目 #

MySQL 8.0+ 写法

直接查匹配记录，存在则返回项目ID，不存在返回空：

SELECT p.id
FROM writers supervisor
JOIN JSON_TABLE(
  supervisor.supervising,
  '$[*]' COLUMNS (sub_id INT PATH '$')
) sub ON 1=1
JOIN writers subordinate ON subordinate.id = sub.sub_id
JOIN JSON_TABLE(
  subordinate.projects,
  '$[*]' COLUMNS (proj_id INT PATH '$')
) proj ON 1=1
JOIN projects p ON p.id = proj.proj_id
WHERE supervisor.id = 1 -- 主管ID
  AND p.id = 13; -- 待校验的项目ID

如果需要直接返回布尔判断结果，可以用EXISTS包裹：

SELECT EXISTS(
  SELECT 1
  FROM writers supervisor
  JOIN JSON_TABLE(
    supervisor.supervising,
    '$[*]' COLUMNS (sub_id INT PATH '$')
  ) sub ON 1=1
  JOIN writers subordinate ON subordinate.id = sub.sub_id
  JOIN JSON_TABLE(
    subordinate.projects,
    '$[*]' COLUMNS (proj_id INT PATH '$')
  ) proj ON 1=1
  WHERE supervisor.id = 1
    AND proj.proj_id = 13
) AS is_supervised;

返回0代表不管辖，返回1代表管辖。

MySQL 5.7 兼容写法

SELECT p.id
FROM writers supervisor
JOIN writers subordinate 
  ON JSON_CONTAINS(supervisor.supervising, CAST(subordinate.id AS JSON), '$')
JOIN projects p
  ON JSON_CONTAINS(subordinate.projects, CAST(p.id AS JSON), '$')
WHERE supervisor.id = 1
  AND p.id = 13;

注意：如果JSON数组中存储的是严格字符串类型的数字（例如["3","4"]而非[3,4]）需要精确匹配，可以把CAST(xxx AS JSON)替换为JSON_QUOTE(CAST(xxx AS CHAR))生成字符串类型的JSON值再做匹配，避免类型隐式转换导致的误差。

内容的提问来源于stack exchange，提问作者Jesse