I am running into an issue where I'm trying to grab some documents near the current document in a lookup. If I manually enter the lon/lat the following query will work but it fails with trying to use anything from the "let". How can I reference the location of the parent document in the geoNear in the lookup pipeline?
[
{
"$match":{
'assessed_improvement_value':{'$gt':500},
'sqft':{'$gt':500}
}
},
{
"$lookup":{
"from":"properties",
"let":{
'lon':{"$arrayElemAt":["$location.coordinates",0]},
'lat':{"$arrayElemAt":["$location.coordinates",1]},
},
'pipeline': [
{
"$geoNear": {
"near": { "type": "Point", "coordinates": [ "$$lon" , "$$lat" ] },
"distanceField": "distance",
"spherical": true
}
},
{"$limit":10}
],
"as":"comps",
}
},
{"$limit":10}
]
Update The first method I posted was in fact a mess. I've now came up with a much cleaner solution. I hope this helps someone in the future
[
{
"$lookup":{
"from":"properties",
"let":{
'plon':{"$arrayElemAt":["$location.coordinates",0]},
'plat':{"$arrayElemAt":["$location.coordinates",1]},
},
'pipeline': [
{
"$addFields":{
"distance":{
"$function":{
"body":"""
function(plonRad,platRad, lonRad, latRad) {
var R = 6373.0;
var dlon = lonRad - plonRad;
var dlat = latRad - platRad;
if((dlon == 0) || (dlat == 0)) {
return 0;
}
var a = Math.pow(Math.sin(dlat / 2),2)+ Math.cos(platRad) * Math.cos(latRad) * Math.pow(Math.sin(dlon / 2),2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
var dist = R * c;
return dist*0.621371;
}
""",
"args":[
{"$toDouble":{"$degreesToRadians":"$$plon"}},
{"$toDouble":{"$degreesToRadians":"$$plat"}},
{"$toDouble":{"$degreesToRadians":{"$arrayElemAt":["$location.coordinates",0]}}},
{"$toDouble":{"$degreesToRadians":{"$arrayElemAt":["$location.coordinates",1]}}}],
"lang":"js"
}
}
}
},
{
"$match":{
"distance":{"$gt":0}
}
},
{"$sort":{"distance":1}},
{"$limit":20}
],
"as":"comps",
}
}
]
I guess this is an old bug that was never fixed for whatever reason. This feels like a mess but it is a working solution. This manually calculates the distance in miles.
[
{
"$match":{
'assessed_improvement_value':{'$gt':500},
'sqft':{'$gt':500}
}
},
{
"$lookup":{
"from":"properties",
"let":{
'lon':{"$arrayElemAt":["$location.coordinates",0]},
'lat':{"$arrayElemAt":["$location.coordinates",1]},
},
'pipeline': [
{
"$addFields": {
'plonRad':{"$degreesToRadians":"$$lon"},
'platRad':{"$degreesToRadians":"$$lat"},
'lonRad':{"$degreesToRadians":{"$arrayElemAt":["$location.coordinates",0]}},
'latRad':{"$degreesToRadians":{"$arrayElemAt":["$location.coordinates",1]}},
}
},
{
'$addFields':{
"dlon":{
"$subtract":["$plonRad", "$lonRad"]
},
"dlat":{
"$subtract":["$platRad", "$latRad"]
},
}
},
{
"$addFields":{
'a':{
"$multiply":[
{
"$add":[
{
"$pow":[
{
"$sin":{
"$divide":["$dlat",2]
}
},
2
]
},
{
"$cos":"$platRad"
}
]
},
{
"$add":[
{
"$pow":[
{
"$sin":{
"$divide":["$dlon",2]
}
},
2
]
},
{
"$cos":"$latRad"
}
]
}
]
},
}
},
{
"$addFields":{
"c":{
"$atan2":[
{"$sqrt":"$a"},
{"$sqrt":{"$subtract":[1,"$a"]}}
]
}
}
},
{
"$addFields":{
"distance":{
"$divide":[
{"$multiply":[6373.0,"$c"]},
1609.34
]
}
}
},
{"$limit":10}
],
"as":"comps",
}
},
{"$limit":10}
]