Mongo Query to filter using multiple conditions together-CodePudding

How do you find documents with ALL value types or some value in MongoDB?

I have some fields in the document which I want to use as filters to filter documents.

Example:-

Person field can take one of the following values:- [Person A, Person B, Person C,....]

Type field can take one of the following values:- [Type A, Type B, Type C]

Source field can take one of the following values:- [Source A, Source B, Source C]

I want to use the above-mentioned fields together to filter the documents.

The above-mentioned fields should be filtered as All or some specific value of the field.

what I mean is

for a query, I want all the Persons of Type A of Source B.
for another query, I may want Person B of All Type for Source C.

and so on...

I want to use all the fields combined in the query to filter the documents.

How Do you achieve this in MongoDB?

Dataset:-

{
    "_id": "1",
    "Person": "Person A",
    "type": "Type A",
    "source": "Source A",
    "subject": "some subject"
},
{
    "_id": "2",
    "Person": "Person B",
    "type": "Type B",
    "source": "Source C",
    "subject": "some subject"
},
{
    "_id": "3",
    "Person": "Person C",
    "type": "Type A",
    "source": "Source A",
    "subject": "some subject"
},
{
    "_id": "4",
    "Person": "Person C",
    "type": "Type C",
    "source": "Source B",
    "subject": "some subject"
},
{
    "_id": "5",
    "Person": "Person A",
    "type": "Type B",
    "source": "Source A",
    "subject": "some subject"
},
{
    "_id": "6",
    "Person": "Person B",
    "type": "Type C",
    "source": "Source A",
    "subject": "some subject"
}

Example Filter Query 1:-

Person: All (i.e A,B,C)

Type: A

Source: A

Expected Output for query 1:-

get documents with id:- 1 and 3 Coz only ids 1&3 have Type and Source as A

Example Filter Query 2:-

Person: A

Type: All

Source: ALL

Expected Output for query 2:-

get documents with id:- 1 and 5 coz person A has documents 1 & 5 associated with him.

Example Filter Query 3:-

Person: ALL

Type: All

Source: B

Expected Output for query 2:-

get documents with id:- 4 coz only id 4 has source as B

CodePudding user response：

While performing querying with $eq, you can use $or to "escape" the query when the param supplied is "All".

db.collection.aggregate([
  {
    "$addFields": {
      // put your query param here
      "paramPerson": "All",
      "paramType": "Type A",
      "paramSource": "Source A"
    }
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $or: [
              {
                $eq: [
                  "$paramPerson",
                  "All"
                ]
              },
              {
                $eq: [
                  "$paramPerson",
                  "$Person"
                ]
              }
            ]
          },
          {
            $or: [
              {
                $eq: [
                  "$paramType",
                  "All"
                ]
              },
              {
                $eq: [
                  "$paramType",
                  "$type"
                ]
              }
            ]
          },
          {
            $or: [
              {
                $eq: [
                  "$paramSource",
                  "All"
                ]
              },
              {
                $eq: [
                  "$paramSource",
                  "$source"
                ]
              }
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      "paramPerson": false,
      "paramSource": false,
      "paramType": false
    }
  }
])

Here is the Mongo playground for your reference.