mongodb loopup pipeline geoNear

Question

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}
]

Answer 1

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}
]