Return document if only sub-document elements matched within a nested array

Question

The main collection is User, and we have a User profile collection which having experience details and other stuff. Also, we have a Skill collection.

USER

[{
    "_id": "5f1eef8ec68d306fbbf13b0f",
    "name": "John Davis",
    "email": "[email protected]",
    "__v": 0
},
{
    "_id": "9q1eef8ec68d306fbbf13bh6",
    "name": "Mik Luca",
    "email": "[email protected]",
    "__v": 0
}]

User profile

[{
        "_id": "5f1eef8ec68d306fbbf13b10",
        "other_skills": [
            null
        ],
        "user_id": "5f1eef8ec68d306fbbf13b0f",
        "phone_number": "1234569870",
        "location": "5f16b72617fee02922688751",
        "primary_skills": [
            {
                "_id": "5f32635cf764cc40447503a6",
                "years": 1,
                "skill_id": "5f0da75907a96c3040b3667d"
            }
        ]
    },
    {
        "_id": "5f1eef8ec68d306fbbf13b10",
        "other_skills": [
            null
        ],
        "user_id": "9q1eef8ec68d306fbbf13bh6",
        "phone_number": "1234569870",
        "location": "5f16b72617fee02922688751",
        "primary_skills": [
            {
                "_id": "6s0da75907a96c3040b36690",
                "years": 1,
                "skill_id": "5f0da75907a96c3040b3667d"
            }
        ]
    }]

Skill

[{  
    "_id": "5f0da75907a96c3040b3667d",
    "skill": "Mongo"
},
{  
    "_id": "6s0da75907a96c3040b36690",
    "skill": "Node"
}]

I need to list the users with their user profile info and need to filter with skills as well

I have tried

db.getCollection("users").aggregate(
    [
        { 
            "$project" : { 
                "_id" : NumberInt(0), 
                "users" : "$$ROOT"
            }
        }, 
        { 
            "$lookup" : { 
                "localField" : "users._id", 
                "from" : "userprofiles", 
                "foreignField" : "user_id", 
                "as" : "userprofiles"
            }
        }, 
        { 
            "$unwind" : { 
                "path" : "$userprofiles", 
                "preserveNullAndEmptyArrays" : true
            }
        }, 
        { 
            "$lookup" : { 
                "localField" : "userprofiles.primary_skills.skill_id", 
                "from" : "skills", 
                "foreignField" : "_id", 
                "as" : "skills"
            }
        }, 
        { 
            "$unwind" : { 
                "path" : "$skills", 
                "preserveNullAndEmptyArrays" : true
            }
        }, 
        { 
            "$match" : { 
                "skills._id" : ObjectId("5f0dce8d07a96c3040b36687")
            }
        }
    ], 
    { 
        "allowDiskUse" : true
    }
);

But not getting the proper results.

How can I populate the user profile and skill information with the User list and filter the user list with Skill ids?

Greetings and thanks.

Please post your expected result

Kunal Mukherjee
– Kunal Mukherjee

2020-08-22 16:02:22 +00:00
Commented Aug 22, 2020 at 16:02 — Kunal Mukherjee
– Kunal Mukherjee, Commented Aug 22, 2020 at 16:02

turivishal · Accepted Answer · 2020-08-22 05:48:31Z

You can match filters inside lookup using lookup with pipeline,

$lookup with userProfile collection
pipelines $match to match profile id
other filters for profile like skill_id match here
$unwind deconstruct primary_skills array because we are going to lookup with skill_id
$lookup will skills collection
$unwind deconstruct primary_skills.skill_id array because we need it as object
$grpup reconstruct primary_skills array
$match if userProfiles not equal to empty []

db.users.aggregate([
  {
    $lookup: {
      from: "usersProfile",
      let: { id: "$_id" },
      as: "userProfiles",
      pipeline: [
        {
          $match: { 
             $expr: { $eq: ["$$id", "$user_id"] },
             // match here user profile filters
            "primary_skills.skill_id": "5f0da75907a96c3040b3667d"
          }
        },
        { $unwind: "$primary_skills" },
        {
          $lookup: {
            from: "skills",
            localField: "primary_skills.skill_id",
            foreignField: "_id",
            as: "primary_skills.skill_id"
          }
        },
        { $unwind: "$primary_skills.skill_id" },
        {
          $group: {
            _id: "$_id",
            other_skills: { $first: "$other_skills" },
            phone_number: { $first: "$phone_number" },
            location: { $first: "$location" },
            primary_skills: {
              $push: {
                _id: "$primary_skills._id",
                skill: "$primary_skills.skill_id.skill",
                years: "$primary_skills.years"
              }
            }
          }
        }
      ]
    }
  },
  { $match: { userProfiles: { $ne: [] } } }
])

Playground

Thanks, @turivishal for the answer. After applying some tweaks, it's working in my use case.

Collectives™ on Stack Overflow

Return document if only sub-document elements matched within a nested array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related