Azure Cosmos DB | SQL with Manoj

Cosmos DB – Retrieve all attributes/fields from the JSON document of all Collections (using .net)

April 6, 2021 1 comment

In our [previous post] we saw how to get COUNT of items/records from all Collections in all Databases of a CosmosDB instance by programmatically/dynamically iterating over all these objects.

Here in this post we will extend it and see how to retrieve all attributes/fields from the JSON document/items present in these Collections. (This is some work I’m doing for maintaining a data catalogue and data classification for privacy and compliance purpose.)

On Azure portal go to your Azure Cosmos DB instance, from Overview tab go to Keys tab and copy the “URI” & “PRIMARY READ-ONLY KEY” key values in code below.

We have discussed all the methods used below in our [previous post], please check here. As the Cosmos DB SQL container is a schema-agnostic and the items in a container can have arbitrary schemas, thus we will read the first JSON document/item from the Collection by using FirstOrDefault() method and Deserialize this JSON document Object to a Dictionary, and finally read all the attributes from this Dictionary Keys:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace CosmosDBgetJSONAttributes
{
    class Program
    {
        static void Main(string[] args)
        {
            string EndPointUri = "https://YourCosmosDBname.documents.azure.com:443/";
            string PK = "YourPrimaryKey==";

            DocumentClient cosmosClient = new DocumentClient(new Uri(EndPointUri), PK);

            string sqlQuery = "SELECT * FROM d";
            var dbs = cosmosClient.CreateDatabaseQuery(sqlQuery).ToList();

            foreach (var db in dbs)
            {
                Console.WriteLine("Database: " + db.id);

                List<DocumentCollection> collections = cosmosClient.CreateDocumentCollectionQuery((String)db._self).ToList();

                foreach (var collection in collections)
                {
                    Console.WriteLine("  - Collection: " + collection.Id);

                    var docu = cosmosClient.CreateDocumentQuery(collection.SelfLink, $"SELECT TOP 1 * FROM c",
                                    new FeedOptions() { EnableCrossPartitionQuery = true, MaxItemCount = 1, })
                                    .AsEnumerable().FirstOrDefault();
                    if (docu != null)
                    {
                        var docuStr = Convert.ToString(docu);
                        var data = JsonConvert.DeserializeObject<Dictionary<string, string>>(docuStr);
                        foreach (var item in data.Keys)
                        {
                            Console.WriteLine("    - Attribute: " + item);
                        }
                    }
                    else
                    {
                        Console.WriteLine("    - Attribute: no data");
                    }
                }
                Console.WriteLine(Environment.NewLine);
            }
            Console.WriteLine("Finished reading all Collections attributes");
            Console.Read();
        }
    }
}

Categories: Cosmos DB Tags: Azure Cosmos DB, Cosmos DB, Deserialize JSON, Document DB

Cosmos DB – Get record COUNT from all Collections in all Databases (using .net)

April 1, 2021 2 comments

Here in this post we will use C# .net code (for beginners like me) to see how to:
1. Connect to a Cosmos DB instance
2. Get list of all Databases in a Cosmos DB
3. Iterate through all the Databases and get the list of all Collections (or Tables)
4. Get COUNT of all documents/items (or records) in these Collections

On Azure portal go to your Azure Cosmos DB instance, from Overview tab go to Keys tab and copy the “URI” & “PRIMARY READ-ONLY KEY” key values:

1. First we will use the Uri & Primary Key to connect to CosmosDB service by using DocumentClient() method.

2. Then we will form a simple query to retrieve all Databases, will use with CreateDatabaseQuery() method to retrieve list of all databases.

3. Now to get list of all Collections present in each Database we will iterate through each Database, use the CreateDocumentCollectionQuery() method using the Database instance with Self link property.

4. Now to get the Document link we will also iterate through each Collection by using CreateDocumentQuery() method.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;

namespace CosmosTables
{
    class ReadCosmosDB
    {
        static void Main(string[] args)
        {
            string EndPointUri = "https://YourCosmosDBname.documents.azure.com:443/";
            string PK = "YourPrimaryKey==";

            DocumentClient cosmosClient = new DocumentClient(new Uri(EndPointUri), PK);

            string sqlQuery = "SELECT * FROM d";
            var dbs = cosmosClient.CreateDatabaseQuery(sqlQuery).ToList();

            foreach (var db in dbs)
            {
                Console.WriteLine("Database: " + db.id);
                Console.WriteLine("- Collections: "); 

                List<DocumentCollection> collections = cosmosClient.CreateDocumentCollectionQuery((String)db._self).ToList();

                foreach (var collection in collections)
                {

                    var count = cosmosClient.CreateDocumentQuery<int>(collection.SelfLink, $"SELECT value count(1) FROM c",
                                    new FeedOptions(){EnableCrossPartitionQuery = true, MaxItemCount = 1,}).AsEnumerable().First();

                    Console.WriteLine("  - " + collection.Id + $", Count: " + count);
                }
                Console.WriteLine(Environment.NewLine);
            }
            Console.WriteLine("Finished reading all DB Collections");
        }
    }
}

Categories: Cosmos DB Tags: Azure Cosmos DB, Cosmos DB, Document DB