MongoDB聚合统计outcome字段值数量问题求助

需要统计MongoDB中outcome字段各值的出现次数，示例数据如下：

[
  {
    _id: new ObjectId("62ebc7b037292c87fbb44b7a"),
    uniqueID: '6303246862620',
    sn: 'N400W150726',
    transactionAmount: '0157213',
    currencyCode: '9000000058800',
    terminalId: '05000001',
    transactionDate: 2022-06-17T00:00:00.000Z,
    transactionTime: +151539-12-31T23:00:00.000Z,
    transactionType: 'TRANSACTION_TYPE_PURCHASE',
    cardPAN_PCI: 'XXXXXXXXXXXXXXX166',
    onlineRetrievalReferenceNumber: '6551151112',
    outcome: 'OUTCOME_APPROUVED',
    encryptionKeyKCV: '65465112213',
    transactionEncrypted: 'SUHTHROTRTH161461'
  },
  {
    _id: new ObjectId("62ebc7b037292c87fbb44b7a"),
    uniqueID: '6303246862620',
    sn: 'N400W150726',
    transactionAmount: '0157213',
    currencyCode: '9000000058800',
    terminalId: '05000001',
    transactionDate: 2022-06-17T00:00:00.000Z,
    transactionTime: +151539-12-31T23:00:00.000Z,
    transactionType: 'TRANSACTION_TYPE_PURCHASE',
    cardPAN_PCI: 'XXXXXXXXXXXXXXX166',
    onlineRetrievalReferenceNumber: '6551151112',
    outcome: 'OUTCOME_DECLINED',
    encryptionKeyKCV: '65465112213',
    transactionEncrypted: 'SUHTHROTRTH161461'
  },
  {
    _id: new ObjectId("62ebc7b037292c87fbb44b7a"),
    uniqueID: '6303246862620',
    sn: 'N400W150726',
    transactionAmount: '0157213',
    currencyCode: '9000000058800',
    terminalId: '05000001',
    transactionDate: 2022-06-17T00:00:00.000Z,
    transactionTime: +151539-12-31T23:00:00.000Z,
    transactionType: 'TRANSACTION_TYPE_PURCHASE',
    cardPAN_PCI: 'XXXXXXXXXXXXXXX166',
    onlineRetrievalReferenceNumber: '6551151112',
    outcome: 'OUTCOME_DECLINED',
    encryptionKeyKCV: '65465112213',
    transactionEncrypted: 'SUHTHROTRTH161461'
  },
  {
    _id: new ObjectId("62ebc7b037292c87fbb44b7a"),
    uniqueID: '6303246862620',
    sn: 'N400W150726',
    transactionAmount: '0157213',
    currencyCode: '9000000058800',
    terminalId: '05000001',
    transactionDate: 2022-06-17T00:00:00.000Z,
    transactionTime: +151539-12-31T23:00:00.000Z,
    transactionType: 'TRANSACTION_TYPE_PURCHASE',
    cardPAN_PCI: 'XXXXXXXXXXXXXXX166',
    onlineRetrievalReferenceNumber: '6551151112',
    outcome: 'OUTCOME_DECLINED',
    encryptionKeyKCV: '65465112213',
    transactionEncrypted: 'SUHTHROTRTH161461'
  }
]

期望输出：

[
  { name: 'OUTCOME_APPROUVED', value: 1 },
  { name: 'OUTCOME_DECLINED', value: 3 }
]

尝试的代码1（无结果返回）：

let logs = await Promise.all(
  tpes.map(async (item) => {
    return await this.logModel.aggregate([
      { $match: { terminalID: item } },
      { $group: { _id: { _id: '$outcome' }, count: { $sum: 1 } } },
    ]);
  }),
);

输出为空数组，怀疑索引对$match不起作用。

尝试的代码2（能获取数据但无法分组统计）：

let logs = await Promise.all(
  tpes.map(async (item) => {
    return await this.logModel.find({terminalId})
  }),
);

问题根源 #

第一个聚合查询无结果的核心原因是字段名大小写不匹配：数据里的字段是terminalId（小写d），但聚合$match里用了terminalID（大写D），导致匹配不到任何数据。

修正后的聚合查询 #

调整字段名，并修改$group结构，同时用$project映射成期望的格式：

let logs = await Promise.all(
  tpes.map(async (item) => {
    return await this.logModel.aggregate([
      // 修正字段名：terminalId（小写d）
      { $match: { terminalId: item } },
      // 直接按outcome分组，简化_id结构
      { $group: { _id: '$outcome', count: { $sum: 1 } } },
      // 映射成目标格式
      { $project: { _id: 0, name: '$_id', value: '$count' } }
    ]);
  }),
);

替代方案：内存中统计（若聚合不适用） #

如果坚持用find获取数据，可以在内存中手动统计：

let logs = await Promise.all(
  tpes.map(async (item) => {
    const data = await this.logModel.find({ terminalId: item });
    // 统计outcome出现次数
    const countMap = data.reduce((acc, curr) => {
      acc[curr.outcome] = (acc[curr.outcome] || 0) + 1;
      return acc;
    }, {});
    // 转换成数组格式
    return Object.entries(countMap).map(([name, value]) => ({ name, value }));
  }),
);

优化建议 #

• 确保MongoDB字段名大小写完全一致，字段名区分大小写。
• 若terminalId是高频查询字段，建议给它创建索引，提升$match的查询效率。

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