I am trying to make a query in which I have a db_task
query that contains the task id and the users assigned to it. I have to fetch the user details which are present in db_user collection and add the incoming details into the same document.
db_task
{
"_id" : ObjectId("5d8b522d0cf2579c57bc8ce0"),
"users" : [
{
"user_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
"is_finished" : false
},
{
"user_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
"is_finished" : false
}
]
}
The users field the users who are assigned that task, I want to do a lookup on the db_user
query which would get me the details inside the same embedded document.
db_user
{
"_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
"first_name" : "Harry",
"last_name" : "Paul"
},
{
"_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
"first_name" : "Aaron",
"last_name" : "Potter"
}
I tried to do $lookup on the db_user table with "users.user_id" but that would fetch me a new field and then I tried to concatenate those arrays with "$concatArrays" but the result still wasn't what I expected.
I want to get the output in a format something like this
db_task
{
"_id" : ObjectId("5d8b522d0cf2579c57bc8ce0"),
"users" : [
{
"user_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
"is_finished" : false,
"user_info":{
"first_name" : "Harry",
"last_name" : "Paul"
}
},
{
"user_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
"is_finished" : false,
"user_info":{
"first_name" : "Aaron",
"last_name" : "Potter"
}
}
]
}
Altough they're working, the provided solutions, with unwind and group, can be expensive in resources. Here's a better solution in only two stages :
db.db_task.aggregate([
{
$lookup: {
from: "db_user",
localField: "users.user_id",
foreignField: "_id",
as: "usersInfos"
}
},
{
$project: {
users: {
$map: {
input: "$usersInfos",
as: "ui",
in: {
$mergeObjects: [
"$$ui",
{
$arrayElemAt: [
{
$filter: {
input: "$users",
as: "users",
cond: {
$eq: [
"$$users.user_id",
"$$ui._id"
]
}
}
},
0
]
}
]
}
}
}
}
}
])
Will output
[
{
"_id": ObjectId("5d8b522d0cf2579c57bc8ce0"),
"users": [
{
"_id": ObjectId("5d6f6d25e079b9fb7d858236"),
"first_name": "Aaron",
"is_finished": false,
"last_name": "Potter",
"user_id": ObjectId("5d6f6d25e079b9fb7d858236")
},
{
"_id": ObjectId("5d8b522d0cf2579e27bc8ce3"),
"first_name": "Harry",
"is_finished": true,
"last_name": "Paul",
"user_id": ObjectId("5d8b522d0cf2579e27bc8ce3")
}
]
}
]
Note : as proposed by @Valijon, you can add a $project stage if you need to slighty re-arrange from here.