MongoDB C# - filter for findig object by contiditon in nested array

Question

I have a problem to build a filter for geting object by condition in nested array.

My models are:

public class ProductPriceInStore
{
    [BsonId]
    public ObjectId Id { get; set; }

    public ObjectId store { get; set; }

    public ObjectId product { get; set; }

    public string measurementUnit { get; set; }

    public IList<ProductPrices> prices { get; set; }
}

 public class ProductPrices
{
    public double? actualPrice { get; set; }
  
    public double? originalPrice { get; set; }
}

What I want to do is find all ProductPriceInStore, which contains ProductPrice with actualPrice greater than originalPrice

I'm using nugget MongoDB.Driver 2.7.3 in my project

Answer 1

db.ProductPriceInStore.find({$expr:{$gt:["$prices.actualPrice", "$prices.originalPrice"]}})

Answer 2

You can use Linq query selector: if you have a list of ProductPriceInStore so you can do:

ProductPriceInStoreList.Where(item => item.prices.Where(p => p.actualPrice  > p.originalPrice   ))

Answer 3

you can get the desired result with the following aggregation pipeline as $elemMatch cannot be used to compare values of fields in nested objects (afaik).

db.ProductPriceInStore.aggregate(
[
    {
        $set: {
            prices: {
                $filter: {
                    input: "$prices",
                    cond: { $gt: ["$$this.actualPrice", "$$this.originalPrice"] }
                }
            }
        }
    },
    {
        $match: {
            prices: { $gt: [0, { $size: "$prices" }] }
        }
    }
])

this however would be not efficient. a much better approach would be for you to store a boolean property IsGreaterThanOriginalPrice while you're creating/saving the nested items, in which case you can easily use ElemMatch without much hassle.