Optional date clause in mongo db query

I have this query below. i want to add an optional date filter.

[
    {
        "$match": {
            "createdAt": { "$gte": new ISODate("2017-01-14"), "$lte": new ISODate("2021-04-15") }
        }
    },
{$group: {
  "_id": {
    "isActive": "$isActive"
  },
  "count": {
    "$sum": 1
  }
}}, {$sort: {
  "_id": 1
}}, {$project: {
  "_id": false,
  "status":{
          $switch:
            {
              branches: [
                {
                  case: { $eq : [ "$_id.isActive", true] },
                  then: "Active"
                },
                {
                  case: { $eq : [ "$_id.isActive", false] },
                  then: "Inactive"
                }
              ],
              default: "No status"
            }
         }
      ,
  "count": true
}}


]);

Note: The date is the document is in datetime

Hi @Anum
Have a look here:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses

I have tried embedding the filter differently twice. Attaching the screenshot for reference
Try1:

Try2:

I'm getting a error. Can you pleaseee help me with this?

The above had a bracket missing. That got resolved. Now i get this error

@Anum It's not your first time in this forum, or trying to debug something, right?
How am I supposed to read a cut-off screenshot error?
As always, the full stracktrace is available in Admin > Troubleshooting > Logs.

[2c179ee7-87ba-418b-ad9c-120bdab96e64] 2021-11-02T22:16:37+05:00 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 307.9 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 2/50 (4 idle, 0 queued) (102 total active threads) Queries in flight: 1 (0 queued)
[2c179ee7-87ba-418b-ad9c-120bdab96e64] 2021-11-02T22:16:38+05:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 2,
:started_at #t "2021-11-02T17:16:38.699059Z[GMT]",
:error_type :invalid-query,
:json_query
{:database 2,
:native
{:collection "Rider",
:template-tags
{:createdAt
{:id "036f757a-01bd-2c1a-fe9a-3803ad3d0acc",
:name "createdAt",
:display-name "start date",
:type "date",
:default nil},
:enddate
{:id "b2af2638-b8d0-7990-7711-b2c0539480b9",
:name "enddate",
:display-name "Enddate",
:type "date",
:default nil}},
:query
"[{"$match":{"$and":[{"$expr":{"$gte":["$createdAt",{"$dateFromString":{"dateString":{{createdAt}}}}]}},{"$expr":{"$lt":["$createdAt",{"$dateFromString":{"dateString":{{enddate}}}}]}}]}},{"$group":{"id":{"isActive":"$isActive"},"count":{"$sum":1}}},{"$sort":{"id":1}},{"$project":{"id":false,"isActive":"$id.isActive","count":true}},{"$sort":{"isActive":1}}]"},
:type "native",
:parameters [],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:status :failed,
:class clojure.lang.ExceptionInfo,
:stacktrace
["--> driver.mongo.parameters$substitute.invokeStatic(parameters.clj:181)"
"driver.mongo.parameters$substitute.invoke(parameters.clj:178)"
"driver.mongo.parameters$parse_and_substitute.invokeStatic(parameters.clj:189)"
"driver.mongo.parameters$parse_and_substitute.invoke(parameters.clj:186)"
"driver.mongo.parameters$substitute_native_parameters.invokeStatic(parameters.clj:197)"
"driver.mongo.parameters$substitute_native_parameters.invoke(parameters.clj:193)"
"driver.mongo$fn__93273.invokeStatic(mongo.clj:238)"
"driver.mongo$fn__93273.invoke(mongo.clj:236)"
"query_processor.middleware.parameters.native$expand_inner.invokeStatic(native.clj:39)"
"query_processor.middleware.parameters.native$expand_inner.invoke(native.clj:30)"
"query_processor.middleware.parameters$expand_one.invokeStatic(parameters.clj:50)"
"query_processor.middleware.parameters$expand_one.invoke(parameters.clj:41)"
"query_processor.middleware.parameters$expand_all$replace_47988__47989.invoke(parameters.clj:59)"
"mbql.util.match.impl$replace_in_collection$iter__25740__25744$fn__25745.invoke(impl.cljc:44)"
"mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:43)"
"mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
"query_processor.middleware.parameters$expand_all$replace_47988__47989.invoke(parameters.clj:59)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:59)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:53)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:56)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:53)"
"query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:77)"
"query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:73)"
"query_processor.middleware.parameters$fn__48004$substitute_parameters_STAR___48009$fn__48010.invoke(parameters.clj:82)"
"query_processor.middleware.parameters$fn__48004$substitute_parameters_STAR___48009.invoke(parameters.clj:79)"
"query_processor.middleware.parameters$substitute_parameters$fn__48030.invoke(parameters.clj:111)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45292.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__51349.invoke(expand_macros.clj:184)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__47809.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__50294.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788$fn__49792.invoke(resolve_database_and_driver.clj:31)"
"driver$do_with_driver.invokeStatic(driver.clj:60)"
"driver$do_with_driver.invoke(driver.clj:56)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788.invoke(resolve_database_and_driver.clj:25)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__45842.invoke(fetch_source_query.clj:274)"
"query_processor.middleware.store$initialize_store$fn__46030$fn__46031.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__46030.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__50077.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__50084.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__47755.invoke(add_rows_truncated.clj:35)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG
$fn__49053.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__47773.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__50063.invoke(process_userland_query.clj:146)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__50374.invoke(catch_exceptions.clj:169)"
"query_processor.reducible$async_qp$qp_STAR___42477$thunk__42478.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___42477.invoke(reducible.clj:109)"
"query_processor.reducible$sync_qp$qp_STAR___42486$fn__42489.invoke(reducible.clj:135)"
"query_processor.reducible$sync_qp$qp_STAR___42486.invoke(reducible.clj:134)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:241)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:237)"
"query_processor$fn__51397$process_query_and_save_execution_BANG___51406$fn__51409.invoke(query_processor.clj:253)"
"query_processor$fn__51397$process_query_and_save_execution_BANG___51406.invoke(query_processor.clj:245)"
"query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450$fn__51453.invoke(query_processor.clj:265)"
"query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450.invoke(query_processor.clj:258)"
"api.dataset$run_query_async$fn__57784.invoke(dataset.clj:64)"
"query_processor.streaming$streaming_response_STAR
$fn__38035$fn__38036.invoke(streaming.clj:148)"
"query_processor.streaming$streaming_response_STAR
$fn__38035.invoke(streaming.clj:147)"
"async.streaming_response$do_f_STAR
.invokeStatic(streaming_response.clj:65)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
"async.streaming_response$do_f_async$fn__25228.invoke(streaming_response.clj:84)"],
:card_id nil,
:context :ad-hoc,
:error "Cannot run query: missing required parameters: #{"createdAt" "enddate"}",
:row_count 0,
:running_time 0,
:ex-data {:type :invalid-query},
:data {:rows [], :cols []}}

@Anum Cannot run query: missing required parameters: #{"createdAt" "enddate"}
I don't know what you're trying to do, but have you tried just do this:

[[
  {
    "$match": {
      "$and": [
        {
          "$expr": {
            "$gte": [
              "$createdAt", {{startdate}}
            ]
          }
        },
        {
          "$expr": {
            "$lt": [
              "$createdAt", {{enddate}}
            ]
          }
        }
      ]
    }
  },
]]

I don't get any answer because the datetime should be converted into date.

The time is stored like this

2021-07-31T15:58:51+05:100:

@Anum Okay, go and do filter in the GUI, where you apply the filters and get the correct results, and then get the Native query and post it here.

The date does not work because the time stored is in date time

[{"$match":{"$and":[{"$expr":{"$gte":["$updatedAt",{"$dateFromString":{"dateString":"2021-11-01T00:00Z"}}]}},{"$expr":{"$lt":["$updatedAt",{"$dateFromString":{"dateString":"2021-11-12T00:00Z"}}]}}]}},{"$project":{"_id":false,"updatedAt":"$updatedAt"}},{"$limit":1048575}]

@Anum Okay, I guess it's not possible to do then.
It's impossible to help you without you providing example data.

Document 1
{
	"_id" : ObjectId("5f71f3bc820d47628c25b22f"),
	"createdAt" : ISODate("2020-09-28T19:31:24.898+05:00"),
	"updatedAt" : ISODate("2020-09-28T19:31:24.898+05:00"),
	"weight" : 0.5
}
Document 2
{
	"_id" : ObjectId("5f71f3bc820d47628c25b22f"),
	"createdAt" : ISODate("2021-09-28T19:31:24.898+05:00"),
	"updatedAt" : ISODate("2021-09-28T19:31:24.898+05:00"),
	"weight" : 0.5
}

I want to pass the date without time in the datefilter and get the desired value within the range. Each document has datetime values.

@Anum Okay, then do what I wrote previously: Optional date clause in mongo db query

What you wrote previously doesn't work. @flamber. I think I mentioned that already.

The stored time is in datetime and what we are passing in the filter is date. I think i mentioned this too earlier.

@Anum Okay, it works fine, when I test on Metabase 0.41.1 with Mongo 4.0.

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.54 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.12+7",
"java.vendor": "Eclipse Foundation",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.12",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.12+7",
"os.name": "Linux",
"os.version": "4.14.248-189.473.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"mongo"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-10-21",
"tag": "v0.41.1",
"branch": "release-x.41.x",
"hash": "76aa4a5"
},
"settings": {
"report-timezone": null
}
}
}

The metabase version is same but still it does not work

Can you suggest a fix?

@Anum I don't know. I don't think I can help you. Create a database View on Mongo instead of creating the native query in Metabase, then you'll have regular "tables" available in Metabase. Search the internet for how to create Views on Mongo or ask in a forum dedicated to that database, since it has nothing to do with Metabase.