I have 3 collections in mongoDB with the following structure
Users:
"name":""
"email":""
"phone":""
"city":""
"customerID":""
Ships:
"address":""
"city":""
"pincode":""
"porderID":""
"customerID":""
Orders:
"productName":""
"quantity":""
"pricing":""
"mrp":""
"porderID":""
"customerID":""
How do I write a get request or an aggregate function that returns all customerIDs in a json like this?
[{
customerID:"",
BuyOrder:[{
porderID:"",
productName:"",
quantity:""
ShipmentDetail:[
address:""
city:""
pincode:""
]
}],
customerID:"",
BuyOrder:[{
porderID:"",
productName:"",
quantity:""
ShipmentDetail:[
address:""
city:""
pincode:""
]
}],
}]
Any suggestions would be helpful.
Is not quite clear what do you want... without an input/output example, without a valid JSON... is not easy but I think you are looking for something like this:
This query is a nested $lookup where for each user join the collection with orders using the customerID, and also, orders is joined with ships using customerID too.
db.users.aggregate([
{
"$lookup": {
"from": "orders",
"localField": "customerID",
"foreignField": "customerID",
"as": "BuyOrder",
"pipeline": [
{
"$lookup": {
"from": "ships",
"localField": "customerID",
"foreignField": "customerID",
"as": "ShipmentDetail"
}
}
]
}
},
])
Example here
You also can add a $project stage to output only fields you want, like this example where the result is:
[
{
"BuyOrder": [
{
"ShipmentDetail": [
{
"address": "",
"city": "",
"pincode": ""
}
],
"porderID": "",
"pricing": "",
"productName": "",
"quantity": ""
}
],
"customerID": ""
}
]