Hey i’m trying to create a similiar to sql inner join query with mongo.
My hotel schema
{
“code”: 1,
“name”: {
“content”: “…”
},
“description”: {
…
“countryCode”: “ES”,
“stateCode”: “43”,
…,
“facilities”: [
{
“facilityCode”: 70,
“facilityGroupCode”: 10,
“order”: 1,
“indYesOrNo”: false,
“number”: 260,
“voucher”: false
},
{
“facilityCode”: 50,
“facilityGroupCode”: 10,
“order”: 1,
“number”: 5,
“voucher”: false
}
]
}
My facility schema
{
“code”: 1,
“facilityGroupCode”: 61,
“facilityTypologyCode”: 20,
“description”: {
“languageCode”: “CAS”,
“content”: “text…”
}
},
{
“code”: 1,
“facilityGroupCode”: 62,
“facilityTypologyCode”: 20,
“description”: {
“languageCode”: “CAS”,
“content”: “more text…”
}
}
I need to join the description content into the array of facilities. The foreign keys are both facilityCode and facilityGroupCode. I have tried:
{
from: ‘facility’,
let: {code: “$code”, groupCode: “$facilityGroupCode”},
pipeline: [
{
$match: {
$expr: {
$and: [
{$eq: ["$$code", “$facilities.facilityCode”]},
{$eq: ["$$groupCode", “$facilities.facilityGroupCode”]}
]
}
}}
],
as: ‘facilities’
}
Any advices are appreciated
Hi @dimitar_vasilev ,
What’s wrong with the way you did it?
Thanks
Pavel
Hey!
Thank your for your answer.
At the end facilities is always an empty array.
Finally is working like this but i don’t think that this is the optimal way to do it.
$lookup: {
from: 'facility',
let: {
code: "$facilities.facilityCode",
groupCode:"$facilities.facilityGroupCode",
facilities: "$facilities"
},
pipeline: [
{$match: {
$expr: {
$and: [
{$eq: ["$code", "$$code"]},
{$eq: ["$groupCode", "$$groupCode"]}
]
}
}
},
{$replaceRoot: {newRoot: {$mergeObjects: ["$$facilities","$$ROOT"]}}}
],
as: 'facilities'
},
$group: {
_id: "$_id",
hotel: {$first: "$$ROOT"},
facilities: {
$push: {$first: "$facilities"}
}
},
$replaceRoot: {
newRoot: {
$mergeObjects: [
"$hotel"
]
}
}
I don’t know why is not formatting my code
The problem is you are using equality and comparing a scalar to an array using $eq
and since they are not identical nothing is matching. If you want to check for presence of value in an array you can use $in
though unfortunately it cannot use an index.
Depending on which direction you are joining you could unwind the array first and then do the $lookup
you are currently doing.
1 Like