MongoDB aggregate return count of documents or 0

user_2135819 0
Viewed 1868

I have the following aggregate query:

db.user.aggregate()
    .match({"business_account_id" : ObjectId("5e3377bcb1dbae5124e4b6bf")})
    .lookup({
        'localField': 'profile_id',
        'from': 'profile',
        'foreignField' : '_id',
        'as': 'profile'
    })
    .unwind("$profile")
    .match({"profile.type" : "consultant"})
    .group({_id:"$business_account_id", count:{$sum:1}})

My goal is to count how many consultant users belong to a given company.

Using the query above, if there is at least one user belonging to the provided business_account_id I get a correct count value.

But if there are none users, the .match({"business_account_id" : ObjectId("5e3377bcb1dbae5124e4b6bf")}) will return an empty (0 documents) result.

How can I get a count: 0 if the there are no users assigned to the company ?

I tried many approach based on other threads but I coundn't get a count: 0

UPDATE 1

A simple version of my problem:

user collection

{
    "_id" : ObjectId("5e36beb7b1dbae5124e4b6dc"),
    "business_account_id" : ObjectId("5e3377bcb1dbae5124e4b6bf"),
},
{
    "_id" : ObjectId("5e36d83db1dbae5124e4b732"),
    "business_account_id" : ObjectId("5e3377bcb1dbae5124e4b6bf"),
}

Using the following aggregate query:

db.getCollection("user").aggregate([
        { "$match" : { 
                "business_account_id" : ObjectId("5e3377bcb1dbae5124e4b6bf")
            }
        }, 
        { "$group" : { 
                "_id" : "$business_account_id", 
                "count" : { "$sum" : 1 }
            }
        }
]);

I get:

{ 
    "_id" : ObjectId("5e3377bcb1dbae5124e4b6bf"), 
    "count" : 2
}

But if I query for an ObjectId that doesn't exist, such as:

db.getCollection("user").aggregate([
        { "$match" : { 
                "business_account_id" : ObjectId("5e335c873e8d40676928656d")
            }
        }, 
        { "$group" : { 
                "_id" : "$business_account_id", 
                "count" : { "$sum" : 1 }
            }
        }
]);

I get an result completely empty. I would expect to get:

{ 
    "_id" : ObjectId("5e335c873e8d40676928656d"), 
    "count" : 0
}
mongodb-query mongodb
2 Answers

The root of the problem is if there is no document in the user collection that satisfies the initial $match there is nothing to pass to the next stage of the pipeline. If the business_account_id actually exists somewhere (perhaps another collection?) run the aggregation against that collection so that the initial match finds at least one document. Then use $lookup to find the users. If you are using MongoDB 3.6+, you can might combine the user and profile lookups. Lastly, use $size to count the elements in the users array.

(You will probably need to tweak the collection and field names)

db.businesses.aggregate([
    {$match:{_id : ObjectId("5e3377bcb1dbae5124e4b6bf")}},
    {$project: { _id:1 }},
    {$lookup:{
          from: "users",
          let: {"busId":"$_id"},
          as: "users",
          pipeline: [
               {$match: {$expr:{$eq:[
                                "$$busId",
                                "$business_account_id"
               ]}}},
               {$lookup:{
                    localField: "profile_id",
                    from: "profile",
                    foreignField : "_id",
                    as: "profile"
               }},
               {$match: { "profile.type" : "consultant"}}
          ]
     }},
     {$project: {
           _id: 0,
           business_account_id: "$_id",
           count:{$size:"$users"} 
     }}
])

Playground

user_22826340

Since you match non-existing business_account_id value, aggregation process will stop.

Workaround: We perform 2 aggregations in parallel with $facet operator to get default value if matching has no result.

Note: Make sure user collection has at least 1 record, otherwise this won't work

db.user.aggregate([
  {
    $facet: {
      not_found: [
        {
          $project: {
            "_id": ObjectId("5e3377bcb1dbae5124e4b6bf"),
            "count": { $const: 0 }
          }
        },
        {
          $limit: 1
        }
      ],
      found: [
        {
          "$match": {
            "business_account_id": ObjectId("5e3377bcb1dbae5124e4b6bf")
          }
        },
        {
          "$group": {
            "_id": "$business_account_id",
            "count": { "$sum": 1 }
          }
        }
      ]
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            $arrayElemAt: ["$not_found", 0]
          },
          {
            $arrayElemAt: ["$found", 0]
          }
        ]
      }
    }
  }
])

MongoPlayground

user_37104900
Related