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

ray · Answer 1 · 2021-09-26T05:41:55.057

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.

edited Sep 26 '21 at 05:41

answered Sep 26 '21 at 01:04

ray

11,310
7
18
42

That would have worked, except DocumentDB doesn't support $reduce either :( The limited support makes everything more difficult. https://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/mongo-apis.html – MarcB Sep 26 '21 at 04:37
update the answer to use `$filter` , which should be supported by AWS document DB – ray Sep 26 '21 at 05:42
Apparently DocDB doesn't allow $expressions ($filter) in field names in $project - and it looks like $addFields is counted as $project. This is so frustrating... I know I can just do two queries from my app, but I feel like the DB should be able to deal with this. I really appreciate your help =) – MarcB Sep 26 '21 at 06:13

score 0 · Answer 2 · answered Apr 27 '22 at 11:48

0

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

answered Apr 27 '22 at 11:48

Mihai A

351
1
4

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

2 Answers2