Build this LEFT() “SQL” in MongoDB Query?

问题:

Have an BsonDocument collection with PhoneNumber in the "1234567890" format. This SQL gets all the PhoneNumbers with the Area Codes between 300 and 399;

WHERE (LEFT(PhoneNumber,3) BETWEEN '300' AND '399') 

How would I do this in MongoDB? I would like to use the MongoDB.Driver.Builders if possible.

回答1:

If you just want phone number that's starts from number '3' you can just use smart decision of @mstearn, here just c# realization:

var query = Query.EQ("PhoneNumber", new BsonRegularExpression("^3"));

But lets say if you need query first 3 numbers in range 345 -- 369 to make it work (without slow operators: $where, $regex) you can create additional field and store there first 3 numbers (area code) of phone. And then use query proposed by @yi_H , here again c# driver realization:

var query = Query.GTE("PhoneAreaCode", 345).LTE(369);

Don't care about extra field in mongodb -- it's common practice. Extra fields usual working faster than any calculation during querying.

回答2:

{'PhoneNumber': {'$gte': '300', '$lt': '400'}}

回答3:

{'PhoneNumber': /^3/ } or {'PhoneNumber': {'$regex': '^3'}}