Add a match statement with a field filter at the end in mongodb

Get Help
#1 
[
[[
  {
    "$match": {
      "$and": [
        {{Daterange}}
      ]
    }
  },
]]
{"$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

#2

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

#3

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}}}
  }
]]
#4

@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/

#5

Can we apply a field filter to a value obtained by joining the two tables?

#6

I didn't get your point. Do you get the question exactly?

#7

@Anum Yes:

image
image.png1920×971 109 KB

But again, I would highly recommend that you just create Views on your database, so you can use regular "tables" and the GUI in Metabase. It would save you so much problems.

1 Like
#8

So, the name should be same. Thank you. This is what worked.

#9

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.

image
image.png3737×1063 225 KB

#10

@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

#11

@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}}
  },
]]
#12

@Anum It was not related to Metabase. It was related to Mongo and the query you wrote. Yes, Mongo is case-sensitive, so in that sense, your queries should also be.