MongoDB Aggregate get the number of user exist in a specific exam and secure number of position in that exam

user_11089028 0
Viewed 59

I have a dataset like this:

[
  {
    "_id" : ObjectId("5fa506a9fe8d8429b0f7a85c"),
    "userid" : "5c35f1045643180d9488112f",
    "pt" : 2,
    "date" : ISODate("2021-02-28T08:17:45.489Z"),
    "wtg" : 0.92,
    "rank" : 1
  },
  {
      "_id" : ObjectId("5fa506a9fe8d8429b0f7a85d"),
      "userid" : "5c35f1045643180d9488112g",
      "pt" : 2,
      "date" : ISODate("2021-02-28T08:17:45.489Z"),
      "wtg" : 0.82,
      "rank" : 2
  },
  {
      "_id" : ObjectId("5fa506a9fe8d8429b0f7a85e"),
      "userid" : "5c35f1045643180d9488112f",
      "pt" : 1,
      "date" : ISODate("2021-02-28T08:17:45.489Z"),
      "wtg" : 0.92,
      "rank" : 1
  },
  {
      "_id" : ObjectId("5fa506a9fe8d8429b0f7a85f"),
      "userid" : "5c35f1045643180d9488112f",
      "pt" : 3,
      "date" : ISODate("2021-02-28T08:17:45.489Z"),
      "wtg" : 21.92,
      "rank" : 1
  },
  {
      "_id" : ObjectId("5fa506a9fe8d8429b0f7a85g"),
      "userid" : "5c35f1045643180d9488112f",
      "pt" : 3,
      "date" : ISODate("2021-02-28T08:17:45.489Z"),
      "wtg" : 10.92,
      "rank" : 2
  },
  {
      "_id" : ObjectId("5fa506a9fe8d8429b0f7a85h"),
      "userid" : "5c35f1045643180d9488112f",
      "pt" : 4,
      "date" : ISODate("2021-02-28T08:17:45.489Z"),
      "wtg" : 1002,
      "rank" : 1
  }
]

I have 4 types of exam i.e pt: 1, pt: 2, pt: 3, pt: 4

Now I want the result of all the users who are on the list along with how many times on that exam user secure rank 1, 2 or 3.

Dummy expected answer: Suppose any user who takes part in the pt: 1 exam and 3 times he secures 1st position, 1 time 2nd position and 5 times he secures 3rd position.

[
  {
    pt: 2,
    users: [
      { uid: '5c35f1045643180d9488112f', rank1: 3, rank2: 1, rank3: 5 },
      { uid: '5c35f1045643180d9488113g', rank1: 0, rank2: 15, rank3: 2 },
    ]
  }
]

What I tried is just for the repetition on behalf of userid. I am confused about how to write the proper query to achieve this :

db.CollectionName.aggregate(
  [
    {
      $facet: {
        groupByPage: [
          { $unwind: '$_id' },
          {
            $group: {
              _id: '$_id',
              count: { $sum: 1 },
            },
          },
        ],
      }
    }
  ]
)

Can anyone guide me with the proper approach and share the steps for understandbility.

aggregation-framework mongodb aggregate
1 Answers

You can use a single $group stage where to sum with a conditional on the rank value, like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        user: "$userid",
        exam: "$pt"
      },
      rank1: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$rank",
                1
              ]
            },
            1,
            0
          ]
        },
        
      },
      rank2: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$rank",
                2
              ]
            },
            1,
            0
          ]
        },
        
      },
      rank3: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$rank",
                3
              ]
            },
            1,
            0
          ]
        },
        
      }
    }
  },
  {
    $group: {
      _id: "$_id.exam",
      users: {
        $push: {
          uud: "$_id.user",
          rank1: "$rank1",
          rank2: "$rank2",
          rank3: "$rank3"
        }
      }
    }
  }
])

Mongo Playground

user_107703700
Related