Question 118167 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongo $lookup aggregation using date timestamps

I have two collections: Car Drive Histories and Car Geolocations. For the purpose of analyzing drive patterns I have to aggregate driving histories and link them to car geolocations.

I’ve used $match and $project aggregation stages to get drive history documents with the following structure:

travelPurpose:<String>
carID:<ObjectId>
checkOutTime:<Date>
checkInTime:<Date>

The next step is to use $lookup stage to get car location between the two timestamps (checkOutTime and checkInTime). Every car geolocation document has carID and geoLocationTimestamp fields. If I use static dates, for example as such:

{
  from: 'carGeoLocations',
  localField: 'carID',
  foreignField: 'carID',
  pipeline: [
    {$match: {
      geoLocationTimestamp: {
        $gte: ISODate('2022-01-01T00:00:00.000+0000'), 
        $lte: ISODate('2023-01-01T00:00:00.000+0000')
      }
    }}
  ],
  as: 'coordinates'
}

I do get geolocations between 1. 1. 2022 and 1. 1. 2023. Mongo Playground with an example of this behaviour can be accessed here.
However, if I try to use dynamic dates based on values of checkOutTime and checkInTime, no documents are retrieved. Mongo playground with this example is available here. I’ve tried the following:

{
 from: 'carGeoLocations',
 localField: 'carID',
 foreignField: 'carID',
 pipeline: [
   {$match: {
     geoLocationTimestamp: {
       $gte: "$checkOutTime", 
       $lte: "$checkInTime"
     }
   }}
 ],
 as: 'coordinates'
}

and

{
 from: 'carGeoLocations',
 localField: 'carID',
 foreignField: 'carID',
 let: {t1: '$checkOutTime', t2: '$checkInTime'}
 pipeline: [
   {$match: {
     geoLocationTimestamp: {
       $gte: '$$t1', 
       $lte: '$$t2'
     }
   }}
 ],
 as: 'coordinates'
}

With the same results. Can anyone spot any issues with my approach?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Update

    After further experimentation, it turns out you need to use $expr when you want to use variables declared with let in $lookup stage of aggretation.

    My lookup stage now looks like this:

    {
    "$lookup": {
      "from": "carGeoLocations",
      "localField": "carID",
      "foreignField": "carID",
      "let": {
        t1: "$checkOutTime",
        t2: "$checkInTime"
      },
      "pipeline": [
        {
          $match: {
            $and: [
              {
                $expr: {
                  $gte: [
                    "$geoLocationTimestamp",
                    "$$t1"
                  ],
                }
              },
              {
                $expr: {
                  $lte: [
                    "$geoLocationTimestamp",
                    "$$t2"
                  ],
                }
              },
            ]
          }
        }
      ],
      "as": "coordinates"
    }
  }

  2. 0

    First lookup and than use match for geoLocationTimestamp
    Try following code 

     {
        $lookup:{
         from: 'carGeoLocations',
         localField: 'carID',
         foreignField: 'carID',
         as: 'coordinates'
        }
        },
        {
        $match:{
           geoLocationTimestamp: {
             $gte:'$coordinates.checkOutTime',
             $lte:'$coordinates.checkInTime'
            }
          }
        }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search