Mongodb lookup with array

Question

I have two collections first one is

user_profile collection

const userProfileSchema = mongoose.Schema({
  
  phone_number: {
    type: String,
    required: false,
  },
  primary_skills: [
    {
      skill_id: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Skill'
      },
      years: Number,
    }
  ]
});

sample data

{
  "phone_number":"222",
   "primary_skills":[{skill_id:1,years:12},{skill_id:2,years:13}]
}

in the primary_skills the key skill_id is mapped with another collection named skills

skills collection

const skillSchema = mongoose.Schema({
  name: {
    type: String,
    required: true,
    unique:true,
  },
});

sample data

[
   {
   id:1,
   name:'php'
   },
  {
   id:2,
   name:'java'
  }
]

I want to fetch all values in the user_profile collection along with the respective skills name

expected output:

{
 "phone_number":"222",
 "primary_skills":[{
    name:"php",skill_id:1,years:12
 },{
  name:"java",skill_id:2,years:13}
]
}

I found a similar thread to my question MongoDB lookup when foreign field is an array of objects but it's doing the opposite of what I want

This is the query I tried

profile.aggregate([{
     $lookup:{
        from:'skills',
        localField:'primary_skills.skill_id',
        foreignField:'_id',
        'as':'primary_skills'
      }
   
}])

This works fine but it didn't contain the years key

score 1 · Accepted Answer · answered Aug 05 '20 at 16:39

You need to do it with $unwind and $group,

$unwind primary_skills because its an array and we need to lookup sub document wise

db.user_profile.aggregate([
  {
    $unwind: "$primary_skills"
  },

$lookup to join primary_skills, that you have already did

  {
    $lookup: {
      from: "skills",
      localField: "primary_skills.skill_id",
      foreignField: "id",
      as: "primary_skills.name"
    }
  },

$unwind primary_skills.name that we have stored join result, its array and we are unwinding to do object

  {
    $unwind: {
      path: "$primary_skills.name"
    }
  },

$addFields replace field name that we have object and we need only name

  {
    $addFields: {
      "primary_skills.name": "$primary_skills.name.name"
    }
  },

$group by _id because we have unwind and we need to combine all documents

  {
    $group: {
      _id: "$_id",
      phone_number: {
        $first: "$phone_number"
      },
      primary_skills: {
        $push: "$primary_skills"
      }
    }
  }
])

Playground: https://mongoplayground.net/p/bDmrOwmASn5

Mongodb lookup with array

1 Answers1