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

Working with Data
#1

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

#2

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

#3

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.

#4

Next thing to try is:

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