Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB Aggregation Lookup Match Array

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

like image 413
Julian Avatar asked Feb 22 '18 19:02

Julian


1 Answers

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"
  }}
])
like image 155
s7vr Avatar answered Oct 03 '22 00:10

s7vr