查询JSON对象数组中嵌套Url重复项的SQL语句问题

API返回的原始JSON数据无法修改，需要遍历所有条目里的Urls数组，汇总其中的Url并找出重复项。

示例JSON数据 #

const data = [
    {
        "Id": 1,
        "ActualUrl": "https://test.com/test",
        "DateCreated": "2024-08-11T08:44:01",
        "DateUpdated": "2024-08-11T08:44:01",
        "Deadline": "2024-08-12T08:44:01",
        "PullZoneId": 1,
        "PullZoneName": "1",
        "Path": "/test",
        "Message": "1",
        "Status": 0,
        "Urls": [
            { "Url": "https://test.com/", "Status": 0 },
            { "Url": "https://test.com/", "Status": 0 }
        ]
    },
    {
        "Id": 12,
        "ActualUrl": "https://test.com/test",
        "DateCreated": "2024-08-11T08:44:01",
        "DateUpdated": "2024-08-11T08:44:01",
        "Deadline": "2024-08-12T08:44:01",
        "PullZoneId": 1,
        "PullZoneName": "1",
        "Path": "/test",
        "Message": "1",
        "Status": 0,
        "Urls": [
            { "Url": "https://test.com/", "Status": 0 },
            { "Url": "https://test.com/", "Status": 0 }
        ]
    }
    // 可添加更多记录
];

尝试过的SQL（报错或无结果） #

SELECT u.Url, COUNT(*) AS Count
FROM AbuseCases a
JOIN a.Urls u
GROUP BY u.Url
HAVING COUNT(*) > 1;

解决方案 #

1. 直接用JavaScript处理（前端/Node.js） #

因为是API返回的JSON，直接在代码层处理最便捷，无需依赖数据库：

// 提取所有Url
const allUrls = data.flatMap(item => item.Urls.map(urlItem => urlItem.Url));

// 统计每个Url出现的次数
const urlCountMap = allUrls.reduce((map, url) => {
  map[url] = (map[url] || 0) + 1;
  return map;
}, {});

// 筛选出重复的Url（出现次数>1）
const duplicateUrls = Object.entries(urlCountMap).filter(([url, count]) => count > 1);

console.log(duplicateUrls);
// 输出示例：[ [ 'https://test.com/', 4 ] ]

2. PostgreSQL数据库处理（JSON存入jsonb字段） #

如果将JSON存入PostgreSQL的jsonb类型字段，需要先展开数组再统计：

-- 假设表名为abuse_cases，存储JSON的字段名为data
SELECT jsonb_extract_path_text(url, 'Url') AS url, COUNT(*) AS count
FROM abuse_cases,
     jsonb_array_elements(data->'Urls') AS url
GROUP BY jsonb_extract_path_text(url, 'Url')
HAVING COUNT(*) > 1;

3. SQLite数据库处理（启用JSON1扩展） #

SQLite需先启用JSON1扩展，再展开数组统计：

-- 假设表名为abuse_cases，存储JSON的字段名为data
SELECT json_extract(value, '$.Url') AS url, COUNT(*) AS count
FROM abuse_cases,
     json_each(data, '$.Urls')
GROUP BY json_extract(value, '$.Url')
HAVING COUNT(*) > 1;

原SQL写法问题说明 #

原SQL用了关系型表的JOIN语法，但JSON数组不是数据库表，无法直接通过JOIN a.Urls u关联。必须先使用数据库的JSON函数将数组展开为行记录，再进行分组统计。

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