Anum
November 2, 2021, 5:52pm
1
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
Anum
November 2, 2021, 6:09pm
3
I have tried embedding the filter differently twice. Attaching the screenshot for reference
Try2:
I'm getting a error. Can you pleaseee help me with this?
Anum
November 2, 2021, 6:15pm
4
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?
Anum
November 2, 2021, 6:33pm
6
[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)#t "2021-11-02T17:16:38.699059Z[GMT]",id":{"isActive":"$isActive"},"count":{"$sum":1}}},{"$sort":{"id":1}},{"$project":{"id":false,"isActive":"$id.isActive","count":true}},{"$sort":{"isActive":1}}]"}, $fn__49053.invoke(results_metadata.clj:147)" $fn__38035$fn__38036.invoke(streaming.clj:148)" $fn__38035.invoke(streaming.clj:147)" .invokeStatic(streaming_response.clj:65)"
@Anum Cannot run query: missing required parameters: #{"createdAt" "enddate"}
[[
{
"$match": {
"$and": [
{
"$expr": {
"$gte": [
"$createdAt", {{startdate}}
]
}
},
{
"$expr": {
"$lt": [
"$createdAt", {{enddate}}
]
}
}
]
}
},
]]
Anum
November 3, 2021, 5:35am
8
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.
Anum
November 3, 2021, 9:21am
10
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}]
flamber
November 3, 2021, 9:43am
11
@Anum Okay, I guess it's not possible to do then.
Anum
November 3, 2021, 11:36am
12
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.
flamber
November 3, 2021, 11:46am
13
Anum
November 3, 2021, 12:13pm
14
What you wrote previously doesn't work. @flamber . I think I mentioned that already.
Anum
November 3, 2021, 12:14pm
15
The stored time is in datetime and what we are passing in the filter is date. I think i mentioned this too earlier.
flamber
November 3, 2021, 12:29pm
16
@Anum Okay, it works fine, when I test on Metabase 0.41.1 with Mongo 4.0.
Anum
November 3, 2021, 12:34pm
17
{https://adoptium.net/ ",
Anum
November 3, 2021, 12:35pm
18
The metabase version is same but still it does not work
flamber
November 3, 2021, 12:55pm
20
@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.
