CODEWITHSUNDEEP
mongodbaggregation-framework
qqilihq
qqilihq

Reputation: 11454

Count _id occurrences in other collection

We have a DB structure similar to the following:

Pet owners:

/* 1 */
{
    "_id" : ObjectId("5baa8b8ce70dcbe59d7f1a32"),
    "name" : "bob"
}

/* 2 */
{
    "_id" : ObjectId("5baa8b8ee70dcbe59d7f1a33"),
    "name" : "mary"
}

Pets:

/* 1 */
{
    "_id" : ObjectId("5baa8b4fe70dcbe59d7f1a2a"),
    "name" : "max",
    "owner" : ObjectId("5baa8b8ce70dcbe59d7f1a32")
}

/* 2 */
{
    "_id" : ObjectId("5baa8b52e70dcbe59d7f1a2b"),
    "name" : "charlie",
    "owner" : ObjectId("5baa8b8ce70dcbe59d7f1a32")
}

/* 3 */
{
    "_id" : ObjectId("5baa8b53e70dcbe59d7f1a2c"),
    "name" : "buddy",
    "owner" : ObjectId("5baa8b8ee70dcbe59d7f1a33")
}

I need a list of all pet owners and additionally the number of pets they own. Our current query looks similar to the following:

db.getCollection('owners').aggregate([
    { $lookup: { from: 'pets', localField: '_id', foreignField: 'owner', as: 'pets' } },
    { $project: { '_id': 1, name: 1, numPets: { $size: '$pets' } } }
]);

This works, however it's quite slow and I'm asking myself if there's a more efficient way to perform the query?

[update and feedback] Thanks for the answers. The solutions work, however I can unfortunately see no performance improvement compared to the query given above. Obviously, MongoDB still needs to scan the entire pet collection. My hope was, that the owner index (which is present) on the pets collection could somehow be exploited for getting just the counts (not needing to touch the pet documents), but this does not seem to be the case.

Are there any other ideas or solutions for a very fast retrieval of the 'pet count' beside explicitly storing the count within the owner documents?

Upvotes: 3

Views: 1499

Answers (2)

Ashh
Ashh

Reputation: 46451

You can try below aggregation

db.owners.aggregate([
  { "$lookup": {
    "from": "pets",
    "let": { "ownerId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$$ownerId", "$owner" ] }}},
      { "$count": "count" }
    ],
    "as": "numPets"
  }},
  { "$project": {
    "_id": 1,
    "name": 1,
    "numPets": { "$ifNull": [{ "$arrayElemAt": ["$numPets.count", 0] }, 0]}
  }}
])

Upvotes: 1

mickl
mickl

Reputation: 49945

In MongoDB 3.6 you can create custom $lookup pipeline and count instead of entire pets documents, try:

db.owners.aggregate([
    {
        $lookup: {
            from: "pets",
            let: { ownerId: "$_id" },
            pipeline: [
                { $match: { $expr: { $eq: [ "$$ownerId", "$owner" ] } } },
                { $count: "count" }
            ],
            as: "numPets"
        }
    },
    {
        $unwind: "$numPets"
    }
])

Upvotes: 4

Related Questions