CODEWITHSUNDEEP
mongodbaggregation-framework
Hải Trung Phạm
Hải Trung Phạm

Reputation: 89

Do count with condition inside mongDB aggregation's function

I have 2 collections inside my MongoDB:

Order:
{
    "date" : ISODate("2020-07-30T22:00:00.000Z"),
    "item" : "banana",
    "price": "4$",
    "order_type" : "INTERNATIONAL", // There are 2 order types: INTERNATIONAL and MAINLAND
    "user" : {                   // I use a dbref to the User collection
        "$ref" : "user",
        "$id" : "user_0"
    }
}
 
User:
{
    "_id": "user_0"
    "login" : "user1",
    "password" : "$2a$10$mE.qmcV0mFU5NcKh73TZx.z4ueI/.bDWbj0T1BYyqP481kGGarKLG",
    "first_name" : "Henry",
    "last_name" : "Windsor",
    "email" : "[email protected]",
}

Each order contains a DB reference to the corresponding user who made it. This is my mongo code to calculate the total number of orders that each user makes.

db.getCollection('order').aggregate([
    {$group: {
        _id: '$user',
        totalNbOfOrders: {$sum: 1}
    }},
    {$addFields: {foreign_key: {$objectToArray: "$_id"}}},
    {$lookup: {
        from: 'user',
        localField: 'foreign_key.1.v',
        foreignField: '_id',
        as: 'userInfo'
        }
    },
    { $unwind: '$userInfo'},
    { $project: {
        '_id': 0,
        'first_name': '$userInfo.first_name',
        'last_name': '$userInfo.last_name',
        'totalNbOfOrders': '$totalNbOfOrders'
        }
    }
])

And the result is:

    /* 1 */
    {
        "first_name" : "John",
        "last_name" : "Kennedy",
        "totalNbOfOrders" : 2.0
    }
    
    /* 2 */
    {
        "first_name" : "Peter",
        "last_name" : "Parker",
        "totalNbOfOrders" : 4.0
    }
    
    /* 3 */
    {
        "first_name" : "Bruce",
        "last_name" : "Banner",
        "totalNbOfOrders" : 2.0
    }

Now, what I also want to calculate is the number of international orders (and eventually of mainland orders) that each user made to have something like this:

{
    "first_name" : "Tony",
    "last_name" : "Stark",
    "totalNbOfOrders" : 10.0,
    "totalNbOfInternationalOrders": 4.0
    "totalNbOfMainlandOrders": 6.0
}

I haven't figured out how to write the code. I tried to use "$accumulator" operator (new feature in version 4.4 of MongoDB) inside "$group" but I used MongoDB 4.2.7, I have to use operators from older versions to accomplish this. Does anybody know how to solve this problem?

Upvotes: 1

Views: 73

Answers (1)

turivishal
turivishal

Reputation: 36114

You can do it inside $group, using $cond and $eq,

  {
    $group: {
      ... // skipped

      // INTERNATIONAL COUNT
      totalNbOfInternationalOrders: {
        $sum: {
          $cond: {
            if: {
              $eq: ["$order_type", "INTERNATIONAL"]
            },
            then: 1,
            else: 0
          }
        }
      },

      // MAINLAND COUNT
      totalNbOfMainlandOrders: {
        $sum: {
          $cond: {
            if: {
              $eq: ["$order_type", "MAINLAND"]
            },
            then: 1,
            else: 0
          }
        }
      }
    }
  },
  • set in $project
  {
    $project: {
      ... // skipped

      "totalNbOfInternationalOrders": "$totalNbOfInternationalOrders",
      "totalNbOfMainlandOrders": "$totalNbOfMainlandOrders"
    }
  }

Playground: https://mongoplayground.net/p/_IeVcSFt_nY

Upvotes: 1

Related Questions