Question 117102 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Filter $gte and $lte a string in C#

how to add filter with $gte and $lte a string value? I have a StaredAt but is a string type. How to filter in a range, for example:

Range: 2022-02-02 / 2022-02-04

I tried this but throw and exception:

var subfilter = new BsonDocument("$expr",
                new BsonDocument("$gte",
                    new BsonArray {
                        new BsonDocument("$toDate", "$StartDate"),
                        From
                    }
                )
            );

        var subfilterTo = new BsonDocument("$expr",
                new BsonDocument("$lte",
                    new BsonArray {
                new BsonDocument("$toDate", "$StartDate"),
                To
                    }
                )
            );

The exception is:

MongoDB.Driver.MongoCommandException: Command find failed: An object representing an expression must have exactly one field: { $gte: [ { $toDate: "$StartDate" }, new Date(1663124400000) ], $lte: [ { $toDate: "$StartDate" }, new Date(1663210799000) ] }
Tags:

2

Answers


  1. 0

    I would recommend using the C# Mongodb Linq provider.

    That way you can avoid the nesting of the BsonDocuments and it can be as simple as:

    collection.AsQueryable().Where(x => x.From > StartDate && x.To < EndDate)

    Login or Signup to reply.
  2. 0

    In order to use $expr to convert the strings to dates, you can create a single expression with a AND condition that combines both conditions:

    new BsonDocument("$expr", new BsonDocument("$and", new BsonArray
        {
            new BsonDocument("$gte", 
            new BsonArray
                {
                    new BsonDocument("$toDate", "$StartDate"),
                    From
                }),
            new BsonDocument("$lte", 
            new BsonArray
                {
                    new BsonDocument("$toDate", "$StartDate"),
                    To
                })
        }));

    This is equivalent to the following query:

    {
  $expr: {
    $and: [
      { $gte: [{ $toDate: "$StartDate" }, From] },
      { $lte: [{ $toDate: "$StartDate" }, To] },
    ];
  }
}

    As a sidenote: if you convert the dates from string to date data types in the documents, this simplifies the query and will improve query performance because you can use an index.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search