MongoDB $lookup in another table with matching field as array-CodePudding

I am trying to return true false based value exists in array in other collection.

Let's say my Event collection is as following.

{"_id":{"$oid":"626aa83ca3d690ffe4a40103"},"name":"event1","title":"Event 1"}
{"_id":{"$oid":"626aa83ca3d690ffe4a40104"},"name":"event2","title":"Event 2"}
{"_id":{"$oid":"626aa83ca3d690ffe4a40105"},"name":"event3","title":"Event 3"}

My User Collection is as following:

{"_id":{"$oid":"62149d30950b000a31448eb3"},"userName":"[email protected]","commentDictionary":"{}","__v":0,"createdAt":{"$date":"2022-02-22T08:22:08.161Z"},"updatedAt":{"$date":"2022-04-27T07:11:58.999Z"},"passwordResetLinkExpiryDate":{"$date":"2022-03-22T16:40:15.356Z"},"_eventIds":[{"$oid":"626aa83ca3d690ffe4a40103"},{"$oid":"626aa83ca3d690ffe4a40105"}]}

depending on matching values in _eventIds in User Collection, I am looking for output as following. And that is also in userName having value as [email protected]

{"_id":{"$oid":"626aa83ca3d690ffe4a40103"},"name":"event1","title":"Event 1", attended: true}
{"_id":{"$oid":"626aa83ca3d690ffe4a40104"},"name":"event2","title":"Event 2", attended: false}
{"_id":{"$oid":"626aa83ca3d690ffe4a40105"},"name":"event3","title":"Event 3", attended: true}

I was trying with $lookup but it needs one matching key, but in my case it is Array. So unable to figure logic for this.

CodePudding user response：

Query

lookup works on single value/array, and matches if the array contains this value
instead of doing all the lookup that will be slower, more memory etc, $limit 1 is used, because we only care if match was found or not
if results are empty => attented = false else attended=true

Playmongo

Event.aggregate(
[{"$lookup": 
   {"from": "User",
    "localField": "_id",
    "foreignField": "_eventIds",
    "pipeline": [{"$limit": 1}],
    "as": "attended"}},
 {"$set": 
   {"attended": {"$cond": [{"$eq": ["$attended", []]}, false, true]}}}])