LEFT JOIN关联JSON字段失败，查询指定用户可访问的文档分类

文档表的cats（分类ID列表）和uids（用户ID列表）字段为JSON类型，需求是筛选出用户ID为1的用户可访问的文档对应的分类，但当前查询无法得到期望结果。

建表与数据插入语句 #

CREATE TABLE IF NOT EXISTS `doccats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  `groupnoreason` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

INSERT INTO `doccats` (`id`, `name`, `pid`, `groupnoreason`) VALUES
(1, 'Test 1', NULL, 1),
(2, 'Test 2', NULL, 1),
(3, 'Sub Test', 1, 1),
(4, 'Inner Sub', 3, 1),
(5, 'Test 3', NULL, 1),
(6, 'Test 4', NULL, 1),
(9, 'Sub Test 2', 2, 1),
(10, 'Inner Sub 2', 2, 1),
(11, 'Sub Test 3', 5, 1),
(12, 'Inner Sub 2', 5, 1),
(13, 'Sub Test 4', 6, 1),
(14, 'Inner Sub 4', 6, 1),
(15, 'Sub Sub 1', 13, 1),
(16, 'Sub Sub 2', 13, 1),
(17, 'asdasdad', 3, 1),
(18, 'fffff', 1, 1),
(19, 'Testing INNNNNNNER', 15, 1),
(20, 'ioausdhioauhduia', 19, 1),
(21, 'dsfsdfdsfsdfsdf', 20, 1),
(22, 'fghfghfghfgh', 21, 1),
(23, 'sdfsdf', 22, 1),
(24, 'fghfghf', 23, 1),
(25, 'ghjghjhgj', 24, 1),
(26, 'hjkhjkhjk', 25, 1),
(27, '567567576', 25, 1),
(28, '678967fghfghfgh', 25, 1),
(29, '345345345453345', 24, 1);

CREATE TABLE IF NOT EXISTS `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `file` varchar(255) NOT NULL,
  `uids` json NOT NULL,
  `duids` json DEFAULT NULL,
  `suids` json DEFAULT NULL,
  `cats` json NOT NULL,
  `uploaded` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='suids';

INSERT INTO `documents` (`id`, `name`, `description`, `file`, `uids`, `duids`, `suids`, `cats`, `uploaded`, `status`) VALUES
(1, 'Test Document 1', 'osidfhjosidjf soijfsiodjfsoidjfoisdjfosidjf soidjfoisdjfoisdjfoisjfij oj oij ojoijjoisdjfiosdjf sdf sdfsdfoijoi oijsdf', 'G04ClNGZIJD4n4I0TizW8kYPdGZHkVPT.pdf', '["1", "2", "3"]', NULL, NULL, '["1", "2", "3"]', '2023-03-11 13:46:03', 1),
(2, 'Test another', 'fdsfsdfsdfs dsfsdfsdf sdfsdfdfdf', 'lowM5s3kXstpU4XJ3mstlGpDxcVckaS5.pdf', '["2", "7", "9", "10", "88"]', NULL, NULL, '["4", "10", "23", "26"]', '2023-03-11 14:31:29', 1);

原查询语句 #

SELECT 
dc.id, dc.pid, dc.name 
FROM doccats dc 
LEFT JOIN documents d ON JSON_CONTAINS(d.cats, CAST(dc.id AS JSON)) 
WHERE JSON_CONTAINS(d.uids, CAST(1 AS JSON)) 
ORDER BY dc.name ASC

问题分析 #

1. 类型不匹配：documents表的cats字段存储的是字符串类型的ID（如"1"），而dc.id是整数类型，CAST(dc.id AS JSON)生成的是整数1，JSON类型严格区分字符串和数字，导致JSON_CONTAINS无法匹配。
2. LEFT JOIN失效：WHERE子句中过滤d.uids，会将LEFT JOIN自动转化为INNER JOIN，不符合预期的关联逻辑。

修正后的查询语句 #

方法1：子查询筛选可访问文档后关联 #

SELECT DISTINCT dc.id, dc.pid, dc.name
FROM doccats dc
JOIN (
    -- 先筛选用户1可访问的文档
    SELECT cats FROM documents 
    WHERE JSON_CONTAINS(uids, '"1"') -- 直接用字符串格式的ID匹配
) d ON JSON_CONTAINS(d.cats, CAST(CONCAT('"', dc.id, '"') AS JSON)) -- 将分类ID转为字符串格式的JSON
ORDER BY dc.name ASC;

方法2：用EXISTS判断关联关系 #

SELECT DISTINCT dc.id, dc.pid, dc.name
FROM doccats dc
WHERE EXISTS (
    SELECT 1 FROM documents d
    WHERE JSON_CONTAINS(d.uids, '"1"')
    AND JSON_SEARCH(d.cats, 'one', dc.id) IS NOT NULL -- JSON_SEARCH会自动匹配类型
)
ORDER BY dc.name ASC;

期望结果 #

id  pid  name
1   NULL Test 1
2   NULL Test 2
3   1    Sub Test

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