I have two collections, a collection with users and a collection with groups. In groups
the documents look like this:
{
"_id": ObjectId("...")
"members": [ObjectId("..."), ObjectId("..."), ObjectId("...")]
}
In the users
collection the documents look like this:
{
"_id": ObjectId("...")
"first_name": "...",
"last_name": "...",
"phone": "..."
}
What I want to do is an aggregation in the users
collection where I add a $lookup
stage to the pipeline where I go inside the groups
collection and get the group from the user based on whether the _id
from the user document is inside the members
array in the group document.
I have the following but I don't know how I'd proceed.
db.users.aggregate([
{
"$match": { "_id": ObjectId("...") }
},
{
"$lookup": {
"from": "groups",
"let": {"_id": "$_id"},
"pipeline": [{"$match": ???}], <- match when $$_id is inside $members
"as": "group"
}
]);
I know that the aggregation framework in MongoDB doesn't work with arrays and that there is an $unwind
stage but I don't really know how I would combine $unwind
and $match
to get the result I want.
What I want:
{
"_id": ObjectId("..."),
"group": ObjectId("..."),
(other fields here)
}
I use MongoDB server version 3.7.2
You can try below aggregation one of two ways using $lookup
and $lookup
pipeline.
$lookup
db.users.aggregate([
{"$match":{"_id":ObjectId(..)}},
{"$lookup":{
"from":"groups",
"localField":"_id",
"foreignField":"members",
"as":"group"
}},
{"$unwind":"$group"},
{"$unwind":"$group.members"},
{"$match":{"group.members":ObjectId(..)}}
])
$lookup pipeline
db.users.aggregate([
{"$match":{"_id":ObjectId(..)}},
{"$lookup":{
"from":"groups",
"let":{"_id":"$_id"},
"pipeline":[
{"$match":{"$expr":{"$in":["$$_id","$members"]}}},
{"$unwind":"$members"},
{"$match":{"$expr":{"$eq":["$$_id","$members"]}}}
],
"as":"members"
}}
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With