Native MongoDB queries don't work if they contain ISODate() calls

[cwbeck]

Query fails to run: (MongoDB)

[
  {
    "$match": {
      "hour": {
        "$gte": ISODate("2016-08-09T00:00:00Z")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "year": {"$year": "$hour"},
        "month": {"$month": "$hour"}
      },
      "count": {
        "$sum": "$count"
      }
    }
  }
]

Unrecognized token 'ISODate': was expecting 'null', 'true', 'false' or NaN at [Source: java.io.StringReader@2b0b4b64; line: 6, column: 24]

[camsaul]

@cwbeck it looks like Metabase is trying to parse your MongoDB query as JSON (which works in most cases). We need to skip parsing and just pass the query string in directly because as this example shows Mongo aggregation pipeline queries are not necessarily strict JSON.

[cwbeck]

@camsaul thanks for the update. I'll take a look shortly

[camsaul]

So as far as I can tell there is no easy way to handle this situation given our current setup with Monger (our Mongo library). I've opened an issue upstream here: michaelklishin/monger#143

So until we have time to roll our own Mongo library for Clojure that handles our needs I'm going to have to mark this as a limitation 💔

[Compuabhi]

@camsaul I was dealing with MongoDB native queries in metabase. In sql, one can add query parameters using {{var_name}}. What is the format for MongoDB query parameters? Using same format was throwing errors that "It is expecting double-quote to start field name. Whether this feature has been added or not?

[camsaul]

Fixed by #4132

[daorren]

WARNING: below is outdated, and using ISODate("2018-06-21") should be OK, which should be documented

try it out in pull/4132 test

[
    {
      "$match": {"$and": [
          {"logType": "ClickGlobalNavBtn"},
          {"target": "Mail"},
          {"created": {"$gte": ["___ISODate", "2018-06-21"]}}
      ]}
    },
    {
      "$group": {"_id": "$target", "count": {"$sum": 1}}
    }
]

[mhdali93]

Not working for me, it is showing: No results!
This may be the answer you’re looking for. If not, try removing or changing your filters to make them less specific.