How to populate a nested field after lookup using lookup mongodb?

Working with Data Node.js Frameworks
, ,
1

question, answer and profile modals

 question = {
      _id: "00000",
      question: "",
      category: "gaming"
    }
    
    answer = {
      _id: "xyz",
      answer: "some answer",
      questionId: "00000",
      userID: "11111"
    }
    
    profile = {
      _id: "11111",
      name: "alex",
    }

what i want to achieve:

  {
      _id: "00000",
      question: "",
      category: "gaming",
      usersAnswered: [
        { _id: "xyz",
          answer: "some answer",
          questionId: "00000",
          userID: {
            _id: "11111",
            name: "alex",
          } 
        }
      ]
    }

i want to populate the userID field which we get from $lookup

   question
      .aggregate([
        {
          $match: {
            category: "gaming",
          },
        },
        {
          $lookup: {
            from: "answers",
            localField: "_id",
            foreignField: "questionID",
            as: "usersAnswered",
          },
        },
      ])

what should be done to further populate the userID field using $lookup i guess.

Just a bit new to aggregation framework, been using mongoose to get the job done but then this situation came up and had to integrate a field from another collection in question model i.e. usersAnswered so i am learning aggregation framework it gets a bit confusing.

3

The first thing is that you had foreignField:questionID rather than foreignField:questionId which was producing an empty usersAnswered.

The $lookup is smart enough to it over arrays so the next stage is simply

$lookup: {
            from: "profiles",
            localField: "usersAnswered.userID",
            foreignField: "_id",
            as: "profiles",
          }

This will not be in the exact format you want as the profile data will not be inside the usersAnswered array but will be at the root. Something like (using extra data to show features) :

{ _id: '00000',
  question: '',
  category: 'gaming',
  usersAnswered: 
   [ { _id: 'xyz',
       answer: 'some answer',
       questionId: '00000',
       userID: '11111' },
     { _id: 0,
       answer: 'some answer',
       questionId: '00000',
       userID: '11111' },
     { _id: 1,
       answer: 'some answer',
       questionId: '00000',
       userID: 222 } ],
  profiles: [ { _id: 222, name: 'alex' }, { _id: '11111', name: 'alex' } ] }

As you can see user _id:11111 answered the question twice (may not be possible in your use-case) but it’s profile was produced only once.

To get your final result you may have a $set stage that $map usersAnswered using $filter on profiles. But personally, I prefer to do this kind of data cosmetic in the application layer to offload as much as possible the data access layer.