Quick question about search queries

Working with Data
,
1

I’m working with javascript and mongoDB. Is there a way to search without it being case sensitive? So I’m looking for a document, which has one of it’s properties set to “Apple”, but it should also return for “apple”, “APPLE” or “aPpLe”

I could just make the search query lower case from the javascript side and then match it to the values, but I didn’t have this in mind when creating the data so I would have to go over them and edit everything to lower case, so figured I’d ask for a possible easier solution

2

Hi @Lord_Wasabi

Collation to the rescue. It allows language-specific rules for string comparison like lettercase and more. The documentation goes into a lot more detail.

This could also be set the index level, or specified directly in the query.

Here’s a snippet in action. It has been tested on 4.4

    db.createCollection("fruits", { collation: { locale: 'en_US', strength: 2, caseLevel: false } } )

    db.fruits.insert( [ { type: "apple" },
                       { type: "aPpLe" },
                       { type: "APPLE" } ] )
                       
    db.fruits.find( { type: "apple" } ) //this will match and return all three docs

    //specify at the query level
    db.fruitsQueryLevel.insert( [ { type: "apple" },
                       { type: "aPpLe" },
                       { type: "APPLE" } ] )
                       
    db.fruitsQueryLevel.find({ type: "apple" }).collation( { locale: "en_US", strength: 2, caseLevel: false } ) //will return all three docs

Let me know if this helps.

Cheers,
Mahi

1 Like
3

Hello, thank you for your reply. Upon trying out the method where I specify it at the query level, I’m getting an error stating .collation is not a function. Do you have any idea about what did I do wrong?

This is my code:

await client.db.itemdata.findOne({displayName: input}).collation( { locale: "en_US", strength: 2, caseLevel: false } )
4

With the node-js driver you specify the collation in a slightly different way.

See Class: Collection for more details.

1 Like
5

This helped to make it work, thank you

6

Keep in mind that any indexes created with a specific collation can only be used with queries that specify the same collation, so be sure you want this collation used for everything in this collection…

If you did want to convert your existing data in a particular field to be lower case, or create a new field that contains the same string in lower case, you can do that with a single multi-update (as long as you’re on 4.2 or later):

 db.coll.updateMany({}, [ {$set:{lowerCaseField:{$toLower:"$field"}}} ])

The above creates a new field but you could do the same update with field:{$toLower:"$field"} to change existing field from mixed case to all lower case.

Asya

2 Likes
7

Thank you for the answer, can you specify what you mean exactly in the first paragraph? What I used this system for seems to work fine, but I’m interested what I should be keeping in mind about it

8

When you create a collection specifying a particular collation, all the indexes and queries for that collection will by default use that collation. @mahisatya made a reference to that when he said collation can also be specified at the index level, or directly in the query.

The caution is around the fact that if you specify one collation when creating an index, using a different collation to query that collection will not be able to use an index with different collation and will use a collection scan instead.

Asya

1 Like