CODEWITHSUNDEEP
mongodbmongoose
Ridge Robinson
Ridge Robinson

Reputation: 758

$lookup for id inside array of objects

I have some models that look like this:

const exerciseDetailSchema = new mongoose.Schema(
    {
        Translations: {
            Name: {
                en: { type: String, required: true },
                no: { type: String }
            }
        }
    }
);

const workoutDetailSchema = new mongoose.Schema(
    {
        Sets: {
            Exercises: [ WorkoutExercises ],
            Units: { type: String }
        }
    }
);

const WorkoutSets = new mongoose.Schema({
    Reps: { type: Number },
    Weight: { type: Number }
});

const WorkoutExercises = new mongoose.Schema({
    ExerciseId: { type: String, required: true },
    Sets: [ WorkoutSets ]
});

Basically, workouts are made of Sets, which contain some metadata including exercises. These exercises is an array which is made of workout sets.

I am trying to make a query that will return to me the Workout Details including the Exercise Name, so that it can look like this:

{
   _id: "5f60dc1069c27c015ede4e3e",
   Sets: {
      Units: 'metric',
      Exercises: [
         {
            _id: "5f60dc1069c27c015ede4e3e",
            ExerciseId: "5f60c3b7f93d8e00a1cdf414",
            ExerciseName: {
               en: "Squat",
               no: "Knebøy"
            },
            Sets: [
               { _id: "5f60dc1069c27c015ede4e3f", Reps: 10 },
               { _id: "5f60dc1069c27c015ede4e40", Reps: 20 }
            ]
         }
      ]
   }
}

This would be with an example exercise like:

{
   _id: "5f60c3b7f93d8e00a1cdf414",
   Translations: {
      Name: {
         en: "Squat",
         no: "Knebøy"
      }
   }
}

I have tried using a $lookup, like this:

const workout = await WorkoutDetail.aggregate([
        {
            $lookup: {
                from: ExerciseDetail.collection.name,
                localField: "Sets.Exercises.ExerciseId",
                foreignField: "_id",
                as: "ExerciseDetail"
            }
        }
]);

But it is always returning with just: ExerciseDetail: [] for the part that is supposed to be "joined". Can anyone provide any help with how to query this properly?

UPDATE

I have asked in another question here about grouping this data, and received an answer that had done it perfectly. Hopefully it can be helpful to others also: MongoDB $group (mongo playground)

Upvotes: 0

Views: 153

Answers (1)

varman
varman

Reputation: 8894

Since the Exercises is an array, you need to $unwind to flat the array.

[
  {
    $unwind: "$Sets.Exercises"
  },
  {
    $lookup: {
      from: "exerciseDetailSchema",
      localField: "Sets.Exercises.ExerciseId",
      foreignField: "_id",
      as: "ExerciseDetail"
    }
  }
]

Note : To restructure you need to use $group. I've not shown since you had a problem with lookup

Working Mongo playground

Upvotes: 1

Related Questions