Does MongoDB support optional filters?

[George_Ponta]

I have a case where I want to run a query that MUST match some field conditions but SHOULD match some others. If they don’t match however the query should still return the conditions that MUST.

For example let’s say in a collection I have 3 documents such as:

{ _id: 1, position: “Developer”, name: “Greg”, surname: “Smith” },
{ _id: 2, position: “QA”, name: “Andrew”, surname: “Samson” },
{ _id: 3, position: “Developer”, name: “Adam”, surname: “Mount” }

If I run a query with a condition { position: “Developer” } that is a MUST and { name: “Greg” } that is a SHOULD I should just get the record:

{ _id: 1, position: “Developer”, name: “Greg”, surname: “Smith” }

However, if I run the query again with { position: “Developer” } and { “name”: “Daniel” }, I should get all records that match the MUST condition. So return records:

{ _id: 1, position: “Developer”, name: “Greg”, surname: “Smith” },
{ _id: 3, position: “Developer”, name: “Adam”, surname: “Mount” }

Furthermore, if I have a query with MUST conditon { position: “Developer” } and SHOULD conditions { name: “Greg”, surname: “Garbrandt” } I should still get:

{ _id: 1, position: “Developer”, name: “Greg”, surname: “Smith” }

Not sure if there is a way to write the query to work like this or if there is a functionality that could do this outright.

[steevej (Steeve Juneau)]

Take a look at https://www.mongodb.com/docs/atlas/atlas-search/compound/#std-label-compound-ref.

If not suitable, you may experiment with something like the following.

1. Start with $match stage for the MUST part of your queries.
2. Do 2 $facets, one that implements the SHOULD part while the other one implements the negation of the SHOULD part

must = { "position" : "Developer" } 
should = { "name" : "Greg" , "surname" : "Garbrandt" }
match_must = { "$match" : must }
match_should = { "$match" : should }
not_match_should = { "$match" : { "$not" : should } }
c.aggregate( [
  match_must ,
  "$facet" :
  {
    "should" : [ match_should ] ,
    "should_not" : [ not_match_should ]
    // would be nice not to run the should_not $facet if the should has some result.
  } ,
  // $project the should facet if it is not empty, otherwise $project the should not.
] )

The $unionWith might also be promising if all else fail.

[Pavel_Duchovny]

Hi @George_Ponta

Please note that the should and must come conditions are very good in Atlas search. I recommend running on Atlas for this robust capabilities:

compound — MongoDB Atlas

Thanks
Pavel