I want to write a $lookup query example User.aggregate([{ $match }]).lookup({... to list users with extra key _team where the user is assigned as a member in any of the team. This is the example mongo collection -

1st Collection

# Users Collection - 
{
    "_id": "1",
    "name": "Sankhnad"
},
{
    "_id": "2",
    "name": "Shweta"
}

2nd Collection

# Teams Collection - 
{
    "_id": "1",
    "name": "Rock Team",
    "_members": [
        2
    ]
},
{
    "_id": "2",
    "name": "Star Team",,
    "_members": [
        1,
    ]
},
{
    "_id": "3",
    "name": "Yeh Team",
    "_members": [
        1,
        2
    ]
}

Now I want a single $lookup / aggregate query on the users and teams collection to list all users and their respective teams or a selected user and its teams where the user has been assigned in the _members.

The expected output for all users -

# Output - 
{
    "_id": "1",
    "name": "Sankhnad",
    "_teams": [
        {
            "_id": "2",
            "name": "Star Team",,
            "_members": [
                1,
            ]
        },
        {
            "_id": "3",
            "name": "Yeh Team",
            "_members": [
                1,
                2
            ]
        }
    ]
},
{
    "_id": "2",
    "name": "Shweta",
    "_teams": [
        {
            "_id": "1",
            "name": "Rock Team",
            "_members": [
                2
            ]
        },
        {
            "_id": "3",
            "name": "Yeh Team",
            "_members": [
                1,
                2
            ]
        }
    ]
}

I really thank all of you for the support.

CodePudding user response：

We can do something like below -

db.users.aggregate([
{$match: {deleted: false}}, // Put condition in user collection if needed
{
    $lookup: {
        from: "teams",
        localField: "_id",
        foreignField: "_members",
        as: "team"
    }
},
{$unwind: { path: "$team", preserveNullAndEmptyArrays: true}},
    {
        $project: { // To show only the records that you want
        "_id": 1,
        "name": 1,
        "team._id": 1,
        "team.name": 1,
        "team._members": 1
        }
    }
]);

CodePudding user response：

Query

• lookup can work on array/single value, and lookup happens if the array contains that value. (works like query $eq operator)
• because you have the "_id" in string and in array you have it as numbers $toInt is used also

Test code here

users.aggregate(
[{"$set":{"nid":{"$toInt":"$_id"}}},
 {"$lookup":
  {"from":"teams",
   "localField":"nid",
   "foreignField":"_members",
   "as":"_teams"}},
 {"$unset":["nid"]}])

CodePudding user response：

You can use $lookup as follow from the users table as you want the associated teams of the users.

Note: The _members id should be string not int32 for lookup. If it would not be string than type conversion is necessary.

db.users.aggregate([$lookup:{
  from: 'teams',
  localField: '_id',
  foreignField: '_members',
  as: 'teams'
}])

The same query using the pipeline of the lookup would like

db.users.aggregate[{
  from: 'teams',
 let:{ids:"$_id"},
 pipeline:[{
  $match:{
    $expr:{
    $in:["$$ids","$_members"]
  }
  }
 }],
  as: '_teams'
}])