如何将两条SQL查询合并为单个MongoDB聚合查询？

需要将两条SQL查询的结果合并为一个MongoDB聚合查询结果，当前现有聚合仅能实现其中一条SQL的逻辑，需调整实现需求。

第一条SQL（带DispositionBy过滤） #

SELECT id,sum(DiscCount) as UTVCount from (
    SELECT edu.dispositionBy as id, count() as DiscCount 
    FROM `HRC_Education` edu 
    WHERE edu.`RecommendedDisposition` = 'Edu.Disposition.UTV' 
      AND edu.dispositionBy = 'users' 
      AND date(edu.DispositionDate) between "2022-01-12T00:00:00.0Z" AND "2022-01-23T00:00:00.0Z" 
    group by id
)
union
(
    SELECT emp.dispositionBy as id, count() as DiscCount 
    FROM `HRC_Employment` emp 
    WHERE emp.`RecommendedDisposition` = 'Emp.Disposition.UTV' 
      AND emp.dispositionBy = 'users' 
      AND date(emp.DispositionDate) between "2022-01-12T00:00:00.0Z" AND "2022-01-23T00:00:00.0Z" 
    group by id
)

第二条SQL（不带DispositionBy过滤） #

SELECT id,sum(DiscCount) as UTVCount from (
    SELECT edu.dispositionBy as id, count() as DiscCount 
    FROM `HRC_Education` edu 
    WHERE edu.`RecommendedDisposition` = 'Edu.Disposition.UTV' 
      AND date(edu.DispositionDate) between "2022-01-12T00:00:00.0Z" AND "2022-01-23T00:00:00.0Z" 
    group by id
)
union
(
    SELECT emp.dispositionBy as id, count() as DiscCount 
    FROM `HRC_Employment` emp 
    WHERE emp.`RecommendedDisposition` = 'Emp.Disposition.UTV' 
      AND date(emp.DispositionDate) between "2022-01-12T00:00:00.0Z" AND "2022-01-23T00:00:00.0Z" 
    group by id
)

如果需要区分两种过滤场景（带/不带users过滤）的结果，使用以下版本：

const result = HRC_Education.aggregate([
  // 处理HRC_Education带users过滤的统计
  {
    $match: {
      DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
      DispositionBy: 'users',
      RecommendedDisposition: 'Edu.Disposition.UTV'
    }
  },
  { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
  {
    $project: {
      _id: 0,
      id: "$_id",
      UTVCount: 1,
      filterType: { $literal: "with_users_filter" }
    }
  },
  // 合并HRC_Employment带users过滤的统计
  {
    $unionWith: {
      coll: "HRC_Employment",
      pipeline: [
        {
          $match: {
            DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
            DispositionBy: 'users',
            RecommendedDisposition: 'Emp.Disposition.UTV'
          }
        },
        { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
        {
          $project: {
            _id: 0,
            id: "$_id",
            UTVCount: 1,
            filterType: { $literal: "with_users_filter" }
          }
        }
      ]
    }
  },
  // 合并HRC_Education不带users过滤的统计
  {
    $unionWith: {
      coll: "HRC_Education",
      pipeline: [
        {
          $match: {
            DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
            RecommendedDisposition: 'Edu.Disposition.UTV'
          }
        },
        { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
        {
          $project: {
            _id: 0,
            id: "$_id",
            UTVCount: 1,
            filterType: { $literal: "without_users_filter" }
          }
        }
      ]
    }
  },
  // 合并HRC_Employment不带users过滤的统计
  {
    $unionWith: {
      coll: "HRC_Employment",
      pipeline: [
        {
          $match: {
            DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
            RecommendedDisposition: 'Emp.Disposition.UTV'
          }
        },
        { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
        {
          $project: {
            _id: 0,
            id: "$_id",
            UTVCount: 1,
            filterType: { $literal: "without_users_filter" }
          }
        }
      ]
    }
  }
])

如果需要直接合并两条SQL的结果（相同id的UTVCount累加），使用以下版本：

const result = HRC_Education.aggregate([
  // 收集所有符合条件的原始记录
  {
    $match: {
      DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
      RecommendedDisposition: 'Edu.Disposition.UTV'
    }
  },
  {
    $unionWith: {
      coll: "HRC_Employment",
      pipeline: [
        {
          $match: {
            DispositionDate: { $gte: new Date("2022-01-12T00:00:00.0Z"), $lt: new Date("2022-01-23T00:00:00.0Z") },
            RecommendedDisposition: 'Emp.Disposition.UTV'
          }
        }
      ]
    }
  },
  // 同时计算两种过滤场景的统计
  {
    $facet: {
      withUsersFilter: [
        { $match: { DispositionBy: 'users' } },
        { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
        { $project: { _id: 0, id: "$_id", UTVCount: 1 } }
      ],
      withoutUsersFilter: [
        { $group: { _id: "$DispositionBy", UTVCount: { $sum: 1 } } },
        { $project: { _id: 0, id: "$_id", UTVCount: 1 } }
      ]
    }
  },
  // 合并两个场景的结果
  { $project: { combined: { $concatArrays: ["$withUsersFilter", "$withoutUsersFilter"] } } },
  { $unwind: "$combined" },
  { $replaceRoot: { newRoot: "$combined" } }
])

1. 拆分逻辑并标识场景：通过filterType字段区分两种过滤规则的结果，避免合并后数据混淆。
2. 多集合结果合并：使用$unionWith依次合并两个集合、两种过滤场景的统计结果。
3. 简化语法：将{ $eq: 'xxx' }简化为直接赋值，符合MongoDB简洁写法规范。

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