I have two collections:
Games with schema:
_id: ObjectId('gameId'),
questions: [
{
position: 1,
question_id: ObjectId('baz')
},
{
position: 2,
question_id: ObjectId('ban')
},
]
Questions with schema:
_id: ObjectId('baz'),
text: 'FooBar'
And now I'd like to join questions to games with adding to each question record value of question_position
.
So, I have query like this:
db.games.aggregate([
{
$lookup: {
from: 'questions',
localField: 'questions.question_id',
foreignField: '_id',
as: 'question_data',
},
}])
Which return me all required info, with correct join according to questions array,
_id: ObjectId('gameId'),
questions: [
{
position: 1,
question_id: ObjectId('baz')
},
{
position: 2,
question_id: ObjectId('ban')
}
],
question_data: [
{
_id: ObjectId('baz'),
text: 'FooBar',
},
{
_id: ObjectId('ban'),
text: 'FooBar2',
}
]
but I'm totally can't figure out how to add into joined questions it's position according to game. To look it like this:
_id: ObjectId('gameId'),
questions: [
{
position: 1,
question_id: ObjectId('baz')
},
{
position: 2,
question_id: ObjectId('ban')
}
],
question_data: [
{
_id: ObjectId('baz'),
text: 'FooBar',
position: 1,
},
{
_id: ObjectId('ban'),
text: 'FooBar2',
position: 2,
}
]
I've tried with $unwind
for question array in game collection, played a little with $project
in aggregation
but still no result.
So, my question is, how to add field from base collection to joined data from another collection