Get the Record Count in Cosmos DB

When working with Azure Cosmos DB, it is guaranteed that at some point that you'll need to get the record count of a document. There are a couple of quick ways of how to do this through the Azure Portal by navigating to the Cosmos DB resource you wish to query and selecting the Data Explorer tab and using the following query: SELECT VALUE COUNT(1) FROM c

If you’re wondering about the VALUE keyword – all queries return JSON fragments back. By using VALUE, you can get the scalar value of count e.g., 100, instead of the JSON document {"$1": 100}

You may want to know the Request Charge for that query and you can see that by clicking on Query Information.

If you compare that to some of the older methods folks used before COUNT was available such as SELECT c.id FROM c then you'd see that it might be time to update the queries.

If I need to put this logic inside of an application instead of access it through the Azure Portal, here is how I do that in C#.

First, I create a app.config file and add the following appSettings tags.

You can get the endpoint and authkey from the Keys section in your Cosmos DB blade in the portal.

<appSettings>
    <add key="endpoint" value="enter" />
    <add key="authkey" value="enter" />
    <add key="database" value="enter" />
    <add key="collection" value="enter" />
  </appSettings>
1
2
3
4
5
6

private static readonly string DatabaseId = ConfigurationManager.AppSettings["database"];
private static readonly string CollectionId = ConfigurationManager.AppSettings["collection"];
private static readonly string EndPointId = ConfigurationManager.AppSettings["endpoint"];
private static readonly string AuthKeyId = ConfigurationManager.AppSettings["authkey"];

private static DocumentClient client;

client = new DocumentClient(new Uri(EndPointId), AuthKeyId);
CreateDatabaseIfNotExistsAsync().Wait();
CreateCollectionIfNotExistsAsync().Wait();

FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };

IQueryable<dynamic> familyQueryInSql = client.CreateDocumentQuery<dynamic>(
UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
    "SELECT VALUE COUNT(1) FROM c",
    queryOptions);

Console.WriteLine("Running direct SQL query...");
foreach (dynamic family in familyQueryInSql)
{
    Console.WriteLine("\tRead {0}", family);
}

Console.Read();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Success!!