Aggregate sum with lookup to another collection

Question

I have two schemas, one is User the other is Pet. The Pet schema has information that I want to $sum inside the User.aggregate pipeline.

Mongo version is 3.4.1.

User schema:

pets: [{type: Schema.Types.ObjectId, ref: 'Pets'}]

Pets schema:

      owner: {type: Schema.Types.ObjectId, ref: 'User'},
      petLost: {
       lost            : {type: Boolean, default: false},
       lostDate        : {type: String},
       selectedComRange: {type: Number, default: 5},
       circumstances   : {type: String},
       extraInfoLost   : {type: String},
       rewardCheck     : {type: Boolean, default: false},
       reward          : {type: String},
       addressLost     : {type: String},
      }

This is the lookup:

{'$unwind': '$pets'},
    {
      '$lookup': {
        'from'        : 'pets',
        'localField'  : '_id',
        'foreignField': '_id',
        'as'          : 'lostPets'
      }
    },
{'$unwind': {path: '$lostPets', preserveNullAndEmptyArrays:true}}

And here's the condition i'm trying to achieve:

'lostPet_count': {
          '$sum': {
            '$cond': [{'$eq': ['$lostPets.lost', true]}, 1, 0]
          }
        } 

Aggregate pipeline:

User.aggregate([
        {
          $group: {
            _id              : null,
            'users_count'    : {
              '$sum': {
                '$cond': [{'$eq': ['$role', 'user']}, 1, 0]
              }
            },
            'volunteer_count': {
              '$sum': {
                '$cond': [{'$eq': ['$isVolunteer', true]}, 1, 0]
              }
            },
            'pet_count'      : {
              '$sum': {
                $size: '$pets'
              }
            },
            'lost_pet'       : {
              '$sum': {
                **// how can i call another collection and $sum some data?**
              }
            }
          }
        },
        {
          '$project': {
            '_id'       : 0, 'role': '$_id',
            'statistics': {
              'users'     : '$users_count',
              'volunteers': '$volunteer_count',
              'pets'      : '$pet_count'
            }
          }
        }
      ]).exec((err, result) => {
        if (err) {
          console.log(err);
        }
        res.status(200).json(result);
      });

How can I inject info into 'lost_pet' property from the Pet schema ?

Answer 1

The following pipeline should return the desired results:

Pets.aggregate([
    {
        '$lookup': {
            'from': 'users',
            'localField': 'owner',
            'foreignField': '_id',
            'as': 'users'
        }
    },
    { '$unwind': { 'path': '$users', 'preserveNullAndEmptyArrays': true } },
    {
        '$group': {
            '_id': '$users._id',
            'users_count': {
                '$sum': {
                    '$cond': [{'$eq': ['$users.role', 'user']}, 1, 0]
                }
            },
            'volunteer_count'   : {
                '$sum': {
                    '$cond': ['$users.isVolunteer', 1, 0]
                }
            },
            'pet_count': { '$sum': 1 },
            'lost_pets': {
                '$sum': {
                    '$cond': ['$petLost.lost', 1, 0]
                }
            }       
        }
    },
    {
        '$project': {
            '_id': 0, 'role': '$_id',
            'statistics': {
                'users': '$users_count',
                'volunteers': '$volunteer_count',
                'pets': '$pet_count',
                'lostpets': '$lost_pets'
            }
        }
    }
]).exec((err, result) => {
    if (err) {
        console.log(err);
    }
    res.status(200).json(result);
});

---

Or aggregating from the User model as

User.aggregate([
    { '$unwind': '$pets' },
    {
        '$lookup': {
            'from': 'pets',
            'localField': 'pets',
            'foreignField': '_id',
            'as': 'pets'
        }
    },
    { '$unwind': { 'path': '$pets', 'preserveNullAndEmptyArrays': true } }
    {
        '$group': {
            '_id': '$_id',
            'role': { '$first': '$role' },
            'isVolunteer': { '$first': '$isVolunteer' },
            'pet_count': { '$sum': 1 },
            'lost_pets': {
                '$sum': {
                    '$cond': ['$pets.petLost.lost', 1, 0]
                }
            }   
        }
    },
    {
        '$group': {
            '_id': null,
            'users_count': {
                '$sum': {
                    '$cond': [{'$eq': ['$role', 'user']}, 1, 0]
                }
            },
            'volunteer_count'   : {
                '$sum': {
                    '$cond': ['$isVolunteer', 1, 0]
                }
            },
            'pet_count': { '$sum': '$pet_count' },
            'lost_pets': { '$sum': '$lost_pets' }   
        }
    },
    {
      '$project': {
        '_id': 0, 'role': '$_id',
        'statistics': {
            'users': '$users_count',
            'volunteers': '$volunteer_count',
            'pets': '$pet_count',
            'lostpets': '$lost_pets'
        }
      }
    }
]).exec((err, result) => {
    if (err) {
        console.log(err);
    }
    res.status(200).json(result);
});