In this article, we will explore how to work with JSON data in Azure Cosmos DB using the SQL API. Azure Cosmos DB is a globally distributed, highly available NoSQL database system with low latency. It supports multiple APIs, including the Core (SQL) API, which allows us to query JSON documents using a SQL-like query language.
Creating an Azure Cosmos DB Account
To get started, you will need an Azure subscription or a Cosmos DB trial account. Once you have access, you can create a Cosmos DB account in the Azure portal. Select the Core (SQL) API and specify the necessary details such as the resource group, account name, and location. You can choose between provisioned throughput and serverless options depending on your requirements.
Adding a Database and Container
After creating the Cosmos DB account, you can add a database and container to store your JSON documents. In the Data Explorer blade, click on “New Container” and provide the database ID, container ID, and partition key. You can also set the throughput at the database or container level. Once created, you can add JSON documents to the container using the “New Item” option.
Querying JSON Data
Now comes the main part – querying JSON data using the SQL API. You can open a query window and use various SQL clauses to retrieve specific data from your JSON documents. Here are some examples:
- Use the SELECT clause to retrieve all records from a container:
SELECT * FROM ContainerName - Use dot notation or quoted notation in the SELECT clause to retrieve selected properties:
SELECT p.firstName, p.lastName, p.Age FROM ContainerName p - Apply filters using the WHERE clause:
SELECT p.firstName, p.lastName, p.Gender FROM ContainerName p WHERE p.Age BETWEEN 30 AND 40 - Use the TOP and ORDER BY clauses to limit the number of results and order them:
SELECT TOP 5 p.firstName, p.lastName FROM ContainerName p ORDER BY p.lastName ASC - Perform joins on nested properties or arrays:
SELECT p.firstName, p.lastName, ph.number FROM ContainerName p JOIN ph IN p.phoneNumbers WHERE ph.type = 'work' - Perform aggregations using functions like AVG, COUNT, MIN, MAX:
SELECT AVG(p.Age) AS AverageAge, COUNT(p.employeeId) AS TotalEmployees FROM ContainerName p - Group data using the GROUP BY clause:
SELECT p.Gender, COUNT(1) AS TotalEmployees FROM ContainerName p GROUP BY p.Gender
These are just a few examples of what you can do with the SQL API in Azure Cosmos DB. The SQL-like query language makes it easy to work with JSON data and retrieve the information you need.
Conclusion
Azure Cosmos DB with the SQL API provides a powerful and flexible way to work with JSON data. In this article, we covered the basics of creating a Cosmos DB account, adding a database and container, and querying JSON documents using SQL-like queries. With Azure Cosmos DB, you can efficiently store, retrieve, and process JSON data for various applications and use cases.