MongoDB聚合查询返回数据量异常冗余问题求助

你的查询返回数据量翻倍，核心问题出在第二个$lookup使用了数组类型的localField，加上后续的$unwind操作触发了笛卡尔积效应：

1. 第一个$lookup关联tpes后，每个profile文档的tpesBySite是数组格式（若部分profile对应多条tpe记录，数组长度会大于1）
2. 第二个$lookup用tpesBySite.terminalId（数组）匹配settlements的terminalID时，MongoDB会遍历数组内每个terminalId单独匹配，导致单个profile拉回多倍的settlements数据
3. 后续连续的$unwind操作（拆分tpesBySite、settlementsByUser、settledTransactions数组）会把每个数组元素拆成独立文档，进一步放大数据量

你提到实际数据266条、返回524条，大概率是某个关联环节存在1对2的匹配关系（比如一个profile对应2条tpe记录，或一个tpe对应2条settlement记录），最终通过unwind把数据量翻倍。

调整聚合流程，先拆分tpesBySite数组，再用单个terminalId关联settlements，避免数组匹配带来的多对多问题：

let settlement = await this.profileModel.aggregate([
    {
      $match: {
        bindedSuperAdmin: name,
      },
    },
    // 关联tpes集合，得到tpesBySite数组
    {
      $lookup: {
        from: 'tpes',
        localField: 'nameUser',
        foreignField: 'merchantName',
        as: 'tpesBySite',
      },
    },
    // 先拆分tpesBySite数组，确保每个文档对应单个tpe记录
    { $unwind: '$tpesBySite' },
    // 用单个terminalId关联settlements，避免数组匹配导致的多对多
    {
      $lookup: {
        from: 'settlements',
        localField: 'tpesBySite.terminalId',
        foreignField: 'terminalID',
        as: 'settlementsByUser',
        pipeline: [
          {
            $sort: {
              transactionDate: -1,
            },
          },
        ],
      },
    },
    // 拆分settlementsByUser数组
    { $unwind: '$settlementsByUser' },
    // 拆分settledTransactions数组
    { $unwind: '$settlementsByUser.settledTransactions' },
    // 投影字段（与原逻辑一致）
    {
      $project: {
        bReconcileError: '$settlementsByUser.bReconcileError',
        batchNumber: '$settlementsByUser.batchNumber',
        btransfered: '$settlementsByUser.btransfered',
        countryCode: '$settlementsByUser.countryCode',
        currencyCode: '$settlementsByUser.currencyCode',
        merchantID: '$settlementsByUser.merchantID',
        nSettlementAmount: '$settlementsByUser.nSettlementAmount',
        onlineMessageMACerror: '$settlementsByUser.onlineMessageMACerror',
        reconciliationAdviceRRN:
          '$settlementsByUser.reconciliationAdviceRRN',
        reconciliationApprovalCode:
          '$settlementsByUser.reconciliationApprovalCode',
        settledTransactions: [
          {
            appName: '$settlementsByUser.settledTransactions.appName',
            cardInputMethod:
              '$settlementsByUser.settledTransactions.cardInputMethod',
            cardPAN_PCI:
              '$settlementsByUser.settledTransactions.cardPAN_PCI',
            networkName:
              '$settlementsByUser.settledTransactions.networkName',
            onlineApprovalCode:
              '$settlementsByUser.settledTransactions.onlineApprovalCode',
            onlineRetrievalReferenceNumber:
              '$settlementsByUser.settledTransactions.onlineRetrievalReferenceNumber',
            transactionAmount:
              '$settlementsByUser.settledTransactions.transactionAmount',
            transactionDate:
              '$settlementsByUser.settledTransactions.transactionDate',
            transactionTime:
              '$settlementsByUser.settledTransactions.transactionTime',
            transactionType:
              '$settlementsByUser.settledTransactions.transactionType',
          },
        ],
        settlementAmount: '$settlementsByUser.settlementAmount',
        settlementDate: '$settlementsByUser.settlementDate',
        settlementTime: '$settlementsByUser.settlementTime',
        terminalID: '$settlementsByUser.terminalID',
        traceNumber: '$settlementsByUser.traceNumber',
        uniqueID: '$settlementsByUser.uniqueID',
      },
    },
  ]);
  console.log('settlement from service ', settlement.length);
  return settlement;

1. 检查tpes集合中，是否存在同一个merchantName对应多条tpe记录（确认业务上是否合理）
2. 检查settlements集合中，是否存在同一个terminalID对应多条settlement记录
3. 若仅需最新一条结算记录，可在第二个$lookup的pipeline中添加$limit: 1，避免拉回多条重复数据

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