Get total users list along with the active users in mongodb matched on OR condition

Question

Table : 
user: A,
active_1 : "true",
active_2 : "false",
is_user : "true"

user: B,
active_1 : "false",
active_2 : "true",
is_user : "true"

user: C,
active_1 : "false",
active_2 : "false",
is_user : "true"

Expected Output:

{
    "_id" : null,
    "total" : 3,
    "count" : 2
}

I need to check either the active_1 or active_2 is true and get the output as like total which indicates the total no.of users which is A,B and C. The count indicates the users who have either active_1 or active_2 is true. It should check is_user true which is mandatory

The code I have tried:

db.getCollection('mycollections').aggregate([{'$match': 'is_user': 'true'}}, 
    {'$group': {'count': {'$sum': 
        {'$or':[
        {
          "active_1": "true"
        },
        {
          "active_2": "true"
        }
      ]}},
        'total': {'$sum': 1}, '_id': 0}}, 
        {'$project': {'count': '$count', 'total': '$total', '_id': 0}}]
)

Result is count:0 and total : 3. But that is not what I have expected.

score 1 · Accepted Answer · answered Jul 29 '20 at 13:58

You're very close, you just need to use something like $cond to achieve this:

db.collection.aggregate([
  {
    "$group": {
      "count": {
        "$sum": {
          $cond: [
            {
              "$or": [
                {
                  $eq: [
                    "$active_1",
                    "true"
                  ]
                },
                {
                  $eq: [
                    "$active_2",
                    "true"
                  ]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "total": {
        "$sum": 1
      },
      "_id": 0
    }
  },
  
])

MongoPlayground

I have been struggling with this for like 2 hours and you made it simple. Thanks a lot @Tom Slabbaert — Navi, Jul 29 '20 at 14:01

Get total users list along with the active users in mongodb matched on OR condition

1 Answers1

Linked