1

I have a C# method that returns users' property data in a particular area using $geoNear and $project. I used the below pipeline to get the result.

var pipeline = new[]
            {
               new BsonDocument("$geoNear", new BsonDocument
                    {
                        {
                            "near",
                            new BsonDocument
                            {
                                { "type", "Point" },
                                {
                                    "coordinates",
                                    new BsonArray
                                    {
                                        areaInfo.longitude,
                                        areaInfo.latitude
                                    }
                                }
                            }
                        },
                        { "key", "PropertyGeoPoint" },
                        { "distanceField", "Distance" },
                        {
                            "maxDistance", areaInfo.Distance * 1.609344 * 1000
                        },
                        { "spherical", true },
                        { "query", new BsonDocument("$or",
                                new BsonArray {
                                    new BsonDocument
                                    {
                                        { "LastSaleDate", new BsonDocument("$gt", areaInfo.days) },
                                        { "IsResidential", true },
                                        { "IsAbsenteeOwners", true}
                                    }
                                })
                        },
                        { "distanceMultiplier", 0.000621371 }
                    }),

               new BsonDocument("$project",
                    new BsonDocument
                    {
                        { "_id", 0 },
                        { "PropertyFullAddress", 1 },
                        { "PropertyCity", 1},
                        { "OwnerName", 1},
                        { "BathCount", 1 },
                        { "Bedroomscount", 1 },
                        { "LastSaleDate", 1 },
                        { "EstimatedValue", 1 },
                        { "Distance", 1},
                        { "YearBuilt", 1 }
                    })
              };

I need to add another field, that should provide the number of properties for a particular user in a given city by using the same collection.

As an example, if the $geoNear stage returns 5 users, I get the name and city of every user from the list and join with the same collection to get the number of properties they have for any "LastSaleDate".

To do that I used $lookup between $geoNear and $project.

        new BsonDocument("$lookup", new BsonDocument
                {
                    { "from", "tax_assessor" },
                    { "let", new BsonDocument
                        {
                            { "propertyCity", "$PropertyCity" },
                            { "ownerName", "$OwnerName" }
                        }
                    },
                    { "pipeline", new BsonArray
                        {
                            new BsonDocument("$match", new BsonDocument
                            {
                                { "$expr", new BsonDocument
                                    {
                                        { "$and", new BsonArray
                                            {
                                                new BsonDocument("$eq", new BsonArray { "$PropertyCity", "$$propertyCity" }),
                                                new BsonDocument("$eq", new BsonArray { "$OwnerName", "$$ownerName" })
                                            }
                                        }
                                    }
                                }
                            })
                        }
                    },
                    { "as", "NumberOfProperties" }
                })

Then I added NumberOfProperties to the $project.

{ "NumberOfProperties", new BsonArray{"$size", "$NumberOfProperties"}}

Every user in the result list should return at least 1 number of properties but I'm getting Bson null array for NumberOfProperties for every object in the result list. Can someone help me to find the issue here?

Shehan V
  • 164
  • 1
  • 14
  • I notice your `$and` uses `$$owner1Name` (with a 1), but the `let` specifies `ownerName`. Might that be the problem? I'm not sure if this will work as it is anyway though - doing a `$lookup` on multiple fields seems to be painful in mongo. [Potentially useful link.](https://www.mongodb.com/community/forums/t/how-to-lookup-using-multiple-localfields-referencing-multiple-foreignfields/4065/3) – Chris Dueck Mar 14 '23 at 20:53
  • @ChrisDueck sorry it's a typing mistake. I need to use the same collection for $lookup on multiple fields. – Shehan V Mar 15 '23 at 03:04
  • @ChrisDueck I need to do this operation (SQL query) from the above pipeline. select * from (select * from tax_assessor where geoNear = XX) res1 inner join (select OwnerName, count(*) as numberOfProperties from tax_assessor where PropertyCity = AA group by OwnerName) res2 on res1.OwnerName = res2.OwnerName – Shehan V Mar 15 '23 at 06:33

0 Answers0