How do I query mongodb with aggregration by passing data as a parameter to filter unix time stamp

I'm querying through Metabase which is connected to a Mongodb server. The field which I'm querying is nested and is a Unix timestamp. See below

{
  room_data: {
      "meta": {
        "xxx_unrecognized": null,
        "xxx_sizecache": 0,
        "id": "Hke7owir4oejq3bMf",
        "createdat": 1565336450838,
        "updatedat": 1565336651548,
      }
   }
}

The query I have written is as follows

[
    {
        $match: {
            client_id: "{{client_id}}",
            "room_data.meta.createdat": {
                $gt: "{{start}}",
                $lt: "{{end}}",
            }
        }

    },
    {
        $group: {

            id: "$room_data.recipe.id",
            count: {
                $sum: 1
            }
        }
    }
]

I do not get any result as the field room_data.meta.createdat is not a date (Aug 20, 2020) which I'm passing in. Here start and end are the parameters (Metabase feature) which I'm passing in the Date format. I need some help in converting those dates into unix timestamp which can then be used to filter out the results between the specific dates

Solution

If you're using Mongo version 4.0+ you can then use $toDate in you're aggregation like so:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $eq: [
              "$client_id",
              {{client_id}}
            ]
          },
          {
            $lt: [
              {
                $toDate: "$room_data.meta.createdat"
              },
             {{end}}
            ]
          },
          {
            $gt: [
              {
                $toDate: "$room_data.meta.createdat"
              },
              {{start}}
            ]
          }
        ]
      }
    }
  }
])

MongoPlayground

If you're you're on an older Mongo version I recommend you either convert you're database fields to be Date type, or you convert your input into a number timestamp somehow (I'm unfamiliar with metabase).

The last option is to use $subtract as you can subtract a number from a date in Mongo, then check to see whether that date is before or after 1970-01-01T00:00:00Z. the problem with this approach is it does not consider timezones, so if your input's timezone is different than your database one or is dynamic this will be a problem you'll have to account for.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $eq: [
              "$client_id",
              {{client_id}}
            ]
          },
          {
            $gt: [
              {
                "$subtract": [
                  {{end}},
                  "$room_data.meta.createdat"
                ]
              },
              ISODate("1970-01-01T00:00:00.000Z")
            ]
          },
          {
            $lt: [
              {
                "$subtract": [
                  {{start}},
                  "$room_data.meta.createdat"
                ]
              },
              ISODate("1970-01-01T00:00:00.000Z")
            ]
          }
        ]
      }
    }
  }
])

MongoPlayground