How to do a Case Insensitive search on Azure DocumentDb?

Question

is it possible to perform a case insensitive search on DocumnetDb?

Let's say I have a record with 'name' key and value as "Timbaktu"

This will work:

select * from json j where j.name  = "Timbaktu" 

This wont:

select * from json j where j.name  = "timbaktu" 

So how do yo do a case insensitive search?

Thanks in advance.

Regards.

Answer 1

There are two ways to do this. 1. use the built-in LOWER/UPPER function, for example,

select * from json j where LOWER(j.name) = 'timbaktu' 

This will require a scan though. Another more efficient way is to store a "canonicalized" form e.g. lowercase and use that for querying. For example, the JSON would be

{ name: "Timbaktu", nameLowerCase: "timbaktu" } 

Then use it for querying like:

select * from json j WHERE j.nameLowerCase = "timbaktu" 

Hope this helps.