• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

155
Views
Multiple group by in mongodb

I have a documents like that at my mongo database:

{
{"_id" : NumberLong(50),
"offerId" : NumberLong(88),
"isInTryPeriod" : false,
"state" : {
    "currentState" : "INACTIVE",
    "oldState" : "INACTIVE"
},
 ......
 ......
 ......
}

And I want to group them under "offerId" but also i want to see the count isInTryPeriod (for true and false) and currentState (active, inactive etc) number under specific states like as follows:

{{
_id: offerId
states: {
isInTryperiodCountForThatOfferId: X
activeForThatOfferId: X
inactiveForThatOfferId: X
},
{
_id: offerId
states: {
isInTryperiodCountForThatOfferId: X
activeForThatOfferId: X
inactiveForThatOfferId: X
}}

What i tried for that is, I write two query for them they work separately but i want to compose them.

To get count for sate.currentState field

db.subscriptions.aggregate(
  {
    $group: {
      _id:   { offerId: "$offerId", state: "$state.currentState" },
      "state": { "$push": "$state.currentState" },
      "total": { "$sum": 1 }
    }
  },
  {
    $group: {
      _id: { offerId: "$_id.offerId" },
      state: { $addToSet: { state: "$_id.state", sum:"$total" } }
    }
  }
);

To get the count for isInTrpPreiod field

db.subscriptions.aggregate([
  { 
    $match : {isInTryPeriod : true}
  },
  {
    $group: {
      _id:   { offerId: "$offerId", "isInTryPeriod" : "$isInTryPeriod"},
      "isInTryPeriod": { "$push": "$isInTryPeriod" },
      "total": { "$sum": 1 }
    }
  },
  {
    $group: {
      _id: "$_id.offerId",
      isInTryPeriod: { $addToSet: { isInTryPeriod: "$_id.isInTryPeriod", sum:"$total" } }
    }
  }
]);
about 3 years ago · Santiago Trujillo
1 answers
Answer question

0

You should be able to group by offerId and use $cond to sum fields based on a condition.

db.getCollection('subscriptions').aggregate([
{
  $group: {
    _id: { offerId : "$offerId" },
    "totalActiveState": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$state.currentState", "ACTIVE"] }, 1, 0
         ]
       }
     },
    "totalInactiveState": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$state.currentState", "INACTIVE"] }, 1, 0
         ]
       }
     },
    "totalIsInTryPeriod": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$isInTryPeriod", true] }, 1, 0
         ]
       }
     }     
    }
  }
])

This will return sth like the following:

[
{
    "_id" : {
        "offerId" : 77.0
    },
    "totalActiveState" : 1.0,
    "totalInactiveState" : 0.0,
    "totalIsInTryPeriod" : 1.0
},
{
    "_id" : {
        "offerId" : 88.0
    },
    "totalActiveState" : 1.0,
    "totalInactiveState" : 2.0,
    "totalIsInTryPeriod" : 2.0
}
]

Not sure about the performance, but it should give you the results..

about 3 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error