编写SQL查询：按与指定用户服务匹配度降序排列项目

现有两个数据表Project和User，两表均包含服务列表字段（存储为JSON数组字符串）。需要编写SQL查询，找出与User A的服务匹配数量最多的项目，并按匹配数量从多到少排序。

Project表 #

User表 #

预期结果 #

结果说明 #

• Project X与User A的全部3项服务("a", "b", "c")匹配；
• Project Z与User A的2项服务("a", "c")匹配；
• Project Y与User A的1项服务("a")匹配。

解决方案 #

不同数据库对JSON数组的处理函数存在差异，以下是主流数据库的实现方式：

1. MySQL（5.7+） #

利用JSON_TABLE将JSON数组展开为行，统计匹配数量：

SELECT 
    p.ProjectName,
    p.Services,
    COUNT(u_service.service) AS match_count
FROM 
    Project p
CROSS JOIN 
    JSON_TABLE(p.Services, '$[*]' COLUMNS (service VARCHAR(255) PATH '$')) AS p_service
JOIN 
    (SELECT service 
     FROM User 
     CROSS JOIN JSON_TABLE(Services, '$[*]' COLUMNS (service VARCHAR(255) PATH '$')) 
     WHERE UserName = 'User A') u_service
ON 
    p_service.service = u_service.service
GROUP BY 
    p.ProjectName, p.Services
ORDER BY 
    match_count DESC;

2. PostgreSQL（9.4+） #

使用jsonb_array_elements_text展开JSON数组：

SELECT 
    p.ProjectName,
    p.Services,
    COUNT(u_service.service) AS match_count
FROM 
    Project p
CROSS JOIN 
    jsonb_array_elements_text(p.Services::jsonb) AS p_service(service)
JOIN 
    (SELECT service 
     FROM User 
     CROSS JOIN jsonb_array_elements_text(Services::jsonb) AS service
     WHERE UserName = 'User A') u_service
ON 
    p_service.service = u_service.service
GROUP BY 
    p.ProjectName, p.Services
ORDER BY 
    match_count DESC;

3. SQL Server（2016+） #

借助OPENJSON解析JSON数组：

SELECT 
    p.ProjectName,
    p.Services,
    COUNT(u_service.value) AS match_count
FROM 
    Project p
CROSS APPLY 
    OPENJSON(p.Services) WITH (service VARCHAR(255) '$') AS p_service
JOIN 
    (SELECT value AS service 
     FROM User 
     CROSS APPLY OPENJSON(Services) 
     WHERE UserName = 'User A') u_service
ON 
    p_service.service = u_service.service
GROUP BY 
    p.ProjectName, p.Services
ORDER BY 
    match_count DESC;

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