Use SQL LIKE operator in CosmosDB

One of known pain of developers who works with Azure table storage is lack of SQL LIKE operator which allows to perform queries with StartsWith, EndsWith or Contains conditions. There is another NoSQL database available in Azure - CosmosDB. It is not free (although Table storage is also not free but is very cheap) but with some notes it supports LIKE operator.

The first thing you should do when create new CosmosDB instance is to select underlying API:

At the moment of writing this post the following APIs were available:

• Core (SQL)
• MongoDB
• Cassandra
• Azure table
• Gremlin
  

If you will create CosmosDB with Azure table API - it will look the same as native table storage and will have the same 6 operations available for queries: <, <=, >, >=, =, <>. I.e. no LIKE operator still:

But if you will create it with Core (SQL) API it will look different (it will work with "documents" instead of tables and rows):

and which is more important will allow to use SQL-like API and queries from Microsoft.Azure.Cosmos nuget package. E.g. if we have Groups table we may fetch groups which have some user as member using the following query (here for simplicity we assume that members are stored in JSON serialized string):

string userName = ...;
string sqlQueryText = $"SELECT * FROM c WHERE c.members like '%{userName}%'";

var queryDefinition = new QueryDefinition(sqlQueryText);
var queryResultSetIterator = this.container.GetItemQueryIterator<Group>(queryDefinition);
var items = new List<Group>();
while (queryResultSetIterator.HasMoreResults)
{
    var currentResultSet = await queryResultSetIterator.ReadNextAsync();
    foreach (var item in currentResultSet)
    {
        items.Add(item);
    }
}

So if you need NoSQL database with support of LIKE operator consider using CosmosDB with Core (SQL) API.