Mongodb convert native query

1

I am using mongodb and created a report using visualization editor option and it shows me correct results. I have used group by, sort and filter clauses in visualization editor.Once I convert the report into native query the same report shows "No results". I have to do this to add locked variables and publish the report.

Please help

My native query:
[{"$project":{"status":"$status","tenant___enterpriseId":"$tenant.enterpriseId","stage___text":"$stage.text"}},{"$match":{"$and":[{"status":{"$eq":"open"}},{"tenant___enterpriseId":{"$eq":"5ccc6587d27de95767793dd1"}}]}},{"$project":{"_id":"$_id","___group":{"stage___text":"$stage___text"}}},{"$group":{"_id":"$___group","count":{"$sum":1}}},{"$sort":{"_id":1}},{"$project":{"_id":false,"stage___text":"$_id.stage___text","count":true}},{"$sort":{"stage___text":1}}]

Diagnostic info:
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.190 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.8+10",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.8",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.8+10",
"os.name": "Linux",
"os.version": "4.14.171-105.231.amzn1.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mongo"
],
"hosting-env": "elastic-beanstalk",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.10"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"date": "2020-09-15",
"tag": "v0.36.6",
"branch": "release-0.36.x",
"hash": "cb258fb"
},
"settings": {
"report-timezone": null
}
}
}

Troubleshooting Log:
:stacktrace
["--> driver.mongo.parameters$substitute.invokeStatic(parameters.clj:160)"
"driver.mongo.parameters$substitute.invoke(parameters.clj:157)"
"driver.mongo.parameters$parse_and_substitute.invokeStatic(parameters.clj:168)"
"driver.mongo.parameters$parse_and_substitute.invoke(parameters.clj:165)"
"driver.mongo.parameters$substitute_native_parameters.invokeStatic(parameters.clj:176)"
"driver.mongo.parameters$substitute_native_parameters.invoke(parameters.clj:172)"
"driver.mongo$eval2514$fn__2515.invoke(mongo.clj:234)"
"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:53)"
"query_processor.middleware.parameters$expand_one.invoke(parameters.clj:44)"
"query_processor.middleware.parameters$expand_all$replace_46258__46259.invoke(parameters.clj:62)"
"mbql.util.match$replace_in_collection$iter__24900__24904$fn__24905.invoke(match.clj:132)"
"mbql.util.match$replace_in_collection.invokeStatic(match.clj:131)"
"mbql.util.match$replace_in_collection.invoke(match.clj:126)"
"query_processor.middleware.parameters$expand_all$replace_46258__46259.invoke(parameters.clj:62)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:62)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:56)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:59)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:56)"
"query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:80)"
"query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:76)"
"query_processor.middleware.parameters$fn__46274$substitute_parameters_STAR___46279$fn__46280.invoke(parameters.clj:85)"
"query_processor.middleware.parameters$fn__46274$substitute_parameters_STAR___46279.invoke(parameters.clj:82)"
"query_processor.middleware.parameters$substitute_parameters$fn__46300.invoke(parameters.clj:114)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__44691.invoke(resolve_referenced.clj:80)"
"query_processor.middleware.expand_macros$expand_macros$fn__45306.invoke(expand_macros.clj:158)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__39442.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__47212.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__46526$fn__46530.invoke(resolve_database_and_driver.clj:33)"
"driver$do_with_driver.invokeStatic(driver.clj:61)"
"driver$do_with_driver.invoke(driver.clj:57)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__46526.invoke(resolve_database_and_driver.clj:27)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__45524.invoke(fetch_source_query.clj:267)"
"query_processor.middleware.store$initialize_store$fn__47221$fn__47222.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:46)"
"query_processor.store$do_with_store.invoke(store.clj:40)"
"query_processor.middleware.store$initialize_store$fn__47221.invoke(store.clj:10)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__44568.invoke(cache.clj:209)"
"query_processor.middleware.validate$validate_query$fn__47230.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__45648.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39280.invoke(add_rows_truncated.clj:36)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__47197.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__44927.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__46389.invoke(process_userland_query.clj:136)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__44870.invoke(catch_exceptions.clj:174)"
"query_processor.reducible$async_qp$qp_STAR___38074$thunk__38075.invoke(reducible.clj:101)"
"query_processor.reducible$async_qp$qp_STAR___38074$fn__38077.invoke(reducible.clj:106)"],
:context :embedded-dashboard,
:error "Cannot run query: missing required parameters: #{"status"}",
:row_count 0,
:running_time 0,
:ex-data {:type :invalid-query},
:data {:rows [], :cols []}}

2

Hi @ritz
If you are embedding, then you need to define the locked parameters in your code. Please post your embed code (with redacted secret).
Have a look at the examples: https://github.com/metabase/embedding-reference-apps
Latest release is 0.38.2

3

Thanks I was able to clear out all errors and don't see any stack trace in Logs. But I still don't understand why my report works when I use Visualization editor and as soon as I convert to native query, the report gives me NO Results. I am posting my native query again below:

{"$project":{"tenant___enterpriseId":"$tenant.enterpriseId","stage___text":"$stage.text"}},{"$match":{"tenant___enterpriseId":{"$eq": {{ enterpriseId }} }}},{"$project":{"_id":"$_id","___group":{"stage___text":"$stage___text"}}},{"$group":{"_id":"$___group","count":{"$sum":1}}},{"$sort":{"_id":1}},{"$project":{"_id":false,"stage___text":"$_id.stage___text","count":true}},{"$sort":{"stage___text":1}}]

Preview of the code when I publish with locked parameter:
var jwt = require("jsonwebtoken");

var METABASE_SITE_URL = "";
var METABASE_SECRET_KEY = "";

var payload = {
resource: { dashboard: 1 },
params: {
"category": "5ccc6587d27de95767793dd1"
},
exp: Math.round(Date.now() / 1000) + (10 * 60) // 10 minute expiration
};
var token = jwt.sign(payload, METABASE_SECRET_KEY);

var iframeUrl = METABASE_SITE_URL + "/embed/dashboard/" + token + "#bordered=true&titled=true";

4

@ritz I think you're hitting a problem with the conversion, which has been fixed on the upcoming 0.39.0, which should be a few weeks from release.

5

Is there any workaround currently available or my only option is to wait for few weeks as I am blocked and cannot generate any reports by embedding into my app?

6

@ritz I would recommend that you follow https://github.com/metabase/metabase/releases since there will be release candidates available fairly soon, which you can then try out.