($geoNear + query with $$variable) Filtering collection based on location data from another collection

Hi,

Trying to fetch “requests” with coordinates that come from “projectsPCT” collection. Coordinates are used as filters and collection fields that connect documents are: requests.projectNumber === projectsPCT.number

Data samples:
Requests:

{
"_id":"uXDyX3Mwqx3mQgsRP",
"projectNumber":"ABC-123",
"extraInfo":"info"
}

ProjectsPCT:

{
"_id":"Cfp2NpwoJFAo22yY3",
"number":"ABC-123","name":"Central Metrics",
"location":{"type":"Point","coordinates":[51.206764,7.030342]}
}

Expected outcome are filtered requests based on location:

[
{
"_id":"uXDyX3Mwqx3mQgsRP",
"projectNumber":"ABC-123",
"extraInfo":"info",
"location":{"type":"Point","coordinates":[51.206764,7.030342]}
}
]

My initial aggregation attempt:

{
'from': 'projectsPCT',
// 'localField': 'number',//can't use with pipeline
// 'foreignField': 'projectNumber',//can't use with pipeline
'let': {
  'projectNumber': '$projectNumber'
},
'pipeline': [
 {
    '$geoNear': {
      'near': {
        'type': 'Point', 
        'coordinates': [
          51.206764, 7.030342
        ]
      }, 
      'distanceField': 'distance', 
      'maxDistance': 70000, 
      'spherical': true,
      //'query': {"number": "$projectNumber"} //<--this doesn't work for some reasons?
      'query': {"number": "ABC-123"} //<- hardcoding number works though
    }
  },
    {
     $set: {
      projectNumberTest: '$$projectNumber'
     }
   },
],
'as': 'projectsPCT',
}

MongoDB server version: 4.4.4

I would try with $$projectNumber like you did inside the $set: to access the variables defined in the let: parameter.

with $$projectNumber or $projectNumber i receive empty results in returned parameter projectsPCT,
while with hardcoded ABC-123 i do receive an array in parameter projectsPCT containing located projectsPCT.

Next thing to try is:

'query': { "$expr" : { "$eq" : [ "$number" , "$$projectNumber"] } }