Hi,
Please find the native query and the complete log here under.
In the query, there's a variable filter of type text, that is required, and the default value is a space. The query (for MongoDB) begins with a "addFileds" stage, in which I indicate that if the filter is space, I want it to be initialized with the current year. When I test the query, with or without a value in the filter, it works fine.
[
{
$addFields: {
yearFilterString: {
$cond: [
{ $eq: [ {{yearFilter}}, " "] },
{ $toString: {$year: new Date() } },
{{yearFilter}}
]
}
}
},
{
$addFields: {
yearFilterInt: { $toInt: "$yearFilterString" },
firstDayYearFilter: {$dateFromString: { dateString: {$concat: [ "$yearFilterString", "-01-01"] } } },
firstDayPreviousYear: {$dateSubtract: {startDate: {$dateFromString: { dateString: {$concat: [ "$yearFilterString", "-01-01"] } } }, unit: "year", amount: 1 } }
}
},
{
$match: {
$or: [
{ $and: [
{ $expr: { $eq: [ "$yearFilterInt", {$year: new Date() } ] } },
{ $expr: { $gte: [ "$date", "$firstDayYearFilter" ] } },
{ $expr: { $lte: [ "$date", new Date() ] } }
]
},
{ $and: [
{ $expr: { $eq: [ "$yearFilterInt", {$year: new Date() } ] } },
{ $expr: { $gte: [ "$date", "$firstDayPreviousYear" ] } },
{ $expr: { $lte: [ "$date", {$dateSubtract: {startDate: new Date(), unit: "year", amount: 1 } } ] } }
]
},
{ $and: [
{ $expr: { $ne: [ "$yearFilterInt", {$year: new Date() } ] } },
{ $expr: { $eq: [ "$year", "$yearFilterInt" ] } }
]
},
{ $and: [
{ $expr: { $ne: [ "$yearFilterInt", {$year: new Date() } ] } },
{ $expr: { $eq: [ "$year", { $subtract: ["$yearFilterInt", 1] } ] } }
]
}
]
}
},
{
$project: {
locality: 1,
year: 1,
"amountY": {$cond: [{$eq: ["$year", "$yearFilterInt"]}, "$feeNetAmount", 0]},
"amountY-1": {$cond: [{$eq: ["$year", { $subtract: ["$yearFilterInt", 1] }]}, "$feeNetAmount", 0]}
}
},
{
$group: {
_id: {
locality: "$locality"
},
"NBI": { $sum: "$amountY" },
"NBI Y-1": { $sum: "$amountY-1" }
}
},
{
$sort: { _id: 1 }
},
{
$project: {
_id: 0,
locality: "$_id.locality",
"NBI": 1,
"NBI Y-1": 1
}
},
{
"$addFields": {
Evolution: {
$round: [
{ $multiply: [
{ $divide: [
{ $subtract: [
"$NBI",
"$NBI Y-1"
]
},
"$NBI Y-1"
]
},
100
]
},
2
]
}
}
}
]
Then in my dashboard, I have a filter of type "input box / single value" , which is linked to the filter of my query.
When putting a value in that filter at the level of the dashboard, I have the following error in the logs (but if I put the same value in the filter of the query directly, it works).
[a5f2f6e5-d057-4458-aa3b-4737398921c0] 2023-04-17T10:21:06+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Command failed with error 16020 (Location16020): 'Invalid $addFields :: caused by :: Expression $in takes exactly 2 arguments. 1 were passed in.' on server dxp-pm2dev-db-mongodb-shard-00-02.autgf.mongodb.net:27017. The full response is {"ok": 0.0, "errmsg": "Invalid $addFields :: caused by :: Expression $in takes exactly 2 arguments. 1 were passed in.", "code": 16020, "codeName": "Location16020", "$clusterTime": {"clusterTime": {"$timestamp": {"t": 1681719664, "i": 1}}, "signature": {"hash": {"$binary": "8L9F27h+8kvZoTmcZbrxxrPoGWw=", "$type": "00"}, "keyId": {"$numberLong": "7185157227357929478"}}}, "operationTime": {"$timestamp": {"t": 1681719664, "i": 1}}}
{:database_id 2,
:started_at #t "2023-04-17T08:21:05.818583Z[GMT]",
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :native,
:middleware {:js-int-to-string? true, :ignore-cached-results? false},
:native
{:template-tags
{"yearFilter"
{:id "f676671c-0fb0-e04b-c44f-26bb80af805b",
:name "yearFilter",
:display-name "Yearfilter",
:type :text,
:required true,
:default " "}},
:query
"[\r\n {\r\n $addFields: {\r\n yearFilterString: {\r\n $cond: [ \r\n { $eq: [ {{yearFilter}}, \" \"] },\r\n { $toString: {$year: new Date() } },\r\n {{yearFilter}}\r\n ]\r\n }\r\n }\r\n },\r\n {\r\n $addFields: {\r\n yearFilterInt: { $toInt: \"$yearFilterString\" },\r\n firstDayYearFilter: {$dateFromString: { dateString: {$concat: [ \"$yearFilterString\", \"-01-01\"] } } },\r\n firstDayPreviousYear: {$dateSubtract: {startDate: {$dateFromString: { dateString: {$concat: [ \"$yearFilterString\", \"-01-01\"] } } }, unit: \"year\", amount: 1 } }\r\n }\r\n },\r\n {\r\n $match: { \r\n $or: [\r\n { $and: [\r\n { $expr: { $eq: [ \"$yearFilterInt\", {$year: new Date() } ] } },\r\n { $expr: { $gte: [ \"$date\", \"$firstDayYearFilter\" ] } },\r\n { $expr: { $lte: [ \"$date\", new Date() ] } }\r\n ]\r\n },\r\n { $and: [\r\n { $expr: { $eq: [ \"$yearFilterInt\", {$year: new Date() } ] } },\r\n { $expr: { $gte: [ \"$date\", \"$firstDayPreviousYear\" ] } },\r\n { $expr: { $lte: [ \"$date\", {$dateSubtract: {startDate: new Date(), unit: \"year\", amount: 1 } } ] } }\r\n ]\r\n },\r\n { $and: [\r\n { $expr: { $ne: [ \"$yearFilterInt\", {$year: new Date() } ] } },\r\n { $expr: { $eq: [ \"$year\", \"$yearFilterInt\" ] } }\r\n ]\r\n },\r\n { $and: [\r\n { $expr: { $ne: [ \"$yearFilterInt\", {$year: new Date() } ] } },\r\n { $expr: { $eq: [ \"$year\", { $subtract: [\"$yearFilterInt\", 1] } ] } }\r\n ]\r\n }\r\n ]\r\n }\r\n },\r\n {\r\n $project: {\r\n locality: 1,\r\n year: 1,\r\n \"amountY\": {$cond: [{$eq: [\"$year\", \"$yearFilterInt\"]}, \"$feeNetAmount\", 0]},\r\n \"amountY-1\": {$cond: [{$eq: [\"$year\", { $subtract: [\"$yearFilterInt\", 1] }]}, \"$feeNetAmount\", 0]}\r\n }\r\n },\r\n {\r\n $group: {\r\n _id: {\r\n locality: \"$locality\"\r\n },\r\n \"NBI\": { $sum: \"$amountY\" },\r\n \"NBI Y-1\": { $sum: \"$amountY-1\" }\r\n }\r\n },\r\n {\r\n $sort: { _id: 1 }\r\n },\r\n {\r\n $project: {\r\n _id: 0,\r\n locality: \"$_id.locality\",\r\n \"NBI\": 1,\r\n \"NBI Y-1\": 1\r\n }\r\n },\r\n {\r\n \"$addFields\": { \r\n Evolution: {\r\n $round: [\r\n { $multiply: [\r\n { $divide: [\r\n { $subtract: [\r\n \"$NBI\", \r\n \"$NBI Y-1\"\r\n ]\r\n },\r\n \"$NBI Y-1\"\r\n ]\r\n }, \r\n 100\r\n ]\r\n },\r\n 2\r\n ]\r\n }\r\n }\r\n }\r\n]",
:collection "net_banking_income"},
:database 2,
:parameters [{:type :string/=, :value ["2022"], :id "84cf96b9", :target [:variable [:template-tag "yearFilter"]]}],
:async? true,
:cache-ttl nil},
:status :failed,
:class com.mongodb.MongoCommandException,
:stacktrace
["com.mongodb.internal.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:175)"
"com.mongodb.internal.connection.InternalStreamConnection.receiveCommandMessageResponse(InternalStreamConnection.java:302)"
"com.mongodb.internal.connection.InternalStreamConnection.sendAndReceive(InternalStreamConnection.java:258)"
"com.mongodb.internal.connection.UsageTrackingInternalConnection.sendAndReceive(UsageTrackingInternalConnection.java:99)"
"com.mongodb.internal.connection.DefaultConnectionPool$PooledConnection.sendAndReceive(DefaultConnectionPool.java:450)"
"com.mongodb.internal.connection.CommandProtocolImpl.execute(CommandProtocolImpl.java:72)"
"com.mongodb.internal.connection.DefaultServer$DefaultServerProtocolExecutor.execute(DefaultServer.java:226)"
"com.mongodb.internal.connection.DefaultServerConnection.executeProtocol(DefaultServerConnection.java:269)"
"com.mongodb.internal.connection.DefaultServerConnection.command(DefaultServerConnection.java:131)"
"com.mongodb.internal.connection.DefaultServerConnection.command(DefaultServerConnection.java:123)"
"com.mongodb.operation.CommandOperationHelper.executeCommand(CommandOperationHelper.java:343)"
"com.mongodb.operation.CommandOperationHelper.executeCommand(CommandOperationHelper.java:334)"
"com.mongodb.operation.CommandOperationHelper.executeCommandWithConnection(CommandOperationHelper.java:220)"
"com.mongodb.operation.CommandOperationHelper$5.call(CommandOperationHelper.java:206)"
"com.mongodb.operation.OperationHelper.withReadConnectionSource(OperationHelper.java:463)"
"com.mongodb.operation.CommandOperationHelper.executeCommand(CommandOperationHelper.java:203)"
"com.mongodb.operation.AggregateOperationImpl.execute(AggregateOperationImpl.java:200)"
"com.mongodb.operation.AggregateOperation.execute(AggregateOperation.java:333)"
"com.mongodb.operation.AggregateOperation.execute(AggregateOperation.java:42)"
"com.mongodb.client.internal.MongoClientDelegate$DelegateOperationExecutor.execute(MongoClientDelegate.java:199)"
"com.mongodb.client.internal.MongoClientDelegate$DelegateOperationExecutor.execute(MongoClientDelegate.java:180)"
"com.mongodb.DBCollection.aggregate(DBCollection.java:1530)"
"com.mongodb.DBCollection.aggregate(DBCollection.java:1488)"
"com.mongodb.DBCollection.aggregate(DBCollection.java:1473)"
"--> driver.mongo.execute$aggregate.invokeStatic(execute.clj:142)"
"driver.mongo.execute$aggregate.invoke(execute.clj:136)"
"driver.mongo.execute$execute_reducible_query.invokeStatic(execute.clj:179)"
"driver.mongo.execute$execute_reducible_query.invoke(execute.clj:173)"
"driver.mongo$fn__120589$f__119411__auto____120590.invoke(mongo.clj:308)"
"driver.mongo.util$do_with_mongo_connection$fn__119399.invoke(util.clj:230)"
"util.ssh$do_with_ssh_tunnel.invokeStatic(ssh.clj:141)"
"util.ssh$do_with_ssh_tunnel.invoke(ssh.clj:130)"
"driver.mongo.util$do_with_mongo_connection.invokeStatic(util.clj:224)"
"driver.mongo.util$do_with_mongo_connection.invoke(util.clj:219)"
"driver.mongo$fn__120589.invokeStatic(mongo.clj:307)"
"driver.mongo$fn__120589.invoke(mongo.clj:305)"
"query_processor.context$executef.invokeStatic(context.clj:60)"
"query_processor.context$executef.invoke(context.clj:49)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:46)"
"query_processor.context$runf.invoke(context.clj:40)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69171.invoke(cache.clj:224)"
"query_processor.middleware.permissions$check_query_permissions$fn__64643.invoke(permissions.clj:126)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68062.invoke(mbql_to_native.clj:24)"
"query_processor$fn__70690$combined_post_process__70695$combined_post_process_STAR___70696.invoke(query_processor.clj:243)"
"query_processor$fn__70690$combined_pre_process__70691$combined_pre_process_STAR___70692.invoke(query_processor.clj:240)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69082$fn__69087.invoke(resolve_database_and_driver.clj:36)"
"driver$do_with_driver.invokeStatic(driver.clj:90)"
"driver$do_with_driver.invoke(driver.clj:86)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69082.invoke(resolve_database_and_driver.clj:35)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__64952.invoke(fetch_source_query.clj:310)"
"query_processor.middleware.store$initialize_store$fn__65130$fn__65131.invoke(store.clj:12)"
"query_processor.store$do_with_store.invokeStatic(store.clj:47)"
"query_processor.store$do_with_store.invoke(store.clj:41)"
"query_processor.middleware.store$initialize_store$fn__65130.invoke(store.clj:11)"
"query_processor.middleware.normalize_query$normalize$fn__69371.invoke(normalize_query.clj:25)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__66308.invoke(constraints.clj:54)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__69307.invoke(process_userland_query.clj:150)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__69684.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___59455$thunk__59457.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___59455$fn__59459.invoke(reducible.clj:108)"],
:card_id 88,
:context :dashboard,
:error
"Command failed with error 16020 (Location16020): 'Invalid $addFields :: caused by :: Expression $in takes exactly 2 arguments. 1 were passed in.' on server dxp-pm2dev-db-mongodb-shard-00-02.autgf.mongodb.net:27017. The full response is {\"ok\": 0.0, \"errmsg\": \"Invalid $addFields :: caused by :: Expression $in takes exactly 2 arguments. 1 were passed in.\", \"code\": 16020, \"codeName\": \"Location16020\", \"$clusterTime\": {\"clusterTime\": {\"$timestamp\": {\"t\": 1681719664, \"i\": 1}}, \"signature\": {\"hash\": {\"$binary\": \"8L9F27h+8kvZoTmcZbrxxrPoGWw=\", \"$type\": \"00\"}, \"keyId\": {\"$numberLong\": \"7185157227357929478\"}}}, \"operationTime\": {\"$timestamp\": {\"t\": 1681719664, \"i\": 1}}}",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}
[a5f2f6e5-d057-4458-aa3b-4737398921c0] 2023-04-17T10:21:06+02:00 DEBUG metabase.server.middleware.log POST /api/dashboard/20/dashcard/138/card/88/query 202 [ASYNC: completed] 473.7 ms (12 DB calls) App DB connections: 0/15 Jetty threads: 2/50 (6 idle, 0 queued) (118 total active threads) Queries in flight: 0 (0 queued)