Querying and Filtering with Specific Keywords and Proximity

Question

How to query the "car" collection, which stores a document for every car, and retrieve all cars that include specific keywords in their names. Then, find the latest "carInfo" document for each of these cars and return only the cars that are within a certain distance (X miles) from given coordinates.

Here are the schemas for the relevant collections:

Car Schema:
{
  "_id": "ObjectId",
  "name": "String",
}


CarInfo Schema:
{
  "car": {
    "$ref": "Car"
  },
  "address": {
     "location": {
      "type": "Point",
      "coordinates": [Number]
      }
  },
  "timestamp": "Date",
}

I tried this but I keep getting: MongoServerError: $geoNear, $near, and $nearSphere are not allowed in this context

db.cars.aggregate([
    {
      $match: {
        name: { $in: KEYWORDS},
      },
    },
    {
      $lookup: {
        from: 'carinfos',
        localField: '_id',
        foreignField: 'car',
        as: 'carInfo',
        pipeline: [{ $sort: { timestamp: -1 } }, { $limit: 1 }],
      },
    },
    {
      $unwind: '$carInfo',
    },
    {
      $match: {
        'carInfo.address.location': {
          $near: {
            $geometry: {
              type: 'Point',
              coordinates: coordinates,
            },
            $maxDistance: 5 * 1609.34,
          },
        },
      },
    }
  ])

Answer 1

One option is to do something like this:

1. Match only carinfos which where in range during the last week
2. Get their car data
3. Match only document which have the relevant KEYWORDS
4. For each relevant document, get the latest location of this car
5. Keep only documents which are the latest location.
6. You can format the response if you want

db.carinfos.aggregate([
  {$geoNear: {
      near: { type: 'Point', coordinates: coordinates},
      distanceField: 'distance',
      maxDistance: DISTANCE_IN_METERS,
      query: {timestamp: {$gte: timestampBeforeLastWeek}}, // for example
      spherical: true
  }},
  {$lookup: {
      from: 'cars',
      localField: 'car',
      foreignField: '_id',
      as: 'carDetail'
  }},
  {$match: {$expr: {$in: [{$first: '$carDetail.name'}, KEYWORDS]}}},
  {$lookup: {
        from: 'carinfos',
        localField: 'car',
        foreignField: 'car',
        as: 'last_id',
        pipeline: [
          { $project: {_id: 0, timestamp: 1}},
          { $sort: { timestamp: -1 } },
          { $limit: 1 },
        ],
   }},
   {$match: {$expr: {$eq: [{$first: "$last_id.timestamp"}, "$timestamp"]}}},

See how it works on the playground example without the $near step as it is not supported