How to do multiple joins between two collection in

Question

I have two collections 1) user_posts 2)user_profile. find the below collection data for your reference.

1) user_posts collection

_id :ObjectId("5d519f861c9d4400005ebd1b")
userid : ObjectId("5d518caed55bc00001d235c1")
media : "hello.jpg"
type : "jpg"
created : " "
modified : " "
like : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           status : "like"
       1 : Object
           userid : ObjectId("5d518da6d55bc00001d235c2")
           status : "happy"
comment : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           comment : "hello"
       1 : Object
           userid : ObjectId("5d518da6d55bc00001d235c2")
           comment : "welcome"
share : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           status : "shared"
       1 : Object
           userid : ObjectId("5d518da6d55bc00001d235c2")
           status : "shared"

2) User_profile collection

 _id : ObjectId("5d518caed55bc00001d235c1")
 username : "ramesh",
 photo :  " ",
 created : " ",
 modified : " "

 _id : ObjectId("5d518da6d55bc00001d235c2")
 username : "shekar",
 photo :  " ",
 created : " ",
 modified : " "

Now i tried to get the profile details from user_profile in lambda function. but i didn't get the details. find the below lambda function code.

def lambda_handler(event, context):

print("Received event: " + json.dumps(event, indent=1))

user_posts = db.user_posts

Userid = event['userid']
uid = ObjectId(Userid)

dispost = list(user_posts.aggregate([{
"$match" : { "userid" : uid }
},
{ "$graphLookup" : 
     {
       "from" : "user_profile",
       "startWith" : "$like.userid",
       "connectFromField" : "like.userid",
       "connectToField" : "_id",
       "as" : "userdetails"
     }
},
{ "$graphLookup" : 
     {
       "from" : "user_profile",
       "startWith" : "$comment.userid",
       "connectFromField" : "comment.userid",
       "connectToField" : "_id",
       "as" : "userdetails1"
     }
}
{ "$graphLookup" : 
     {
       "from" : "user_profile",
       "startWith" : "$share.userid",
       "connectFromField" : "share.userid",
       "connectToField" : "_id",
       "as" : "userdetails2"
     }
}
]))     
disair = json.dumps(dispost, default=json_util.default)
return json.loads(disair)

but i didn't get the output. i need output like this below.

_id :ObjectId("5d519f861c9d4400005ebd1b")
userid : ObjectId("5d518caed55bc00001d235c1")
username : "ramesh"
photo : " ",
media : "hello.jpg"
type : "jpg"
created : " "
modified : " "
like : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           status : "like"
           username : "ramesh"
           photo : " "
       1 : Object
           username : "shekar"
           photo : " "
           userid : ObjectId("5d518da6d55bc00001d235c2")
           status : "happy"
           username : "shekar"
           photo : " "
comment : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           comment : "hello"
           username : "ramesh"
           photo : " "
       1 : Object
           userid : ObjectId("5d518da6d55bc00001d235c2")
           comment : "welocme"
           username : "shekar"
           photo : " "
share : Array
       0 : Object
           userid : ObjectId("5d518caed55bc00001d235c1")
           status : "shared"
           username : "ramesh"
           photo : " "
       1 : Object
           userid : ObjectId("5d518da6d55bc00001d235c2")
           status : "shared"
           username : "shekar"
           photo : " "

can you please help me the solutions. Thanks in advance.

Answer 1

Please check this:

db.collection("user_posts").aggregate(
{ $match: {"userid" : uid}},
{ $unwind: '$like' },
{ $lookup: { from: "users", localField: "like.userid", foreignField: "_id", as: 
"users" }},
{ $group: {
    _id: "$_id",
    like: { $push: { $mergeObjects: ['$like', { $arrayElemAt: [ "$users", 0 ] } ]}},
    data: { $first: "$$ROOT" }
}},
{ $replaceRoot: { newRoot: { $mergeObjects: ['$data', { like: "$like"} ]} } },
{ $unwind: '$comment' },
{ $lookup: { from: "users", localField: "comment.userid", foreignField: "_id", as: 
 "users" }},
{ $group: {
    _id: "$_id",
        comment: { $push: { $mergeObjects: ['$comment', { $arrayElemAt: [ "$users", 0 
 ] } ]}},
        data: { $first: "$$ROOT" }
}},
{ $replaceRoot: { newRoot: { $mergeObjects: ['$data', { comment: "$comment"} ]} } },
{ $unwind: '$share' },
{ $lookup: { from: "users", localField: "share.userid", foreignField: "_id", as: 
"users" }},
{ $group: {
    _id: "$_id",
    share: { $push: { $mergeObjects: ['$share', { $arrayElemAt: [ "$users", 0 ] } 
]}},
    data: { $first: "$$ROOT" }
}},
{ $replaceRoot: { newRoot: { $mergeObjects: ['$data', { share: "$share"} ]} } },
{ $project: { users: 0 }}
)

you will get output, changes add/remove fields name in project aggregation as per your requirement

Answer 2

Please check this :

    db.user_posts.aggregate([{ $match: { _id: ObjectId("5d519f861c9d4400005ebd1b") } }, {
    $lookup:
    {
        from: "user_profile",
        let: { userIdToBeCompared: "$userid", like: '$like', comment: '$comment', share: '$share' },
        pipeline: [
            {
                $match:
                {
                    $expr:
                    {

                        $or:
                            [
                                { $eq: ["$_id", "$$userIdToBeCompared"] },
                                { $in: ["$_id", "$$like.userid"] },
                                { $in: ["$_id", "$$comment.userid"] },
                                { $in: ["$_id", "$$share.userid"] }
                            ]
                    }
                }
            }

        ],
        as: "data"
    }
}])

Ok there are way too many things has to happen in your requirement and with your current data structure it might not be an easy thing & a good idea to implement all your needs from your DB layer, So over here final response data will have all the mappings for your needs, get it to your code and fetch the needed details to each section based on userid, as of what I've tried I thought that would be ideal scenario to implement !!