Querying in Azure Cosmos DB just got even better! You now have an option for case-insensitive queries with the following string search system functions:<\/p>\n
Additionally, both Contains and EndsWith also have significant performance improvements. This update was rolled out to Azure Cosmos DB core (SQL) API accounts in our most recent service update. Customers using Azure Cosmos DB\u2019s API for MongoDB accounts can leverage the case-insensitive support and performance improvements through the $regex<\/a> query evaluation operator.<\/p>\n Let\u2019s test the performance improvements by running some queries on a sample dataset with 8.5 million documents. I\u2019ve uploaded the dataset to a Cosmos container with 30,000 provisioned RU\u2019s.<\/p>\n The dataset was generated using Bogus <\/a>and contains a unique id identifier, a name, an address, a company, and a job:<\/p>\n Let\u2019s first do a search for all the people that live on a street named \u201cBrooks Street\u201d. We\u2019ll run the following query to check if the address property contains the word \u201cBrooks Street\u201d:<\/p>\n The query returns 17 results with the following RU charges:<\/p>\n Original RU charge:<\/strong> 221,566.68 RUs<\/p>\n New RU charge:<\/strong> 224.99 RUs<\/p>\n The performance improvement for Contains<\/strong>, gave this query over a 99% decrease in RU charge! Aside from indexing the property in the Contains<\/strong> system function, there are no other changes you need to make to see these improvements.<\/p>\n <\/p>\n Let\u2019s look at an example with EndsWith<\/strong>. Here\u2019s a query that finds all the people that have a name that ends in \u201cLee\u201d:<\/p>\n This query returns 0 results with the following RU charges:<\/p>\n Original RU charge:<\/strong> 198,649.32 RUs<\/p>\n New RU charge:<\/strong> 122.76 RUs<\/p>\n <\/p>\n Additionally, you can take advantage of a new parameter in each of these system functions to get case-insensitive support. This parameter is optional and defaults to false when unspecified. The RU charge for Contains and EndsWith is the same regardless of whether they are case-insensitive.<\/p>\n Here\u2019s an example with StartsWith<\/strong> to find the TOP 100 job titles that start with \u201cdeveloper\u201d whether the case matches or not:<\/p>\n RU charge:<\/strong> 38.91 RUs<\/p>\n <\/p>\n If you wanted to check for a full match, you could use StringEquals<\/strong>:<\/p>\n RU charge:<\/strong> 38.89 RUs<\/p>\n The RU charge for StartsWith and StringEquals is slightly higher with the case-insensitive option than without it. The case-insensitive option is available in the latest version of all Azure Cosmos DB SDK’s. For the .NET SDK, this is version 3.10 or later.<\/p>\n In general, Contains<\/strong> and EndsWith<\/strong> will consume more RUs than StartsWith<\/strong> or StringEquals<\/strong>. The RU charge of Contains<\/strong> and EndsWith<\/strong> will increase as the cardinality of the property in the system function increases. Learn more about Contains<\/strong>\u00a0<\/a>and EndsWith<\/strong><\/a> index utilization.<\/p>\n We hope you try out these new query features! Here\u2019s a query lab<\/a>\u00a0with some sample data to get started. Beyond adding an index<\/a> for properties in the system functions, there is nothing else you need to do to leverage these significant improvements! If you are interested in additional text search functionality, you can leverage Azure Cosmos DB’s integration with Azure Cognitive Search<\/a>.<\/p>\n <\/p>\nTesting performance<\/h2>\n
{ \u00a0\u00a0 \r\n\"id\": \"ym570004cj\", \u00a0\u00a0 \r\n\"name\": \"Evans Kilback\", \u00a0\u00a0 \r\n\"address\": \"4561 Abernathy Station, Hahnmouth, Ecuador\",\r\n\"company\": \"Terry, Lind and Bogan\",\r\n\"job\": \"Administrator\" \r\n}<\/pre>\nContains<\/h3>\n
SELECT * FROM c \r\nWHERE CONTAINS(c.address, \"Brooks Street\")\r\n<\/pre>\n
EndsWith<\/h3>\n
SELECT * FROM c \r\nWHERE EndsWith (c.name, \"Lee\")<\/pre>\n
StartsWith<\/h3>\n
SELECT TOP 100 * FROM c\r\nWHERE StartsWith(c.job, \"developer\", true)<\/pre>\n<\/div>\n
StringEquals<\/h3>\n
SELECT TOP 100 * FROM c\r\nWHERE StringEquals(c.job, \"developer\", true)<\/pre>\n<\/div>\n
Next steps<\/h2>\n