mongoDB query with case insensitive schema element

Question

In my MongoDB collection I have added a record as follows

db.teacher.insert({_id:1 ,"name":"Kaushik"})

If I search

db.teacher.find({name:"Kaushik"})

I get one record. But if I try "NAME" instead of "name" i.e.

db.teacher.find({NAME:"Kaushik"})

It won't return any record.

It means that I must know how schema element is spelled exactly with exact case. Is there way to write query by ignoring case of schema element.

We can search the element value using case insensitive as follows

> db.teacher.find({name:/kAUSHIK/i})
{ "_id" : 1, "name" : "Kaushik" }

Is there similar for schema element; something like

> db.teacher.find({/NAME/i:"kaushik"})

Not in a good way there isn't. You can write up all the matching in JavaScript but it cannot use indexes or other optimized native features Why would you want to do this anyway? I know MongoDB is schemaless, but still your names of elements should not vary in case between documents just as much as it isn't wise to generally vary structure or types between documents. — user3561036
– user3561036, Commented Jun 17, 2015 at 15:20
How about simply using all lowercase or camelcase keys? Problem solved. — Markus W Mahlberg
– Markus W Mahlberg, Commented Jun 17, 2015 at 15:59
@user3561036 while adding into collection I myself made mistake where I used "name" in one document and "Name" in other. This can happen for some other element "studenId"/"studentid". I agree that element name should be consistent. But for some reason; if they are not; then is there way to handle it; is what I was curious about. — Kaushik Lele
– Kaushik Lele, Commented Jun 18, 2015 at 3:25
@KaushikLele How to handle it is "fix" the incorrect entries. And as for answers here, despite one being "upvoted" and accepted, nothing here even attempts to explain how to "fix" this. — user3561036
– user3561036, Commented Jun 18, 2015 at 3:34

Sylvain Leroux · Accepted Answer · 2015-06-17 16:12:40Z

3

We can search the element value using case insensitive [...]

Is there [something] similar for schema element [?]

No.

We may assume that JavaScript and JSON are case sensitive, and so are MongoDB queries.

That being said, internally MongoDB uses BSON, and the specs say nothing about case-sensitivity of keys. The BNF grammar only said that an element name is a nul terminated modified UTF-8 string:

e_name      ::=     cstring            Key name
cstring     ::=     (byte*) "\x00"     Zero or more modified UTF-8 encoded
                                       characters followed by '\x00'. The
                                       (byte*) MUST NOT contain '\x00', hence
                                       it is not full UTF-8.

But, from the source code (here or here for example), it appears that MongoDB BSON's implementation use strcmp to perform binary comparison on element names, confirming there is no way to achieve what you want.

This might be indeed an issue beyond case sensitivity, as using combining characters, the same character might have several binary representations -- but MongoDB does not perform Unicode normalization. For example:

> db.collection.insert({"é":1})
> db.collection.find({"é":1}).count()
1
> db.collection.find({"e\u0301":1}).count()
0

edited Jun 17, 2015 at 16:12

answered Jun 17, 2015 at 15:36

Sylvain Leroux

52.4k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaushik Lele Over a year ago

Thanks for detailed explanation. Do you think it is worth to report this problem to mongoDB; in case they have not already marked it as "known limitation".

Himen · Accepted Answer · 2015-06-17 15:19:06Z

0

This related to javascript engine and json specification. in js identifiers are case sensitive. This means you can have a document with two field named "name" and "Name" or "NAME". So mongodb act as two distinct filed with your fields.

answered Jun 17, 2015 at 15:19

Himen

1,45012 silver badges18 bronze badges

Comments

Aditya Chopra · Accepted Answer · 2015-06-17 15:28:50Z

-2

You could use a regex like

db.teacher.find({name:/^kaushik$/i})

answered Jun 17, 2015 at 15:28

Aditya Chopra

216 bronze badges

1 Comment

Kaushik Lele Over a year ago

I had asked about case-insensitivity of schema element and not for value.

Collectives™ on Stack Overflow

mongoDB query with case insensitive schema element

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related