Aggregation pipeline with arrays and multiple foreign keys

Working with Data
#1

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

#2

Hi @dimitar_vasilev ,

What’s wrong with the way you did it?

Thanks
Pavel

#3

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

#4

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