Aggregation with indexed date field not working

Working with Data
,
12

Here is the steps to rewrite the pipeline :

  • create a collection named workaround
  • insert documents with the value you want to use :
{_id:"1h", ms:3600000}
{_id:"1d", ms:86400000}
{_id:"4d", ms:345600000}
  • rewrite your pipeline from the workaround collection, with this stages
    ** stage 1 : $match to target your duration (4d? 1h?)
    ** stage 2 : $project to calculate your threashold date from $subtract and $$NOW
    ** stage 3 : $lookup to build a _tmp array, from stage 2 and the main collection. Use your original pipeline as a sub-pipeline in this stage
    ** stage 4 : $unwind items in the _tmp array
    ** stage 5 : $project to print the attended result

Here is the rewrite for what I posted here : Aggregation with indexed date field not working - #4 by Patrick_DEMONGUERES

[
  {
    $match: {
      _id: "4d"
    }
  },
  {
    $project: {
      _id: 0,
      ms: 1,
      threasholddate: {
        $subtract: ["$$NOW", "$ms"]
      }
    }
  },
  {
    $lookup: {
      from: "main_collection",
      as: "_tmp",
      let: {
        threshold: "$threasholddate"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gte: [
                "$creation_date",
                "$$threshold"
              ]
            }
          }
        },
        {
          $group: {
            _id: {
              creation_date: {
                $dateToString: {
                  format: "%Y-%m-%d",
                  date: "$creation_date"
                }
              }
            },
            total: {
              $sum: 1
            }
          }
        }
      ]
    }
  },
  {
    $unwind: {
      path: "$_tmp"
    }
  },
  {
    $project: {
      _id: 0,
      creation_date: "$_tmp._id.creation_date",
      total: {
        $toInt: "$_tmp.total"
      }
    }
  }
]