Mongodb aggregation filter lookup on parent property

Question

Given an object like this, how can I make it lookup users info only if the isAnonymous field is false?

{ 
"_id" : ObjectId(""), 
"user" : ObjectId(""), 
"title" : "This is an idea", 
"shortDescription" : "Pretty cool stuff",
"downVoteCount" : NumberInt(0), 
"upVoteCount" : NumberInt(12), 
"voteTotalCount" : NumberInt(12), 
"deleted" : false, 
"ideaNumber" : NumberInt(5),
"isAnonymous" : false, 
"created" : ISODate("2018-01-19T23:37:10.949+0000")
}

Here is my current query:

db.ideas.aggregate(
{
    $match: { "deleted": false }
},
{
    $lookup: {
        "from" : "users",
        "localField" : "user",
        "foreignField" : "_id",
        "as" : "user"
    }
},
{
    $unwind: {
        path : "$user",
        includeArrayIndex : "arrayIndex", // optional
        preserveNullAndEmptyArrays : false // optional
    }
},
{
    $project: {
        "ideaNumber": 1,
        "title": 1,
        "isAnonymous": 1,
        "shortDescription": 1,
        "upVoteCount": 1,
        "created": 1,
        "user.email": 1,
        "user.name": 1,
        "user.createdAt": 1
    }
},
);

Yields an object like this:

{ 
"_id" : ObjectId(""), 
"user" : {
    "createdAt" : ISODate("2018-01-19T21:50:02.758+0000"), 
    "email" : "blah", 
    "name" : "Foo Bar"
}, 
"title" : "This is an idea", 
"shortDescription" : "Pretty cool stuff", 
"upVoteCount" : NumberInt(12), 
"ideaNumber" : NumberInt(5), 
"isAnonymous" : false, 
"created" : ISODate("2018-01-19T23:37:10.949+0000")

}

I'd like to see the object like this instead

{ 
"_id" : ObjectId(""), 
"user" : {
    "createdAt" : "anonymous", 
    "email" : "anonymous", 
    "name" : "anonymous"
}, 
"title" : "This is an idea", 
"shortDescription" : "Pretty cool stuff", 
"upVoteCount" : NumberInt(12), 
"ideaNumber" : NumberInt(5), 
"isAnonymous" : false, 
"created" : ISODate("2018-01-19T23:37:10.949+0000")
}

So when anonymous is false you you need user info no ? Just trying to see what the problem is. Btw if you are looking for a conditional lookup it is not possible. — s7vr, Feb 16 '18 at 22:37
@Veeram conditional lookup are available in v3.6, I don't have experience in this version but you can found it in the documentation: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#specify-multiple-join-conditions-with-lookup — Raul Rueda, Feb 16 '18 at 23:59
@RaulRueda How is it possible ? The way I'm thinking there should be expression which decides whether lookup should be performed or not. Can you guys me an example ? 3.6 has is a join on multiple criteria. — s7vr, Feb 17 '18 at 00:21
Please see my answer down below, especially `projection` part of query — Saleem, Feb 17 '18 at 00:22
@Saleem conditional lookup as in using condition in $lookup operator to determine whether to execute lookup or not . What did I miss ? — s7vr, Feb 17 '18 at 00:24
@Veeram, no you didn't missed anything. it's just about how to achieve desired result using available options. — Saleem, Feb 17 '18 at 00:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165312/discussion-between-saleem-and-veeram). — Saleem, Feb 17 '18 at 00:28
The conditional $lookup it's totally different from applying filters in subsequent stages like in $projection or $match. Please read the link I posted that have plenty examples of how they work and what properties they have. — Raul Rueda, Feb 17 '18 at 00:36

score 1 · Answer 1 · answered Feb 16 '18 at 23:56

You can use a $cond sentence for projection fields:

db.ideas.aggregate(
    {
        $match: { "deleted": false }
    },
    {
        $lookup: {
            "from": "users",
            "localField": "user",
            "foreignField": "_id",
            "as": "user"
        }
    },
    {
        $unwind: {
            path: "$user",
            includeArrayIndex: "arrayIndex", // optional
            preserveNullAndEmptyArrays: false // optional
        }
    },
    {
        $project: {
            "ideaNumber": 1,
            "title": 1,
            "isAnonymous": 1,
            "shortDescription": 1,
            "upVoteCount": 1,
            "created": 1,
            "user.email": {
                $cond: {
                    if: { $eq: ["$isAnonymous", true] },
                    then: "anonymous",
                    else: "$user.email"
                }
            },
            "user.name": 1,
            "user.createdAt": 1
        }
    },
);

Of course, you should apply this to the other 2 fields. If you don't want even show them of the result, use a filter $match for the last stage.

{
    $match: { isAnonymous: { $ne: "anonymous" } }
}

score 1 · Accepted Answer · answered Feb 17 '18 at 00:13

Indeed you can use conditionals in aggregation pipeline to alter projection.

Following query should project in desired format.

db.T.aggregate([
{
    $match: { "deleted": false }
},
{
    $lookup: {
        "from" : "users",
        "localField" : "user",
        "foreignField" : "_id",
        "as" : "user"
    }
},
{
    $unwind: {
        path : "$user",
        includeArrayIndex : "arrayIndex", // optional
        preserveNullAndEmptyArrays : false // optional
    }
},
{ 
    $project:{
        "ideaNumber": 1,
        "title": 1,
        "isAnonymous": 1,
        "shortDescription": 1,
        "upVoteCount": 1,
        "created": 1,
         user : {$cond: [{$eq:["$isAnonymous", true]}, 
         {
            "createdAt" : "anonymous", 
            "email" : "anonymous", 
            "name" : "anonymous"
         }, 
         {
            "createdAt" : "$user.createdAt", 
            "email" : "$user.email", 
            "name" : "$user.name"
         }]}
    }
}]);

Above query leads to result as:

{ 
    "_id" : ObjectId("5a876fae304c013cbf854766"), 
    "title" : "This is an idea", 
    "shortDescription" : "Pretty cool stuff", 
    "upVoteCount" : NumberInt(12), 
    "ideaNumber" : NumberInt(5), 
    "isAnonymous" : false, 
    "created" : ISODate("2018-01-19T23:37:10.949+0000"), 
    "user" : {
        "createdAt" : ISODate("2018-01-19T23:37:10.949+0000"), 
        "email" : "abc@cde.com", 
        "name" : "user A"
    }
}

Mongodb aggregation filter lookup on parent property

2 Answers2