Query items from CosmosDB based on presense or absense of complex object property

In my previous article I showed how to query items from CosmosDB using conditions against nested properties: see Query nested data in CosmosDB. In this post I will show how to query items based on presence or absence of complex object property. Let's use the same example which was used in mentioned post:

[{
    "id": "1",
    "title": "Foo",
    "metadata": {
        "metadataId": "123",
        "fieldValues": [
            {
                "fieldName": "field1",
                "values": [
                    "val1"
                ]
            },
            {
                "fieldName": "field2",
                "values": [
                    "val2"
                ]
            },
            {
                "fieldName": "field3",
                "values": [
                    "val2"
                ]
            }
        ]
    }
},
{
    "id": "2",
    "title": "Bar",
},
...
]

So we have 2 objects Foo and Bar. Foo object has metadata property while Bar doesn't. How to fetch all objects which have metadata property? And vice versa: how to fetch those objects which don't have it?

The first attempt would be to compare it with null:

SELECT * FROM c WHERE c.metadata != null

And it works. However if we will try to use the same technique for fetching all items which don't have metadata the following query won't return any results:

SELECT * FROM c WHERE c.metadata = null

Better option which works in both cases will be to use helper function IS_DEFINED. With this function in order to fetch all items which have metadata property we may use the following query:

SELECT * FROM c WHERE IS_DEFINED(c.metadata)

And similarly we may fetch objects which don't have metadata:

SELECT * FROM c WHERE NOT IS_DEFINED(c.dynamicMetadata)

This technique is more universal since it works in both scenarios.

Query nested data in CosmosDB

Let's say we have objects in CosmosDB with complex nested structure:

[{
	"id": "1",
	"title": "Foo",
	"metadata": {
		"metadataId": "123",
		"fieldValues": [
			{
				"fieldName": "field1",
				"values": [
					"val1"
				]
			},
			{
				"fieldName": "field2",
				"values": [
					"val2"
				]
			},
			{
				"fieldName": "field3",
				"values": [
					"val2"
				]
			}
		]
	}
},
...
]

And we need to fetch items items by metadata values stored in field1, field2, etc. Using above example if we need to fetch objects which have metadata.metadataId = "123", field1.value = "val1" and field2.value = "val2" we should use the following query:

SELECT * FROM c
WHERE c.metadata.metadataId = '123' AND
EXISTS(SELECT VALUE fv.fieldName FROM fv IN c.dynamicMetadata.fieldValues WHERE fv.fieldName = 'field1' AND
    EXISTS(SELECT VALUE v FROM v IN fv.values WHERE v = 'val1'))
AND
EXISTS(SELECT VALUE fv.fieldName FROM fv IN c.dynamicMetadata.fieldValues WHERE fv.fieldName = 'field2' AND
        EXISTS(SELECT VALUE v FROM v IN fv.values WHERE v = 'val2'))

If values for some field contain multiple values we may construct even more complex conditions by combining conditions for single field values by AND or OR operators depending on requirements.

CosmosDB: compare queries performance against strings and array properties

Imagine that we have CosmosDB instance running in Azure (created with SQL syntax enabled) which has groups entities. Each group has members property - JSON array of members of this group. Also each group has the same members serialized to string in membersStr property:

{
    "id": "cf66e4ea-39b6-43bf-a237-8ae997c8cd6a",
    "membersStr": "[{\"loginName\":\"user.automatic17@mytenant.onmicrosoft.com\",\"displayName\":\"User Automatic17\",\"id\":\"53bcf941-fa64-4af2-ac47-c7f48430df29\"},{\"loginName\":\"user.automatic52@mytenant.onmicrosoft.com\",\"displayName\":\"User Automatic52\",\"msGraphUserId\":\"515ee5ae-71dc-4c5a-a844-41af5f9d419c\"},{\"loginName\":\"user.automatic67@mytenant.onmicrosoft.com\",\"displayName\":\"User Automatic67\",\"msGraphUserId\":\"21106ae1-25fb-43ac-a5e5-aa1f0d3f9f2f\"}]",
    "members": [{
        "loginName": "user.automatic17@mytenant.onmicrosoft.com",
        "displayName": "User Automatic17",
        "id": "53bcf941-fa64-4af2-ac47-c7f48430df29"
    }, {
        "loginName": "user.automatic52@mytenant.onmicrosoft.com",
        "displayName": "User Automatic52",
        "msGraphUserId": "515ee5ae-71dc-4c5a-a844-41af5f9d419c"
    }, {
        "loginName": "user.automatic67@mytenant.onmicrosoft.com",
        "displayName": "User Automatic67",
        "msGraphUserId": "21106ae1-25fb-43ac-a5e5-aa1f0d3f9f2f"
    }]
	...
}

The question which we wanted to clarify is if we want to get groups where specific user is member - what query will be faster:

1. which uses membersStr property and LIKE operator:

SELECT c FROM c WHERE c.membersStr LIKE '%{userId}%'

2. or which uses array and object-oriented query against members properties:

SELECT c FROM c JOIN m IN c.members WHERE m.id = '{userId}'

For running this test I used CosmosDB with 10K entities with random users data. For testing I used the following C# methods:

private static List<object> GetGroupsByArray(string userId)
{
    string query = $"SELECT c FROM c JOIN m IN c.members WHERE m.id = '{userId}'";
    QueryDefinition queryDefinition = new QueryDefinition(query);
    var groupsContainer = cosmosClient.GetContainer(DBName, ContainerName);
    FeedIterator<object> queryResultSetIterator = groupsContainer.GetItemQueryIterator<object>(queryDefinition);
    var groups = new List<object>();
    while (queryResultSetIterator.HasMoreResults)
    {
        FeedResponse<object> currentResultSet = queryResultSetIterator.ReadNextAsync().GetAwaiter().GetResult();
        foreach (var group in currentResultSet)
        {
            groups.Add(group);
        }
    }

    return groups;
}

private static List<object> GetGroupsByString(string userId)
{
    string query = $"SELECT c FROM c WHERE c.membersStr LIKE '%{userId}%'";
    QueryDefinition queryDefinition = new QueryDefinition(query);
    var groupsContainer = cosmosClient.GetContainer(DBName, ContainerName);
    FeedIterator<object> queryResultSetIterator = groupsContainer.GetItemQueryIterator<object>(queryDefinition);
    var groups = new List<object>();
    while (queryResultSetIterator.HasMoreResults)
    {
        FeedResponse<object> currentResultSet = queryResultSetIterator.ReadNextAsync().GetAwaiter().GetResult();
        foreach (var group in currentResultSet)
        {
            groups.Add(group);
        }
    }

    return groups;
}

Each method was called with 10 random user ids. In both cases time of query depended from number of groups user belongs to (the more groups user was member of the more time query took). I made 10 test runs of both methods and measured execution time with System.Diagnostics.Stopwatch. Then I calculated average execution time for each run of 1st method and separately for 2nd method. Finally I measured average execution time for all 10 test runs.

Result was that query against string property (JSON-serialized array) was a bit faster (929.53 milliseconds) than query agianst array property (1362.3). Conclusion is that if performance is not critical for you and you want to have nice-looking entities in CosmosDB you may use array properties. But if performance is critical you may consider to use JSON-serialized property (or use both as an option).

Update 2022-04-14: we also compared internal query engine execution time and got different results. For array-based approach performance was better. For string-based approach query took 38ms:

while for array-based approach only 18ms:

Probably .Net SDK which was used during 1st test added own delays. Consider that when you will choose approach for your scenario.

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.

Compare performance of Azure CosmosDB (with Azure Table API) vs Table storage

As you probably know Table storage is basic NoSQL db option available in Azure. It is very cheap and used in many Azure components internally (e.g. in Azure functions or bots). From other side CosmosDB is powerful NoSQL db engine available in Azure which support several different APIs. You need to select this API when create CosmosDB instance and it can't be changed after that:

• Core (SQL) 
• MondoDB
• Cassandra
• Azure Table
• Gremlin
  

I.e. if you have existing application which already uses Table storage - you may create CosmosDB with Azure Table API and switch to it without rewriting application code (although this is in theory. E.g. if you app targets .Net Framework you will most probably need to retarget it to .Net Standard or .Net Core. But this is topic of different post).

In our product we use Table storage and decided to compare performance of CosmosDB (with Azure table API) and native Table storage. For testing the following data was used:

• table with about 100K rows
• query against system PartitionKey field: PartitionKey eq {guid} (this is fast query itself because PartitionKey is indexed field)
• each query returned about 300 rows from overall 100K rows
• execution time was measured
  

Same test had been ran 10 times in order to get average execution time - for CosmosDB and Table storage separately. Here are results:

Test run#	CosmosDB (msec)	Table storage (msec)
1	3639	2825
2	3249	2008
3	3626	1613
4	3016	1813
5	2764	1942
6	2840	1713
7	4704	1776
8	2975	2036
9	3353	1808
10	2833	1699
Avg (msec)	3299.9	1923.3

I.e. average execution time for native Table storage was less than for CosmosDB with Azure Table API.