Issues with $gte and $lte for string comparision

My sample table contains data as follows
{ “_id” : 6, “Last_Name” : “Brady”, “First_Name” : “Eoin”}
{ “_id” : 1, “Last_Name” : “Curran”, “First_Name” : “Kevin”}
{ “_id” : 0, “Last_Name” : “Curran”, “First_Name” : “Sam”}
{ “_id” : 7, “Last_Name” : “Hazzard”, “First_Name” : “Aidan”}
{ “_id” : 5, “Last_Name” : “Kelly”, “First_Name” : “Ciaran”}
{ “_id” : 8, “Last_Name” : “Kholi”, “First_Name” : “Virat”}
{ “_id” : 3, “Last_Name” : “Morgan”, “First_Name” : “Eden”}
{ “_id” : 2, “Last_Name” : “Morgan”, “First_Name” : “Eoin”}
{ “_id” : 4, “Last_Name” : “Pollard”, “First_Name” : “Kevin”}
{ “_id” : 9, “Last_Name” : “Sharma”, “First_Name” : “Rohit”}

I am trying to get all documents whose last name less than or equal to C. But it does not bring data starting with “C”.
db.sar.find({“Last_Name”:{$lte:“C”}})
{ “_id” : 6, “Last_Name” : “Brady”, “First_Name” : “Eoin”}
It does not return documents with “C” as last name why?

It works fine with $gte.
db.sar.find({“Last_Name”:{$gte:“C”}})
it brings last name starting with C and others.
Thanks in advance
Sarma

Most likely because any name other than the letter C itself is considered greater than C. See

> db.C.insert( { "_id" : "C" } )
WriteResult({ "nInserted" : 1 })
> db.C.insert( { "_id" : "Ci" } )
WriteResult({ "nInserted" : 1 })
> db.C.insert( { "_id" : "D" } )
WriteResult({ "nInserted" : 1 })
> db.C.find().sort( { "_id" : 1 } )
{ "_id" : "C" }
{ "_id" : "Ci" }
{ "_id" : "D" }

So you should try with

{"Last_Name":{$gte:"C"}}
{"Last_Name":{$lt:"D"}}

And thinking about if

it then confirm what I wrote above any name other than the letter C itself is considered greater than C.

Hi Steeve,

I have already tried that and it is working.

My question is that why it does not bring data when I say $lte : “C”. (less than or equal to C). I am expecting it to bring data for C as well. This works for numeric data but it is not working for string data.
Any thoughts on this please?

Look at the output of my sort above. The name Ci comes after C so it is bigger. And think about it, $lte CANNOT select the same set than $gte EXCEPT for the one that are equals.

Please show me a number that is both $gte and $lte to another one but that is not equal.

Hi Steeve,

Probably I did not explain my question correctly.

Issue : if I run below command

db.test.find({“name”:{$lte: "C}}

It is not returning any names starting with “C”.

$lte : less than or equal right?

This is my issue?

Thanks
Sarma
Please let me know your thoughts.

No you do. But you do not understand my answer. Look back at my sorted output. The string Ci, or any string that starts with C, comes after C so they are $gte. Since they are not equal they are $gt.

Yes. But if Ci, as in my example above, is $gte to C, and then since it is not equal to C, then is not less than equal.

Hi Steeve,

Yes I could follow your example.

Thanks for that information.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.