CODEWITHSUNDEEP
mongodbaggregation
Rakesh Mhasawade
Rakesh Mhasawade

Reputation: 135

MongoDB aggregation - Match input parameter if provided else do not match

I have a MongoDB aggregation query in which I have the following:

{ $match: { version: versionNumber }

The 'versionNumber' is an optional input parameter to the aggreagation. If this versionNumber is not provided, then I do not want this match to be performed.

Currently, if the versionNumber is not supplied, the match still happens and I get a blank query output.

Is there a way in Mongo to do this? Thanks!

Upvotes: 4

Views: 2988

Answers (5)

Aniket Raj
Aniket Raj

Reputation: 2141

Mongodb playground link: https://mongoplayground.net/p/ZyGO63f2885

We can use $let-vars also but for I have used $addFields instead.

  db.collection.aggregate([
      {
        "$addFields": {
          "versionNumber": null
        }
      },
      {
        $match: {
          $expr: {
            $cond: [
              {
                $ne: [
                  {
                    $ifNull: [
                      "$versionNumber",
                      null
                    ]
                  },
                  null
                ]
              },
              {
                $eq: [
                  "$version",
                  "$versionNumber"
                ]
              },
              true
            ]
          }
        }
      },
      {
        "$project": {
          "versionNumber": 0
        }
      }
    ])

Usecases :

$ifNull operator: The $ifNull operator returns the first argument if it is not null or undefined, otherwise, it returns the second argument.

$addFields stage: This stage is used to add new versionNumber fields to the document to check whether to apply $match condition or not.

$expr operator: The $expr operator allows the use of aggregation expressions with $match operator.

$cond operator:It is used check for if-else conditions.

$ne operator: The $ne operator checks if two values are not equal. In this case, we are checking if the result of the $ifNull operator is not equal to null.

$project : To show/hide variables in the stage.

Upvotes: 0

Ali Ibraheem
Ali Ibraheem

Reputation: 73

You can provide optional parameter to $match in aggregation like this:

$match:{
    "$and": [
        versionNumber ? { "version": parseInt(versionNumber) } : {}
     ]
}

This will match versionNumber only if is defined.

Upvotes: 0

ray
ray

Reputation: 15266

Randomly stumbled upon this post and realized it is a common use case of feeding multiple, optional input values. Here is a generalized template to contruct the $match query:

{
  "$or": [
    {
      "$eq": [
        {
          "$ifNull": [
            <your version number input here>,
            null
          ]
        },
        null
      ]
    },
    {
      "$eq": [
        <your version number input here>,
        "$version" // the field you want to match upon
      ]
    }
  ]
}

How it works:

  1. input safeguarding: used $ifNull to handle the not provided case(i.e. it could be undefined or null). It will fallback to the value null.
  2. input value not provided handling: the input value will be evaluated to null. It will bypass the whole $or block, since the first $eq block is evaluated to true.
  3. input value provided handling: the input value will not pass the first $eq block. Instead, it will be used in evaluation in 2nd $eq block and matched against the field.

With this basic building block, we can chain them up in an $and to build multiple field matching:

{
  "$and": [
    <basic $or block for version field>,
    <basic $or block for another field 1>,
    <basic $or block for another field 2>,
    ...
  ]
}

an actual example would be:

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$or": [
              {
                "$eq": [
                  {
                    "$ifNull": [
                      // your version number input here
                      1,
                      null
                    ]
                  },
                  null
                ]
              },
              {
                "$eq": [
                  // your version number input here
                  1,
                  "$version"
                ]
              }
            ]
          },
          {
            "$or": [
              {
                "$eq": [
                  {
                    "$ifNull": [
                      // your name input here
                      null,
                      null
                    ]
                  },
                  null
                ]
              },
              {
                "$eq": [
                  // your name input here
                  null,
                  "$name"
                ]
              }
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground

Upvotes: 0

turivishal
turivishal

Reputation: 36114

I am not sure what will be the value in versionNumber when its not provided (optional), lets assume versionNumber will be any from "" or undefined or null,

  • if versionNumebr is not available then it will skip and when its available then it will match $eq condition
  • you can add more values in array [null, "", "undefined"], 0 zero or anything you wanted to skip
  {
    $match: {
      $expr: {
        $cond: [
          { $in: [versionNumber, [null, "", "undefined"]] },
          true,
          { $eq: ["$version", versionNumber] }
        ]
      }
    }
  }
  • if versionNumebr will be always single possible value "" then you can use $eq instead of $in,
  {
    $match: {
      $expr: {
        $cond: [
          { $eq: [versionNumber, ""] },
          true,
          { $eq: ["$version", versionNumber] }
        ]
      }
    }
  }

Playground

Upvotes: 2

Joe
Joe

Reputation: 28356

There is a way to do that, yes, but it should be done in the application code. When building the pipeline array to pass to the query, only include the $match stage if the necessary information is provided.

var pipeline=[]
if (versionNumber) pipeline.push( {$match: {version: versionNumber }} )
pipeline.push( ... Other Stages ... )

db.collection.aggregate(pipeline)

Upvotes: 1

Related Questions