{"$group":{"_id":{"statusRepositoryId":"$statusRepositoryId","vendorId":"$vendorId"},"count":{"$sum":1}}},{"$sort":{"_id":1}},{"$project":{"_id":false,"statusRepositoryId":"$_id.statusRepositoryId","vendorId":"$_id.vendorId","count":true}},{"$sort":{"count":-1,"statusRepositoryId":1,"vendorId":1}}
{"$lookup": {
"from": "StatusRepository",
"localField": "statusRepositoryId",
"foreignField": "_id",
"as": "R"
{"$unwind": "$R"},
{"$lookup": {
"from": "Vendor",
"localField": "vendorId",
"foreignField": "_id",
"as": "V"
{"$unwind": "$V"},
"$project": {
"Status value": "$R.status",
"count":true,
"vendorName":"$V.name"
"vendorId":"$V._id"
"$match":{"vendorId" : ObjectId("5ee07336e3647639d9b9b068")}
I want to add a field filter at the end of the query. Here I have hard-coded a value because the value is achieved by joining the tables. can we do this in Metabase. Field filter works without aliasing so how would it interpret a joined table entry? I have tried adding it but it ends up giving nothing
Hi
@Anum
Mongo works very differently from other databases.
You need to move the
match
below the
project
:
https://stackoverflow.com/questions/33752817/project-with-match-in-aggregate-not-working-in-mongodb
I have already moved the match clause below the project and it works on the given hardcoded value.
VendorId is a achieved by joining two tables. and now I want a filter on vendor id using a field filter.
"$match":{"vendorid" : {{id}}}
The above does it by using the id as a text field. but i want a dropdown.
if i change it to this and make it a field filter on id in the vendor table, it fails to give me the result.
"$match":{{{id}}}
@Anum
Okay, I don't know how you have configured your Field Filter, so I guess the problem is there.
I would recommend that you create a View on Mongo, so Metabase just sees a regular "table":
https://docs.mongodb.com/manual/reference/method/db.createView/
Does the filter fails to work if the category has space?
I have a category with two words separated by space.
Whenever i put in a name, it gives me an empty result.
@Anum
Here's what I recommend you'll do. Enable query logging on your MongoDB, so you can see which queries it receives:
https://stackoverflow.com/questions/15204341/mongodb-logging-all-queries
@flamber
It was not related to mongo. it was related to Metabase. Posting the solution so that others can get benefit.
So basically, if the case of the name matters. It should be
{ "$project": {
"Name":"$R1.Name",
"riderId":"$R1._id"
"$match": {{Name}}
