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

Anum · November 8, 2021, 11:41am

[
[[
  {
    "$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

flamber · November 8, 2021, 11:54am

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

Anum · November 8, 2021, 11:59am

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

flamber · November 8, 2021, 12:06pm

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

Anum · November 8, 2021, 12:06pm

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

Anum · November 8, 2021, 12:07pm

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

flamber · November 8, 2021, 12:10pm

@Anum Yes:

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.

Anum · November 8, 2021, 12:28pm

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

Anum · November 12, 2021, 2:56pm

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.

flamber · November 12, 2021, 3:04pm

@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

Anum · November 12, 2021, 8:25pm

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

flamber · November 12, 2021, 8:29pm

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

sharayu · November 15, 2023, 6:58am

@Anum did it work for you? As it's not working for me. Or you found any other solution?

qatest · March 20, 2024, 8:53am

Filter does not reflect on dashboard