My claim_rules collection looks like this
[
{
"_id": {"$oid": "66c3118a234ecb42936c6dba"},
"ramo_sx": 1,
"tipo_sx": 700201
},
{
"_id": {"$oid": "66c3118a234ecb42936c6dbb"},
"ramo_sx": 1,
"tipo_sx": 700202
},
{
"_id": {"$oid": "66c3118a234ecb42936c6dbc"},
"ramo_sx": 1,
"tipo_sx": 700208
},
{
"_id": {"$oid": "66c3118a234ecb42936c6dbd"},
"ramo_sx": 1,
"tipo_sx": 700210
},
{
"_id": {"$oid": "66c3118a234ecb42936c6dbe"},
"ramo_sx": 1,
"tipo_sx": 700219
},
{
"_id": {"$oid": "66c3118a234ecb42936c6dbf"},
"ramo_sx": 1,
"tipo_sx": 900102
},
...
I am trying to lookup only for specific ramo_sx and tipo_sx fields using a lookup stage with a pipeline like this
{
$lookup: {
from: "claim_rules",
let: { ramo_sx: "$vda.ramo_sx", tipo_sx: "$vda.tipo_sx" },
pipeline: [
{
$project: {
_id: 0,
}
},
{
$match: {
ramo_sx: "$$ramo_sx",
tipo_sx: "$$tipo_sx"
}
}
],
as: "claim_rules"
}
}
The vda variable is taken from this collection:
[
{
"_id": "1-8101-2023-0445722",
"vda": {
"ramo_sx": 5,
"tipo_sx": 31
}
},
{
"_id": "1-8101-2023-0447269",
"vda": {
"ramo_sx": 39,
"tipo_sx": 22
}
},
...
The problem is that the resulting claim_rules is empty
According to the docs, you must use $expr operator to access the variable. Your $lookup pipeline should as below:
{
$lookup: {
from: "claim_rules",
let: {
ramo_sx: "$vda.ramo_sx",
tipo_sx: "$vda.tipo_sx"
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: [
"$ramo_sx",
"$$ramo_sx"
]
},
{
$eq: [
"$tipo_sx",
"$$tipo_sx"
]
}
]
}
}
},
{
$project: {
_id: 0
}
}
],
as: "claim_rules"
}
}
Also, it is recommended to place the $match stage as the first stage to utilize the index and have better query performance.