lookup with pipeline and geoIntersects that use let variable

Question

I am trying to lookup for neighborhoods which match my condition - the boundries polygon intersects with the post's coordinates but I am unable to do it - cant use the let in my pipeline $match

example of post entity:

{
  _id: ObjectId,
  ...,
  location: {
    ...,
    coordinates: {
      type: 'Point',
      coordinates: [number, number]
    }
  }
};

example of neighborhood entity:

{
  _id: ObjectId,
  ...,
  boundries: {
    type: 'Polygon',
    coordinates: [ [ [number, number], [number, number], [number, number], [number, number], [number, number] ] ]
  }
};

example of query I am trying to "fix":

db.posts.aggregate([
  { $match: { _id: ObjectId('5a562e62100338001218dffa') } },
  {
    $lookup: {
      from: 'neighborhoods',
      let: { postCoordinates: '$location.coordinates.coordinates' },
      pipeline: [
        {
          $match: {
            boundries: {
              $geoIntersects: {
                $geometry: {
                  type: 'Point',
                  coordinates: '$$postCoordinates'
                }
              }
            }
          }
        }
      ],
      as: 'neighborhoods'
    }
  }
]);

For using `let` you can see `https://stackoverflow.com/questions/48518215/mongodb-aggregation-lookup-with-conditions` might it help you — IftekharDani, Sep 11 '18 at 13:37

score 4 · Accepted Answer · answered Sep 12 '18 at 01:41

Unfortunately coordinates can't be populated from document field.

Geospatial are query expressions and $let variables are only permitted to use in $match with $expr variant for aggregation expressions in $lookup pipeline.

You have to perform the query in two steps.

First step to get the coordinates for matching record.

var result =  db.posts.findOne({ _id: ObjectId('5a562e62100338001218dffa')},{ _id: 0, 'location':1});

Second step to look for point in the polygon.

db.neighborhoods.find(
   {
     "boundries": {
       $geoIntersects: {
          $geometry: {
             type: "Point" ,
             coordinates: result.location.coordinates.coordinates
          }
       }
     }
   }
)

You answer is ok but it's more complicated for multiple entities (posts in this case) that I want to find their neighborhoods. I made an array of MultiPoints and then intersected with hoods, it works but I am having hard time to map the correct hood/s to the post. I decided to take a different path. Saving the neighborhoods' id on the post entity in field called "hoods" and then have all the ids of the hoods in each post, this way I can query for all the needed hoods and map easily. — Ron, Sep 13 '18 at 08:25

lookup with pipeline and geoIntersects that use let variable

1 Answers1