Search code examples
mongodbmongoosemongodb-queryaggregation-frameworkaggregation

MongoDB Aggregation - Check the second value if the first is null in

I have this object in my products schema:

{
  price: {
    v1: number,
    v2: number
  }
}

price.v1 is always required and not null, on the other hand, price.v2 is not required and can be null.

I want to search the products with the price > 20.

If the price.v2 is null, I need to check with the price.v1 > 20.

If the price.v2 is not null, I need to check with the price.v2 > 20.

How can I do this in an aggregation query?

Solution

  • Work with $ifNull operator. The query will filter with price.v2 first when it is not null. Otherwise, it will use price.v1 to filter.

    db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $ifNull: [
              "$price.v2",
              "$price.v1"
            ]
          },
          20
        ]
      }
    }
  }
])

    Demo @ Mongo Playground