MongoDB match expression query not working

Issue

MongoDb query match expression not working.

I have a posts collection and want to return only those posts which match the user Id of the user who created it but my query does not seem to be working.

Sample Dataset

[
  // 1
  {
    "_id": ObjectId("6257047cffd61ab62864c1ae"),
    "type": "A",
    "source": "B",
    "status": "A",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  // 2
  {
    "_id": ObjectId("6257047cffd61ab62864c1ad"),
    "type": "B",
    "source": "A",
    "status": "A",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  // 3
  {
    "_id": ObjectId("6257047cffd61ab62864c1ce"),
    "type": "A",
    "source": "C",
    "status": "B",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  // 4
  {"_id": ObjectId("6257047cffd61ab62864c1cb"),
  "type": "A",
  "source": "B",
  "status": "C",
  "user": ObjectId("622b56250b0af6b049c387d6")
}
]

MongoDb Query:-

db.collection.aggregate([
  {
    $addFields: {
      paramType: "All",
      paramSource: "All",
      paramStatus: "All",
      
    },
    
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            user: ObjectId("622b55ff0b0af6b049c387d3")
          },
          {
            $or: [
              {
                $eq: [
                  "$paramType",
                  "All"
                ],
                
              },
              {
                $eq: [
                  "$paramType",
                  "$type"
                ],
                
              },
              
            ],
            
          },
          {
            $or: [
              {
                $eq: [
                  "$paramSource",
                  "All"
                ],
                
              },
              {
                $eq: [
                  "$paramSource",
                  "$source"
                ],
                
              },
              
            ],
            
          },
          {
            $or: [
              {
                $eq: [
                  "$paramStatus",
                  "All"
                ],
                
              },
              {
                $eq: [
                  "$paramStatus",
                  "$status"
                ],
                
              },
              
            ],
            
          },
          
        ],
        
      },
      
    },
    
  },
  {
    $setWindowFields: {
      output: {
        totalCount: {
          $count: {}
        }
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 6
  },
  {
    "$project": {
      "paramSource": false,
      "paramStatus": false,
      "paramType": false,
      
    }
  }
])

Query Output:-

[
  {
    "_id": ObjectId("6257047cffd61ab62864c1ae"),
    "source": "B",
    "status": "A",
    "totalCount": 4,
    "type": "A",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1ad"),
    "source": "A",
    "status": "A",
    "totalCount": 4,
    "type": "B",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1ce"),
    "source": "C",
    "status": "B",
    "totalCount": 4,
    "type": "A",
    "user": ObjectId("622b55ff0b0af6b049c387d3")
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1cb"),
    "source": "B",
    "status": "C",
    "totalCount": 4,
    "type": "A",
    "user": ObjectId("622b56250b0af6b049c387d6")
  }
]

The query does not work output contains posts created by all users it is not filtering.

Solution

The $match part should look like this:

  {
    $match: {
      $and: [
        {
          user: ObjectId("622b55ff0b0af6b049c387d3")
        },
        {
          $or: [{paramType: {$eq: "All"}},
            {$expr: {$eq: ["$paramType", "$type"]}}
          ]
        },
        {
          $or: [{paramSource: {$eq: "All"}},
            {$expr: {$eq: ["$paramSource", "$type"]}}
          ]
        },
        {
          $or: [{paramStatus: {$eq: "All"}},
            {$expr: {$eq: ["$paramStatus", "$type"]}}
          ]
        }
      ]
    }
  }

The $expr should only be assigned to cases where both values are in the document. This query returns 3 / 4 documents, the ones in which user: ObjectId("622b55ff0b0af6b049c387d3")

BTW, the last 3 conditions on this $match stage are redundant, as they will always be true, since the query sets them with the value ‘All’ on the former stage

Answered By – nimrod serok

Answer Checked By – Candace Johnson (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.