Mongodb aggregate query to get data based on a list obtained from another collection

Question

I have two collections. Users and Clients

Companies:

{"_id":{"$oid":"599d8864a5304114ac9b8a0a"},"id":"company1","name":"Big Company", "users":["Jack", "Martin"]}
{"_id":{"$oid":"599d8864a5304114ac9b8a0b"},"id":"company2","name":"Super Big Company", "users":["Kate", "Jack"]}

Users:

{"_id":{"$oid":"59b0178ca5304101c1a99711"},"username":"Jack","hashedPassword":"2f1f147e358f28a74cd29c7f44eedd4ec0293d6c"},
{"_id":{"$oid":"59b0178ca5304101c1a99811"},"username":"Martin","hashedPassword":"1f5f147e358f21a74cd29c7f44eedd4ec0293d6c"}

Having one username I would like to get a list of Users from the Companies this user is a member of.

So the flow would be like this:

Find all companies which have Jack in the list of their users -> extract and join arrays -> Return the list of User objects with matching username from the array

I know it should be possible to get this in one query using aggregate function and $lookup, but I've failed miserably trying to make it work.

Answer 1

db.Clients.aggregate([
   {
      "$lookup":
         {
            "from": "Users",
            "localField": "users",
            "foreignField": "username",
            "as": "user_details"
        }
   },
  

{"$match": {"users":"Jack"}},
{"$unwind": "$user_details" },
{"$project": {"user_details" :1,"_id":0}}
])