'mongodb - $lookup not searchable

So i have two collections

  1. petrolStations
  2. petrolData

They are linked by the petrolStations $_id

as I need to go a $geoNear

I planned on doing the following

[{$geoNear: {
 near: {
  type: 'Point',
  coordinates: [
   115.951869,
   -31.940377
  ]
 },
 spherical: true,
 distanceField: 'distance',
 distanceMultiplier: 0.000621371,
 maxDistance: 100000,
 includeLocs: 'location'
}}, {$sort: {
 distance: 1
}}, {$lookup: {
 from: 'petrolData',
 localField: '_id',
 foreignField: 'storeid',
 as: 'latest'
}}]

While technically that works - by creating a subarray. I can't workout how I search the PetrolData to only return based on the following

  1. is petrolData.date field eq to today
  2. is petrolData.price NOT null

Also I don't want it in a subarray if possible.

Sample data

PetrolStation

/** 
* Paste one or more documents here
*/
{
  "_id": ObjectId("626f47fe6220ff227b587d01"),
  "location": {
    "coordinates": [
      115.80609,
      -31.904837
    ],
    "type": "Point"
  },
  "store": "United Osborne Park",
  "address": "479 Scarborough Beach Rd, OSBORNE PARK"
}

petrolData Sample Data

/** 
* Paste one or more documents here
*/
{
  "date": {
    "$date": {
      "$numberLong": "1651461677000"
    }
  },
  "storeid": {
    "$oid": "626f47fe6220ff227b587d01"
  },
  "__v": 0,
  "price": 1.95,
  "typeOfFuel": "91"
}

Based on the answer below I created this query.

[{$geoNear: {
 near: {
  type: 'Point',
  coordinates: [
   115.951869,
   -31.940377
  ]
 },
 spherical: true,
 distanceField: 'distance',
 distanceMultiplier: 0.000621371,
 minDistance: 5000,
 maxDistance: 10000,
 includeLocs: 'location'
}}, {$limit: 8}, {$lookup: {
 from: 'petrolData',
 localField: '_id',
 foreignField: 'storeid',
 as: 'latest'
}}, {$sort: {
 distance: 1
}}, {$unwind: {
 path: '$latest'
}}, {$match: {
 'latest.price': {
  $exists: true,
  $ne: null
 },
 'latest.typeOfFuel': '95'
}}, {$limit: 4}]

However, I feel wouldn't it be faster to check if the petrol station has latest.typeOfFuel: 95 first?

mongodb


Solution 1:[1]

Add an other stage to your aggregation to match the price and the date. You might want to make the date dynamic:

[
  {
    $geoNear: {
      near: {
        type: "Point",
        coordinates: [
          115.951869,
          -31.940377
        ]
      },
      spherical: true,
      distanceField: "distance",
      distanceMultiplier: 0.000621371,
      maxDistance: 100000,
      includeLocs: "location"
    }
  },
  {
    $sort: {
      distance: 1
    }
  },
  {
    $lookup: {
      from: "petrolData",
      localField: "_id",
      foreignField: "storeid",
      as: "latest"
    }
  },
  {
    $match: {
      "latest.price": {
        "$exists": true,
        "$ne": null
      },
      "latest.date": {
        $gte: ISODate("2022-05-02"),
        $lte: ISODate("2022-05-02")
      }
    }
  }
]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 PatersonCode

Related Questions