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

Question

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

score 1 · Accepted Answer · answered Aug 20 '20 at 14:19

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

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

1 Answers1