分页接口返回重复文档排查：数据库无重复但接口返回同ID数据

问题描述 #

测试getLeads分页接口时，不同分页返回了ID和内容完全一致的文档，但数据库中无重复数据。相关代码、示例文档及查询参数如下：

接口代码 #

async function getLeads(req, res) {

    try {
        
        const { 
            page = 0, 
            pageSize = 10, 
            sort = null, 
            search = "", 
            leadStatus = "", 
            industry = "", 
            agentId = "",
            endDate = "", 
            startDate = "",
        } = req.query
        
        const generateSort = () => {
            
            const sortParsed = JSON.parse(sort)
            
            const sortFormatted = {
                [sortParsed.field]: (sortParsed.sort = 'asc' ? 1 : -1)
            }
            
            return sortFormatted;
        }
        
        const sortFormatted = Boolean(sort) ? generateSort() : { createdAt: -1 }
        
        const leadsQuery = {
            $or: [
                { PhoneNumberCombined: { $regex: new RegExp(search) } },
                { ExecutiveFirstName: { $regex: new RegExp(search) } },
                { Email: { $regex: new RegExp(search) } },
            ],
            Agent: agentId !== "" ? new mongoose.Types.ObjectId(agentId) : { $exists: true },
            createdAt: {
                $gt: startDate !== "" ? new Date(startDate) : new Date("2000-01-01"),
                $lte: endDate !== "" ? new Date(endDate) : new Date("3000-03-03"),
            },
            LeadStatus: leadStatus !== "" ? leadStatus : { $exists: true },
        };
        
        if (industry !== "") {
            console.log("industry: ",industry);
            leadsQuery.Industry = industry;
        }
        
        const leads = await Lead.find(leadsQuery)
            .sort(sortFormatted)
            .skip(page * pageSize)
            .limit(pageSize)
            .populate({ path: 'Agent', select: 'FirstName LastName' });

        const totalLeads = await Lead.countDocuments({
            PhoneNumberCombined: { $regex: search }
        })

        const agents = await User.find({
            Role: "agent"
        }).select(' FirstName LastName ')

        res.status(200).json({
            leads: leads,
            agents: agents,
            total: totalLeads
        })
    } catch (error) {
        res.status(500).json({
            'error': error,
            'errorMassage': 'something went wrong in get all leads'
        })
    }
}

示例文档片段 #

[
    {
        "_id": "64d2acb05ea3ee328798ad48",
        "DateListProduced": "08/07/2023",
        "CompanyName": "Building Futures Marketing LLC",
        "MailingAdress": "100 W Hoover Ave # 103-618",
        "MailingCity": "Mesa",
        "MailingState": "AZ",
        "MailingZipCode": "85210",
        "ExecutiveLastName": "",
        "ExecutiveFirstName": "",
        "ExecutiveGender": "",
        "ExecutiveTitle": "",
        "PhoneNumberCombined": "(480) 382-7039",
        "LocationSalesVolumeRange": "$500,000-1 Million",
        "SICCodeDescription1": "Smoke Shops & Supplies",
        "LeadStatus": "New",
        "Email": "",
        "Agent": {
            "_id": "64c95c8dae03f14f3a9ffce3",
            "FirstName": "haboub",
            "LastName": "habiyb"
        },
        "Notes": [],
        "Calls": [],
        "Industry": "ISAM",
        "__v": 0,
        "createdAt": "2023-08-08T20:59:28.592Z",
        "updatedAt": "2023-08-08T20:59:28.592Z"
    },
    {
        "_id": "64d2acb05ea3ee328798ad47",
        "DateListProduced": "08/07/2023",
        "CompanyName": "Bullhead Vape Fort Mohave",
        "MailingAdress": "4470 S Highway 95 # 15",
        "MailingCity": "Fort Mohave",
        "MailingState": "AZ",
        "MailingZipCode": "86426",
        "ExecutiveLastName": "",
        "ExecutiveFirstName": "",
        "ExecutiveGender": "",
        "ExecutiveTitle": "",
        "PhoneNumberCombined": "(928) 299-5112",
        "LocationSalesVolumeRange": "",
        "SICCodeDescription1": "Smoke Shops & Supplies",
        "LeadStatus": "New",
        "Email": "",
        "Agent": {
            "_id": "64c95c8dae03f14f3a9ffce3",
            "FirstName": "haboub",
            "LastName": "habiyb"
        },
        "Notes": [],
        "Calls": [],
        "Industry": "ISAM",
        "__v": 0,
        "createdAt": "2023-08-08T20:59:28.592Z",
        "updatedAt": "2023-08-08T20:59:28.592Z"
    }
]

查询参数 #

const queryPredicates = {
page: 0,
pageSize: 10, 
sort: { field: 'CompanyName', sort: 'asc' },
leadStatus: "New",
search:"",
agentId: "",
industry: "Food",};

问题原因分析 #

1. 排序逻辑赋值错误：generateSort函数中用赋值运算符=替代了比较运算符===，导致无论传入的排序方向是什么，都会被强制设为asc，排序逻辑完全失效。
2. 排序稳定性缺失：当使用CompanyName或createdAt这类非唯一字段排序时，MongoDB无法保证相同字段值的文档返回顺序固定。每次查询时这部分文档的顺序可能随机变化，导致skip+limit分页时，同一文档出现在不同页中。
3. 总数查询条件不匹配：totalLeads仅统计了PhoneNumberCombined匹配的文档数，而实际查询的leadsQuery包含了Agent、LeadStatus、Industry等多个过滤条件，导致返回的总页数计算错误，前端可能重复加载同一批数据。

修复方案 #

1. 修正排序逻辑的赋值错误 #

const generateSort = () => {
    const sortParsed = JSON.parse(sort);
    const sortFormatted = {
        [sortParsed.field]: sortParsed.sort === 'asc' ? 1 : -1
    };
    return sortFormatted;
};

2. 增加排序稳定性（唯一字段兜底） #

在排序规则中追加_id字段，利用其唯一性保证排序结果固定：

const sortFormatted = Boolean(sort) 
    ? { ...generateSort(), _id: 1 } 
    : { createdAt: -1, _id: 1 };

3. 统一总数查询条件 #

将totalLeads的查询条件与leadsQuery保持一致，确保总数统计准确：

const totalLeads = await Lead.countDocuments(leadsQuery);

修复后核心代码片段 #

const generateSort = () => {
    const sortParsed = JSON.parse(sort);
    const sortFormatted = {
        [sortParsed.field]: sortParsed.sort === 'asc' ? 1 : -1
    };
    return sortFormatted;
};

const sortFormatted = Boolean(sort) 
    ? { ...generateSort(), _id: 1 } 
    : { createdAt: -1, _id: 1 };

const leads = await Lead.find(leadsQuery)
    .sort(sortFormatted)
    .skip(page * pageSize)
    .limit(pageSize)
    .populate({ path: 'Agent', select: 'FirstName LastName' });

const totalLeads = await Lead.countDocuments(leadsQuery);

验证建议 #

1. 用相同查询参数请求不同分页，检查是否仍出现重复文档。
2. 确认返回的total值与实际符合条件的文档总数一致。
3. 测试升序、降序两种排序方向，验证分页逻辑是否正常。

内容的提问来源于stack exchange，提问作者Mohamed habib Grami