I have two mongodb aggregate query as shown below.

The below query gives me the count of total failed and the sectionType

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}
    }
])

The below query find the total who attended the exam in the section type which we got from the above query

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}
    }
])
 

Both queries are working fine, but I would like to know if there is any other way I can join these two queries and rewrite in any other better way

A sample data is as given below

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"
      }
    ]
  }

doc 3

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

The result of 1st Query

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

From the first query result we get sectionType typeA, by using that we get the second query result

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

Expected Result

My expected result which I am trying to get is

The result of 1st Query

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

CodePudding user response：

The title is really confusing. You are not merging 2 queries, you need 2 counts - total and conditional. I am sure you would find plenty of answers to that question on SO, all about using $cond in the aggregator:

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
    }
  }
])

As a side note, the original queries suggest you copied them from SQL. You may find results quite unexpected for documents with your structure.

The initial $match stage in your pipelines will match all documents where at least 1 item in the subject array matches, i.e. failed.

If you intend to count per-subject, you need to add {"$unwind": "$subject"}, stage right after the $match.

CodePudding user response：

You can use $lookup to perform your 2nd query. Simply put your 2nd query into the sub-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"
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Your requirements are not really clear and difficult to guess with given sample. You can simplify the $match stage. If you run MongoDB 5.0 or never, you can use $setWindowFields

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