I have the following MongoDB collection:

db={
  "problems": [
    {
      "problemId": 1,
      "title": "dummy 1",
    },
    {
      "problemId": 2,
      "title": "dummy 2",
    }
  ],
  "submissions": [
    {
      "submissionId": 1,
      "status": "AC",
      "problemId": 1,
    },
    {
      "submissionId": 2,
      "status": "AC",
      "problemId": 1,
    },
    {
      "submissionId": 3,
      "status": "WA",
      "problemId": 2,
    },
    {
      "submissionId": 4,
      "status": "WA",
      "problemId": 1,
    },
    {
      "_id": 5,
      "status": "AC",
      "problemId": 2,
    },
    {
      "_id": 6,
      "status": "WA",
      "problemId": 2,
    }
  ]
}

I want to show the total submission count and the number of solutions with status = 'AC'. I want the result to look like this:

[
  {
    "problemId": 1,
    "title": "dummy 1",
    "total_submissions": 3,
    "accepted_submissions": 2
  },
]

So far I have used the $lookup operator and did something like this:

db.problems.aggregate([
  {
    "$lookup": {
      "from": "submissions",
      "localField": "problemId",
      "foreignField": "problemId",
      "as": "submission_docs"
    }
  }
])

But the result I get is: (I'm just showing 1 item in the list)

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "problemId": 1,
    "submission_docs": [
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "problemId": 1,
        "status": "AC",
        "submissionId": 1
      },
      {
        "_id": ObjectId("5a934e000102030405000004"),
        "problemId": 1,
        "status": "AC",
        "submissionId": 2
      },
      {
        "_id": ObjectId("5a934e000102030405000006"),
        "problemId": 1,
        "status": "WA",
        "submissionId": 4
      }
    ],
    "title": "dummy 1"
  }
]

The MongoDB playground can be found here: https://mongoplayground.net/p/YRdpyQN5f00

CodePudding user response：

1. Use $size to count the submission_docs array length for total_submissions.
2. Filter the submission with status "AC" in submission_docs via $filter and next count the array length.

db.problems.aggregate([
  {
    "$lookup": {
      "from": "submissions",
      "localField": "problemId",
      "foreignField": "problemId",
      "as": "submission_docs"
    }
  },
  {
    $project: {
      _id: 0,
      problemId: 1,
      title: 1,
      total_submissions: {
        $size: "$submission_docs"
      },
      accepted_submissions: {
        $size: {
          $filter: {
            input: "$submission_docs",
            cond: {
              $eq: [
                "$$this.status",
                "AC"
              ]
            }
          }
        }
      }
    }
  }
])

Sample Mongo Playground