Counting $lookup and $unwind documents filtered with $match without getting rid of parent document when all results match

Question

I have a collection "Owners" and I want to return a list of "Owner" matching a filter (any filter), plus the count of "Pet" from the "Pets" collection for that owner, except I don't want the dead pets. (made up example)

I need the returned documents to look exactly like an "Owner" document with the addition of the "petCount" field because I'm using Java Pojos with the Mongo Java driver.

I'm using AWS DocumentDB that does not support $lookup with filters yet. If it did I would use this and I'd be done:

        db.Owners.aggregate( [
            { $match: {_id: UUID("b13e733d-2686-4266-a686-d3dae6501887")} },
            { $lookup: { from: 'Pets', as: 'pets', 'let': { ownerId: '$_id' }, pipeline: [ { $match: { $expr: { $ne: ['$state', 'DEAD'] } } } ] } },
            { $addFields: { petCount: { $size: '$pets' } } },
            { $project: { pets: 0 } }
        ]).pretty()

But since it doesn't this is what I got so far:

        db.Owners.aggregate( [
            { $match: {_id: { $in: [ UUID("cbb921f6-50f8-4b0c-833f-934998e5fbff") ] } } },
            { $lookup: { from: 'Pets', localField: '_id', foreignField: 'ownerId', as: 'pets' } },
            { $unwind: { path: '$pets', preserveNullAndEmptyArrays: true } },
            { $match: { 'pets.state': { $ne: 'DEAD' } } },
            { "$group": {
                "_id": "$_id",
                "doc": { "$first": "$$ROOT" },
                "pets": { "$push": "$pets" }
                }
            },
            { $addFields: { "doc.petCount": { $size: '$pets' } } },
            { $replaceRoot: { "newRoot": "$doc" } },
            { $project: { pets: 0 } }
        ]).pretty()

This works perfectly, except if an Owner only has "DEAD" pets, then the owner doesn't get returned because all the "document copies" got filtered out by the $match. I'd need the parent document to be returned with petCount = 0 when ALL of them are "DEAD". I cannot figure out how to do this.

Any ideas?

These are the supported operations for DocDB 4.0 https://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/mongo-apis.html

Answer 1

EDIT: update to use $filter as $reduce not supported by aws document DB

You can use $filter to keep only not DEAD pets in the lookup array, then count the size of the remaining array.

Here is the Mongo playground for your reference.

---

$reduce version

You can use $reduce in your aggregation pipeline to to a conditional sum for the state.

Here is Mongo playground for your reference.

Answer 2

As of January 2022, Amazon DocumentDB added support for $reduce, the solution posted above should work for you. Reference.