How to apply condition on $lookup result in mongoDB?

Question

With the reference of my previous question, I have a question about $lookup with add some conditions. You may get enough reference about question from below link description.

Photo:

{_id: 1, photo_name: '1.jpg', photo_description: 'description 1', album_id: 1, flag:1 },
{_id: 2, photo_name: '2.jpg', photo_description: 'description 2', album_id: 1, flag:1 },
{_id: 3, photo_name: '3.jpg', photo_description: 'description 3', album_id: 1, flag:1 },
{_id: 4, photo_name: '4.jpg', photo_description: 'description 4', album_id: 2, flag:0 },
{_id: 5, photo_name: '5.jpg', photo_description: 'description 5', album_id: 2, flag:0 },
{_id: 6, photo_name: '6.jpg', photo_description: 'description 6', album_id: 2}

Album:

{_id: 1, album_name: "my album 1", album_description: "album description 1", emoji_id: 1},
{_id: 2, album_name: "my album 2", album_description: "album description 2", emoji_id: 2},
{_id: 3, album_name: "my album 3", album_description: "album description 3", emoji_id: 3},
{_id: 4, album_name: "my album 4", album_description: "album description 4", emoji_id: 4},
{_id: 5, album_name: "my album 5", album_description: "album description 5", emoji_id: 5}

Emoji:

{_id: 1, emoji_name: "1.jpg"},  
{_id: 2, emoji_name: "2.jpg"},  
{_id: 3, emoji_name: "3.jpg"},  
{_id: 4, emoji_name: "4.jpg"},  
{_id: 5, emoji_name: "5.jpg"},  
{_id: 6, emoji_name: "6.jpg"},  
{_id: 7, emoji_name: "7.jpg"},  
{_id: 8, emoji_name: "8.jpg"}

Testing record pagination :

2

Suppose I add one another field of flag in photo collection and now I want to get count only those photos whose flag is one.

I tried to add $match immediately after $lookup in query, but it fails, It doesn't exclude photos whose flag=0 and also in counter it does not flag condition.

Present Output There are 3 photos out of 10 photos having set flag 0. And we could not consider those photos whose flag is 0. So expected total is 7 photos but count returns 10 photos though I applied condition of flag in photos.

Present Query:

 db
.album
.aggregate([
  {
     $lookup:{
        from:"photo",
        localField:"_id",
        foreignField:"album_id",
        as:"photo"
     }
  },
  {
     $match:{
        "photo.flag": 1
     }
  },
  {
     $lookup:{
        from:"emoji",
        localField:"album_emoji",
        foreignField:"_id",
        as:"emoji"
     }
  },
  {
     $project:{
        album_name:"$album_name",
        album_description:"$album_description",
        album_emoji:"$emoji.image_name",
        photo:"$photo",
        total_photos: {$size: "$photo"}
     }
  }
])
.toArray();

Expected output:

[
    {
        "_id" : 1,
        "album_name" : "Album 1",
        "album_description" : "Album description 1",
        "album_emoji" : [
            "1.jpg"
        ],
        "total_photos" : 3,
        "photo" : [
            {
                "_id" : 1,
                "album_id" : 1,
                "photo_description" : "description 1",
                "photo_name" : "1.jpg",             
                "flag" : 0,
            },
            {
                "_id" : 2,
                "album_id" : 1,
                "photo_description" : "description 2",
                "photo_name" : "2.jpg",             
                "flag" : 0,
            },
            {
                "_id" : 1,
                "album_id" : 1,
                "photo_description" : "description 3",
                "photo_name" : "3.jpg",             
                "flag" : 0,
            }
        ]
    }
]

Present output:

[
    {
        "_id" : 1,
        "album_name" : "Album 1",
        "album_description" : "Album description 1",
        "album_emoji" : [
            "1.jpg"
        ],
        "total_photos" : 5,
        "photo" : [
            {
                "_id" : 1,
                "album_id" : 1,
                "photo_description" : "description 1",
                "photo_name" : "1.jpg",             
                "flag" : 1,
            },
            {
                "_id" : 2,
                "album_id" : 1,
                "photo_description" : "description 2",
                "photo_name" : "2.jpg",             
                "flag" : 1,
            },
            {
                "_id" : 3,
                "album_id" : 1,
                "photo_description" : "description 3",
                "photo_name" : "3.jpg",             
                "flag" : 1,
            },
            {
                "_id" : 4,
                "album_id" : 1,
                "photo_description" : "description 4",
                "photo_name" : "4.jpg",             
                "flag" : 0,
            },
            {
                "_id" : 5,
                "album_id" : 1,
                "photo_description" : "description 5",
                "photo_name" : "5.jpg",             
                "flag" : 0,
            }
        ]
    }
]

It does exclude the photos where "photo.flag": 0 for me. Are you using MongoDB 3.2. — notionquest, Aug 15 '16 at 12:27
@notionquest: Yes I use MongoDB3.2 and also this query work me.. but for me it doesn't exclude recods and does not give proper result of total photos counter. — Dipak, Aug 15 '16 at 13:11
Would you be kind enough to add your expected output from those sample documents? — chridam, Aug 15 '16 at 13:12
@Chridam: Yes I update question please review again whole question. — Dipak, Aug 15 '16 at 13:18
Something is not adding up here; you are filtering on `photo.flag = 1` yet in your expected output the `photos` array has `"flag" : 0` — chridam, Aug 15 '16 at 13:27

Darryl Fabian · Answer 1 · 2017-07-12T05:28:03.460

You can't use "$match" with a object method after "$lookup", because return value of "$lookup" are array values. You better add "$unwind" function after the look up then group it.

Example Query

 db
.album
.aggregate([
    {
        $lookup:{
            from:"photo",
            localField:"_id",
            foreignField:"album_id",
            as:"photo"
        }
     },
    {
        preserveNullAndEmptyArrays : true,
        path : "$photo"
    },
    {
        $match:{
            "photo.flag": 1
        }
     },
     {
        $group : {
            _id : {
                id : "$_id",
                album_name: "$album_name",
                album_description: "$album_description",
                emoji_id: "$emoji_id"
            },
            photo: {
                $push : "$photo"
            }
        }
     }
    {
         $lookup:{
            from:"emoji",
            localField:"_id.album_emoji",
            foreignField:"_id",
            as:"emoji"
         }
    },
    {
         $project:{
            album_name:"$album_name",
            album_description:"$album_description",
            emoji:"$emoji",
            photo:"$photo",
            total_photos: {$size: "$photo"}
         }
    }
])

Or use "$filter".

db
.album
.aggregate([
    {
        $lookup:{
            from:"photo",
            localField:"_id",
            foreignField:"album_id",
            as:"photo"
        }
     },
    {
        $project: {
            id : "$_id",
            album_name: "$album_name",
            album_description: "$album_description",
            emoji_id: "$emoji_id",
            photo: {
                $filter : {
                    input: "$photo",
                    as : "photo_field",
                    cond : {
                        $eq: ["$$photo_field.flag",1]
                    }
                }
            }
        }
    },
    {
         $lookup:{
            from:"emoji",
            localField:"album_emoji",
            foreignField:"_id",
            as:"emoji"
         }
    },
    {
         $project:{
            album_name:"$album_name",
            album_description:"$album_description",
            emoji:"$emoji",
            photo:"$photo",
            total_photos: {$size: "$photo"}
         }
    }
])

You are missing the `$unwind` definition in the second stage. — Adam Reis, Nov 12 '17 at 19:51
@AdamReis, No, Based on his/her example, photo should be array, `$lookup` is return array then i just filtered it by **flag**. — Darryl Fabian, Aug 13 '18 at 02:31

score 2 · Answer 2 · edited Jul 02 '18 at 20:27

Your query is wrong. What is localField:"album_emoji", there is no field named album_emoji in Album collection. The field name is album_id in Album collection which is a foreign key to _id in Emoji collection.

If you are using version 3.5, the correct query is (I assume Album, Photo, Emoji are the names of three collections):

 db.Album.aggregate([
  {
   $lookup:{
    from:"Photo",
    localField:"_id",
    foreignField:"album_id",
    as:"photo"
 }
},
{
 $match:{
    "photo.flag": 1
 }
},
{
 $lookup:{
    from:"Emoji",
    localField:"emoji_id",
    foreignField:"_id",
    as:"emoji"
 }
},
{ 
 $project:{
    album_name:"$album_name",
    album_description:"$album_description",
    album_emoji:"$emoji.emoji_name",
    photo:"$photo",
    total_photos: {$size: "$photo"}
 }
}

]).pretty()

And the result is as follows:

/* 1 */
{
 "_id" : 1,
 "album_name" : "my album 1",
 "album_description" : "album description 1",
 "album_emoji" : [
    "1.jpg"
 ],
 "photo" : [
    {
        "_id" : 1,
        "photo_name" : "1.jpg",
        "photo_description" : "description 1",
        "album_id" : 1,
        "flag" : 1
    },
    {
        "_id" : 2,
        "photo_name" : "2.jpg",
        "photo_description" : "description 2",
        "album_id" : 1,
        "flag" : 1
    },
    {
        "_id" : 3,
        "photo_name" : "3.jpg",
        "photo_description" : "description 3",
        "album_id" : 1,
        "flag" : 1
    }
 ],
 "total_photos" : 3
}

this answer looks really good, BUT unfortunately fails if there's nothing matched in the photo array. The reference to "photo.flag" in the post-lookup match fails as the photo array is empty. — Andy Lorenz, May 13 '20 at 16:36
The resolution is to use $filter instead, which I used inside an $addFields to simply replace the original array with the filtered array based on a condition. $filter just returns an empty array when it acts on an array that's already empty — Andy Lorenz, May 13 '20 at 17:08

How to apply condition on $lookup result in mongoDB?

2 Answers2

Linked