29

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.

Codehelp
  • 4,157
  • 9
  • 59
  • 96

3 Answers3

37

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.

Aravind Krishna R.
  • 7,885
  • 27
  • 37
  • Aravind, the LOWER does not work. The query will return an empty result when the provided string is converted to LOWER and the actual string in the db is not. – Codehelp May 29 '15 at 05:18
  • 2
    Codehelp, on my query, I'm calling LOWER on the string in the DB, right? I'm assuming the string passed in is lower, but you can instead, do something like LOWER(j.name) = LOWER('timbaktu') – Aravind Krishna R. May 29 '15 at 15:08
  • Aravind, it works when the passed in string, be it LOWER or any other matches what's in the Db. My question is do we have something like 'COLLATE' as in SQL? – Codehelp May 29 '15 at 15:11
  • 1
    There is no equivalent to COLLATE in DocumentDB. Strings are represented in UTF-8 only (per the JSON standard). – Aravind Krishna R. Oct 13 '15 at 20:45
  • 2
    Azure Cosmos DB now supports case-insensitive queries through the functions `Contains`, `EndsWith`, `StartsWith` and `StringsEquals`. https://devblogs.microsoft.com/cosmosdb/new-string-function-performance-improvements-and-case-insensitive-search/ – Martin4ndersen Jun 21 '20 at 16:26
  • @Martin4ndersen it indeed looks like it, but there is no support in the SDK yet so for most of the users it can't be used until then – Jean-Paul Smit Jul 09 '20 at 15:20
  • Using LOWER in a call when trying to return results, such as SELECT * from c WHERE CONTAINS(LOWER(c.firstName), "fred") is massively inefficient and slow. – Brett Rigby Jul 22 '20 at 12:29
  • @AravindKrishnaR. FYI the email in your profile doesn't work. Comes back as Recipient address rejected: access denied – The Muffin Man Jul 01 '22 at 04:50
8

Cosmos recently added a case-insensitive option for string functions:

You now have an option to make these string comparisons case-insensitive: Contains, EndsWith, StringEquals, and StartsWith. and Significant performance improvements have been realized for these string system functions. Each of these four string system functions now benefit from an index and will therefore have much lower latency and request unit (RU) consumption.

Announcement

Noah Stahl
  • 6,905
  • 5
  • 25
  • 36
2

Perhaps this is an ancient case, I just want to provide a workaround.

You could use UDF in azure cosmos db.

udf:

function userDefinedFunction(str){
    return str .toLowerCase();
}

And use below sql to query results:

SELECT c.firstName FROM c where udf.lowerConvert(c.firstName) = udf.lowerConvert('John')

enter image description here

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • 5
    Just wondering - is this a better approach than using the built-in functions (LOWER/UPPER) as mentioned above? – Sean Jan 03 '19 at 22:01