mongodb aggregation with array and lookup

Question

I have a MongoDB collection with documents in the following format collection name called post

{
    "_id" : ObjectId("5c88b225fac24431d947abad"),
    "user_id" : "5c87ad6c623f1e2bd4d041d0",
    "post_likes" : [ 
        {
            "post_user_id" : "5c87ad6c623f1e2bd4d041d0",
            "like_status" : true
        }, 
        {
            "post_user_id" : "5c88b42b71611926c055508b",
            "like_status" : true
        }
    ],
    "post_comments" : [ 
        {
            "comment_user_id" : "5c87ad6c623f1e2bd4d041d0",
            "comment_like" : "",
            "comment_description" : ""
        }, 
        {
            "comment_user_id" : "5c88b42b71611926c055508b",
            "comment_like" : "",
            "comment_description" : "nice post"
        }
    ]
}

i have another collection name called user_ptofile_info

{
    "_id" : ObjectId("5c923682c088564cf01056cb"),
    "user_id" : "5c87ad6c623f1e2bd4d041d0",
    "image_url" : "image/url",
    "user_name":"xxxxxxxx",
    "created_at" : "",
    "updated_at" : ""
}

requested output like
note:the post_user_id from user_ptofile_info and user_id from post are lookup and i need total count for post_likes and post comments also

{
    "_id" : ObjectId("5c88b225fac24431d947abad"),
    "user_id" : "5c87ad6c623f1e2bd4d041d0",
    "post_likes" : [ 
        {
            "post_user_id" : "5c87ad6c623f1e2bd4d041d0",
            "like_status" : true,
            "image_url" : "image/url",
            "user_name":"xxxxxxxx",
        }, 
        {
            "post_user_id" : "5c88b42b71611926c055508b",
            "like_status" : true,
            "image_url" : "image/url",
            "user_name":"xxxxxxxx",
        }
    ],
    "post_comments" : [ 
        {
            "comment_user_id" : "5c87ad6c623f1e2bd4d041d0",
            "comment_like" : "",
            "comment_description" : ""
        }, 
        {
            "comment_user_id" : "5c88b42b71611926c055508b",
            "comment_like" : "",
            "comment_description" : "nice post"
        }
    ]
}

Could you show what you have tried

Ashh
– Ashh

2019-03-27 13:26:21 +00:00
Commented Mar 27, 2019 at 13:26 — Ashh
– Ashh, Commented Mar 27, 2019 at 13:26

mickl · Accepted Answer · 2019-03-27 14:16:24Z

1

You can use below aggregation:

db.post.aggregate([
    {
        $lookup: {
            from: "user_profile_info",
            let: { user_ids: "$post_likes.post_user_id" },
            pipeline: [
                { $match: { $expr: { $in: [ "$user_id", "$$user_ids" ] } } },
                { 
                    $project: {
                        post_user_id: "$user_id",
                        image_url: 1,
                        user_name: 1                        
                    }  
                }
            ],
            as: "users"
        }
    },
    {
        $project: {
            _id: 1,
            user_id: 1,
            post_likes: {
                $map: {
                    input: "$users",
                    as: "user",
                    in: {
                        post_user_id: "$$user.post_user_id",
                        image_url: "$$user.image_url",
                        user_name: "$$user.user_name",
                        like_status: {
                            $let: { 
                                vars: { 
                                    like: { 
                                        $arrayElemAt: [ 
                                            { $filter: { input: "$post_likes", as: "pl", cond: { $eq: [ "$$pl.post_user_id", "$$user.post_user_id" ] } } }, 0 
                                        ] 
                                    } 
                                },
                                in: "$$like.like_status"
                            }
                        }
                    }
                }
            },
            post_comments: 1
        }
    }
])

$lookup with custom pipeline (MongoDB 3.6 or newer) will allow you to get the data from user_profile_info for all users that are present in post_likes array. Then you need to "merge" users array with post_likes to get like_status. Since you have two arrays and you know that the same post_user_id appears in both of them you can use $map with $arrayElemAt and $filter to combine the data from both arrays.

answered Mar 27, 2019 at 14:16

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

s.chandran sha Over a year ago

hi mickl still i facing some issues like : ** Mongo Server error (MongoCommandException): Command failed with error 40081 (Location40081): '$in requires an array as a second argument, found: missing' on server localhost:27017. ** i thought its happening because of some times the post_likes array may be empty or the key may not present this same condition for post_comments also

mickl Over a year ago

@s.chandransha yes, definitely that's the reason. What should happen for empty post_likes ? Can you filter them out ?

s.chandran sha Over a year ago

i done like this let: { user_ids: {$cond: {if: { $isArray:"$post_likes" },then: "$post_likes.post_user_id",else: []}}} this is working for me is that correct way to approach

mickl Over a year ago

@s.chandransha should be fine

Collectives™ on Stack Overflow

mongodb aggregation with array and lookup

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related