Query documents in collection A having field in the result of collection B query

Question

I want to get all messages from userA given Messages collection and Room (i.e conversation) but can not find a way to do it similar to sql

The logic is quite simple in sql: just find any room having userA in it and then all messages belonging to the roomId above. I tried this query but it does not work. I also tried $lookup but failed

   db.getCollection("message").find(
     { 
         "roomId" :{
             "$in" : db.getCollection("room").find(
                 { 
                     "usernames" : "userA", 
                     "msgs" : {
                         "$gt" : 20.0
                     }
                 }, 
                 { 
                     "_id" : 1
                 }
             )
         }
     }, 
     { 
         "msg" : 1, 
         "u" : 1, 
         "roomId" : 1, 
         "ts" : 1
     }
 ).sort(        {             "ts" : 1       }    );

Answer 1

The following query can get you the expected output:

db.messages.aggreagte([
    {
        $lookup:{
            "from":"room",
            "let":{
                "roomId":"$roomId"
            },
            "pipeline":[
                {
                    $match:{    
                        "usernames": "userA",
                        "msgs":{
                            $gt: 20
                        },
                        $expr:{
                            $eq:["$_id","$$roomId"]
                        }
                    }
                },
                {
                    $limit:1
                },
                {
                    $project:{
                        "_id":0,
                        "found": true
                    }
                }
            ],
            "as":"roomLookup"
        }
    },
    {
        $unwind: "$roomLookup"
    },
    {
        $project:{ 
             "msg" : 1, 
             "u" : 1, 
             "roomId" : 1, 
             "ts" : 1
         }
    },
    {
        $sort:{ 
            "ts": 1
        }
    }
]).pretty()

Note: We are limiting the output in lookup as we don't need anything from there. Also, the unwind stage would only pass when the lookup is successful.