Here is the output that I am getting
{
"_id": "614ed24fe42a63ba4c41b4ab",
"service_id": [
"613b3a234aeeea1991bbe075",
"613b3a234aeeea1991bbe075",
"613b3a4d4aeeea1991bbe07e"
],
"review_given": "false",
"customer_id": "613b2a89b77e2315c71ba8c0",
"date": "2021-05-07T18:30:00.000Z",
"time": "2:15 PM",
"Staff_id": "613b468e884dd11e63bd3f7a",
"saloon_user_id": "60ed77c6e0cce2756bcacac1",
"payment_status": "pending",
"payment_mode": "cash",
"amount": "50",
"createdOn": "2021-09-25T07:39:59.658Z",
"__v": 0,
"userRole": [],
"saloon_services": []
}
Now, what I need to find is the data related to each service id, if it is a single service id as string it is working fine. But Now I am confused how to proceeds forward. Below is the lookup if it is a single service id as string.
Bookings.aggregate([
{"$match":{"customer_id":new mongoose.Types.ObjectId(req.body.user_id)}},
{
$lookup: {
from: "users",
localField: "saloon_user_id",
foreignField: "_id",
as: "userRole"
}
},
{
$lookup: {
from: "owner_servicedatas",
let: {
id: "$service_id" //localField
},
pipeline: [{ $match: {$expr:{$eq:["$$id","$_id" ] } }},
{
$lookup: {
from: "owner_staffdatas",
let: { "portId": "$staff_id" },
pipeline: [
{ $match: {$expr:{$eq:["$$portId","$_id" ] } }},
],
"as": "staff"
}}
],
as: "saloon_services"
}
}
Any help would be appreciated. Sample result can be like this, there will three services listed with each of the staff associated to services.
{
"_id": "6144e5db0b8a00090f84fc23",
"review_given": "true",
"saloon_user_id": "613b12e4fd3e6d0a2ac49bc7",
"customer_id": "613b2a89b77e2315c71ba8c0",
"service_id": "613b3a4d4aeeea1991bbe07e",
"date": "2021-09-20T18:30:00.000Z",
"amount": "100",
"time": "9:30 AM",
"payment_status": "pending",
"payment_mode": "cash",
"createdOn": "2021-09-17T19:00:43.839Z",
"__v": 0,
"booking_status": "Completed",
"saloon_services": [
{
"_id": "613b3a4d4aeeea1991bbe07e",
"service_description": "hair salon market place",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"category_id": "613b39c04aeeea1991bbe059",
"staff_id": "613b468e884dd11e63bd3f7a",
"service_name": "hair dressing",
"service_duration": "45 min",
"service_price": "100",
"service_type": "Fixed",
"createdOn": "2021-09-10T10:58:21.482Z",
"__v": 0,
"staff": [
{
"_id": "613b468e884dd11e63bd3f7a",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"staff_name": "Harish",
"staff_job_position": "Manager",
"staff_phone_number": 85025585844,
"staff_email": "[email protected]",
"createdOn": "2021-09-10T11:50:38.144Z",
"__v": 0,
"staff_photo": "Johan.png"
}
]
},
{
"_id": "613b3a4d4aeeea1991bbe07e",
"service_description": "hair salon market place",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"category_id": "613b39c04aeeea1991bbe059",
"staff_id": "613b468e884dd11e63bd3f7a",
"service_name": "hair dressing",
"service_duration": "45 min",
"service_price": "100",
"service_type": "Fixed",
"createdOn": "2021-09-10T10:58:21.482Z",
"__v": 0,
"staff": [
{
"_id": "613b468e884dd11e63bd3f7a",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"staff_name": "Harish",
"staff_job_position": "Manager",
"staff_phone_number": 85025585844,
"staff_email": "[email protected]",
"createdOn": "2021-09-10T11:50:38.144Z",
"__v": 0,
"staff_photo": "Johan.png"
}
]
},
{
"_id": "613b3a4d4aeeea1991bbe07e",
"service_description": "hair salon market place",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"category_id": "613b39c04aeeea1991bbe059",
"staff_id": "613b468e884dd11e63bd3f7a",
"service_name": "hair dressing",
"service_duration": "45 min",
"service_price": "100",
"service_type": "Fixed",
"createdOn": "2021-09-10T10:58:21.482Z",
"__v": 0,
"staff": [
{
"_id": "613b468e884dd11e63bd3f7a",
"user_id": "613b12e4fd3e6d0a2ac49bc7",
"staff_name": "Harish",
"staff_job_position": "Manager",
"staff_phone_number": 85025585844,
"staff_email": "[email protected]",
"createdOn": "2021-09-10T11:50:38.144Z",
"__v": 0,
"staff_photo": "Johan.png"
}
]
}
]
}
Thanks
CodePudding user response：
Lookup inside a lookup is not a great way to do things. You should work on your schema modeling so that these situation doesn't come. Obviously, it will come from experience.
Anyway, as your service_id is an array of mongo ids, the let variable id below
let: {
id: "$service_id" //localField
},
will also have the array.
Stage: 1
So you have to use $in, below is the sample code:-
{
$lookup: {
from: "owner_servicedatas",
let: { service_ids: "$service_id" },
pipeline: [
{
$match: {
$expr: {
$in: ["$_id", "$$service_ids"],
},
},
},
],
as: "saloon_services",
},
}
Stage: 2
Now you will get the services mongo document in an array. Now instead of writing lookup inside the above lookup, we will write another pipeline stage. Before that, we need to $unwind the saloon_services. So that instead of an array of mongo doc we will have the plain mongo doc
{
$unwind: "$saloon_services"
},
The above code will create the duplicate of the root document, that we will solve at the end of the stage with $group.
Stage: 3
After this, we will write the lookup for staff
{
$lookup: {
from: "owner_staffdatas",
let: { staffId: "$saloon_services.staff" },
pipeline: [
{ $match: { $expr: { $eq: ["$_id", "$$staffId"] } } },
],
as: "staff"
},
},
The above code will give you an array of staff and add the staff array in the root mongo doc but we want it to be mongo doc no the array and also we want it to be inside of saloon_services mongo doc.
Stage: 4 So to achieve that, first, we have to unwind the staff
{
$unwind: "$staff"
},
Stage: 5 Now add that staff mongo doc into saloon_services
{
$addFields: {
"saloon_services.staff": "$staff"
}
},
Stage: 6 Now we have to get rid of the duplicates which I have mentioned at the end of stage 2
{
$group: {
_id: "$_id",
"saloon_services_array":{ $push: "$saloon_services"},
review_given: { $first: "$review_given" },
// similar to review_given above you can add the other fields of root document
}
}
Each stage is independent. You can copy-paste each one after the other to see how the data looks like after each stage.
Below is the complete code
Bookings.aggregate([
{ $match: { customer_id: new mongoose.Types.ObjectId(req.body.user_id) } },
{
$lookup: {
from: "users",
localField: "saloon_user_id",
foreignField: "_id",
as: "userRole",
},
},
{
$lookup: {
from: "owner_servicedatas",
let: { service_ids: "$service_id" },
pipeline: [
{
$match: {
$expr: {
$in: ["$_id", "$$service_ids"],
},
},
},
],
as: "saloon_services",
},
},
{
$unwind: "$saloon_services",
},
{
$lookup: {
from: "owner_staffdatas",
let: { staffId: "$saloon_services.staff" },
pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$staffId"] } } }],
as: "staff",
},
},
{
$unwind: "$staff",
},
{
$addFields: {
"saloon_services.staff": "$staff",
},
},
{
$group: {
_id: "$_id",
saloon_services_array: { $push: "$saloon_services" },
review_given: { $first: "$review_given" },
// similar to review_given above you can add the other fields of root document
},
},
]);
If anything I missed or it doesn't work do let me know. I hope this solves your problem. Happy coding.