如何将两个mongodb查询合并为一个

Question

如下所示，我有两个mongoDB骨料查询。

以下查询给了我总数失败的计数，而截面类型的

db.Departments.aggregate([
    {
        $match: {$and: [{"subject.failed": {$eq: 'true'}}, {"section": {$eq: 'A'}}]}
    },
    {
        $group: {
            _id: {"sectionType": "$sectionType"},
            "COUNT(*)": {$sum: 1}
        }
    },
    {
        $project: {"totalFailed": "$COUNT(*)", "$sectionType": "$_id.$sectionType", "_id": 0}
    }
])

查询查找了我们从上面查询中获得的部分类型参加考试的总数，

db.Departments.aggregate([
    {
        $match: {$and: [{"sectionType": {$eq: 'Section Type From the Previous query result'}}, {"section": {$eq: 'A'}}]}
    },
    {
        $group: {
            _id: null,
            "COUNT(*)": {$sum: 1}
        }
    },
    {
        $project: {"totalCount": "$COUNT(*)", "_id": 0}
    }
])
 

这两个查询都可以正常工作，但是我会想知道我是否有其他方法可以加入这两个查询并以其他更好的方式重写

样本数据，如

doc 1

{
        "section": "A",
        "sectionType": "typeA",
        "subject": [
          {
            "failed": "true",
            "subject_name": "Name A",
            "subject_staff_count": "1245",
            "subject_id": "a1111"
          },
          {
            "failed": "true",
            "subject_name": "Name B",
            "subject_staff_count": "2222",
            "subject_id": "a2222"
          }
        ]
      }

doc 2

 {
    "section": "A",
    "sectionType": "typeA",
    "subject": [
      {
        "failed": "true",
        "subject_name": "Name B",
        "subject_staff_count": "3333",
        "subject_id": "a331"
      },
      {
        "failed": "true",
        "subject_name": "Name C",
        "subject_staff_count": "4444",
        "subject_id": "a44422"
      }
    ]
  }

文档3

{
    "section": "A",
    "sectionType": "typeA",
    "subject": []
  }

第一个查询结果的

|-------------|-------------|
| sectionType | totalFailed |
|-------------|-------------|
|    typeA    |      2      | 
|_____________|_____________|

第一个查询结果我们获得了sectionType typea ，通过使用，我们获得了第二个查询结果

|-------------|
| totalCount  |
|-------------|
|     3       | 
|_____________|

预期结果

，我试图获得的预期结果

是第一个查询的结果

|-------------|-------------|
| totalFailed |  totalCount |
|-------------|-------------|
|      2      |      3      | 
|_____________|_____________|

Answer 1

标题确实令人困惑。您没有合并2个查询，需要2个计数 - 总和有条件。我相信您会在这样的问题上找到很多答案noreferrer“> $ cond 在聚合器中：

db.Departments.aggregate([
  {
    $match: {
      "section": "A"
    }
  },
  {
    $group: {
      _id: "$sectionType",
      "totalFailed": {
        $sum: {
          $cond: [
            {
              $eq: [
                "$subject.failed",
                "true"
              ]
            },
            1,
            0
          ]
        }
      },
      "total": {
        $sum: 1
      }
    }
  },
  {
    $project: {
      "totalFailed": 1,
      "sectionType": "$_id",
      "total": 1,
      "_id": 0
    }
  }
])

作为旁注，原始查询建议您从SQL复制它们。您可能会发现带有结构的文档的结果非常出乎意料。

管道中的初始$匹配阶段将匹配所有文档，其中至少1个项目在主题数组匹配中，即失败。

如果您打算每项对象计数，则需要添加{“ $ undind”：“ $ object”}， 阶段，阶段。

Answer 2

您可以使用$ lookup执行第二查询。只需将您的第二个查询放入子pipeline中

db.Departments.aggregate([
  {
    $match: {
      $and: [
        {
          "subject.failed": {
            $eq: "true"
          }
        },
        {
          "section": {
            $eq: "A"
          }
        }
      ]
    }
  },
  {
    $group: {
      _id: {
        "sectionType": "$sectionType"
      },
      "COUNT(*)": {
        $sum: 1
      }
    }
  },
  {
    $project: {
      "totalFailed": "$COUNT(*)",
      "sectionType": "$_id.sectionType",
      "_id": 0
    }
  },
  {
    "$lookup": {
      "from": "Departments",
      "let": {
        st: "$sectionType"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$sectionType",
                    "$st"
                  ]
                },
                {
                  $eq: [
                    "$section",
                    "A"
                  ]
                }
              ]
            }
          }
        },
        {
          $group: {
            _id: null,
            "COUNT(*)": {
              $sum: 1
            }
          }
        },
        {
          $project: {
            "totalCount": "$COUNT(*)",
            "_id": 0
          }
        }
      ],
      "as": "query2"
    }
  }
])

，这就是 mongo playground 供您参考。

Answer 3

您的要求并不是很清楚且难以猜测给定的样本。您可以简化$ MATD阶段。如果您运行mongoDB 5.0或从未运行，则可以使用

db.collection.aggregate([
  { $match: { section: "A" } },
  {
    $set: {
      failed: { $ifNull: [ { $first: "$subject.failed" }, "false" ] }
    }
  },
  {
    $setWindowFields: {
      partitionBy: {
        sectionType: "$sectionType",
        failed: "$failed"
      },
      output: {
        count_failed: { $sum: 1 }
      }
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$sectionType",
      output: { count_total: { $sum: 1 } }
    }
  },
  { $match: { failed: "true" } },
  {
    $group: {
      _id: {
        sectionType: "$sectionType",
        totalCount: "$count_failed",
        totalFailed: "$count_total"
      }
    }
  }
])

mongo Playground