Metabase Mongodb simple join group by query not working

Get Help
1

I am trying to create a new question (report) using below criteria for Mongodb but after entering below native query it’s giving me no results after trying multiple things:

db.getCollection(“opportunity”).aggregate(
[
{
“$project” : {
“_id” : NumberInt(0),
“opportunity” : “$$ROOT”
}
},
{
“$lookup” : {
“localField” : “opportunity.stage”,
“from” : “master_list”,
“foreignField” : “_id”,
“as” : “master_list”
}
},
{
“$unwind” : {
“path” : “$master_list”,
“preserveNullAndEmptyArrays” : false
}
},
{
“$match” : {
“master_list.type” : “opp_stage”,
“opportunity.status” : “open”
}
},
{
“$group” : {
“_id” : {
“opportunity᎐stage” : “$opportunity.stage”,
“master_list᎐text” : “$master_list.text”
},
“COUNT(opportunity᎐stage)” : {
“$sum” : NumberInt(1)
}
}
},
{
“$project” : {
“COUNT(opportunity᎐stage)” : “$COUNT(opportunity᎐stage)”,
“master_list.text” : “$_id.master_list᎐text”,
“_id” : NumberInt(0)
}
}
],
{
“allowDiskUse” : true
}
);

The above “Query code” is equivalent to below “SQL Query”
select count ( opportunity.stage ), master_list.text from opportunity inner join master_list
on opportunity.stage = master_list._id
where master_list.type = “opp_stage”
and opportunity.status = “open”
group by opportunity.stage , master_list.text

2

Hi @ritz
If you’re having problems with a Native query, then you’ll likely find better help on stackoverflow.com or similar forums, since it’s not specific to Metabase.

3

Hi @flamber - sorry if it was not clear in my earlier post. The native query gives me result when I run it in mongodb shell but the same query when I use with Native Query option on Metabase does not give me any result.

4

@ritz
Please post “Diagnostic Info” from Admin > Troubleshooting.

Just to understand you completely, you say that if you run this query directly on Mongo shell, then it works, but when you use Metabase, then it doesn’t return anything?

Are there any errors in the log, when you run the query? Admin > Troubleshooting > Logs.

My first guess is that it has something to do with privileges. You’re probably not using the same credentials on shell vs Metabase.
I have no knowledge of Mongo, but with many other databases, you can activate debug logging, which will tell exactly what query and credentials the database is running.