I am trying to $lookup against a master table from my main table to get the Status From the master table by using the Status ID. The Master table has multiple types of statuses, of that, I am able to filter to required master data but i am not able to get the match to the right master data value.
My Main Table:
db.Orders.insert([
{
"_id" : ObjectId("61404c6a3231ea22259be204"),
"userId" : 10000030,
"orderStatus" : {
"statusId" : 1
}
}
])
Lookup Master Table:
db.masterdata.insert([
{
"_id" : ObjectId("61388ccb8426124d580fae74"),
"name" : "orderStatus",
"data" : [
{
"key" : "1",
"value" : "Processed"
},
{
"key" : "2",
"value" : "Cancelled"
}
]
},
{
"_id" : ObjectId("61388ccb8426124d580fae75"),
"name" : "shippingStatus",
"data" : [
{
"key" : "1",
"value" : "Shipped"
},
{
"key" : "2",
"value" : "Delivered"
}
]
}
])
This is my aggregate lookup query which is returning both the status values instead of only one relevant status:
db.Orders.aggregate([
{
$match : {userId : 10000030 }
},
{
$lookup:
{
from: "masterdata",
let: { status_id: "$orderStatus.statusId"},
pipeline:
[
{
$match:
{ $expr:
{ $and:
[
{ $eq: [ "$name", "orderStatus" ] }
,
{ $eq: [ "$data.key", "$$status_id" ] }
]
}
}
},
{ $project: { _id: 0,name:1,"data.key":1,"data.value":1 } }
],
as: "lookupval"
}
},
{
$project : {
_id:0,userId : 1,"orderStatus.statusId" : 1,"lookupval":1
}
}
]).pretty()
The equality operator does not seem to be getting the exact match on the Status.
Can someone help?