Post
Ask Question

Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API

Introduction

 
In this article we will try to understand Azure Cosmos db service, and also create our first Cosmos db account to store and retrieve Json data using SQL API.  We will also try to understand the advantges of using cosmos db. 
 
Prerequisites 
  • Microsoft free account and Azure Credits.
  • Basic knowledge of using Azure Services
In this article, we will cover the following topics:
  • Advantages of using Azure cosmos db
  • Step by step creating and configuring cosmos db SQL API data store 
  • Store json data in cosmos db and query using sql query.

Advantages of using Azure cosmos db

  • Cosmos db is fully managed;  no SQL data store 
  • Don't have to worry about underlying infrastructure 
  • Automatically scales storage
  • Automatically scales compute
  • Support many APIs inculding SQL,Mongo db,Cassendra,Table and Gremin. 

Document database terminolgies

 
In this article we will be storing json data and using SQL API for the same.
 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
Container in SQL API will represent table in our RDMS 
 
Document IN SQL API will represent a table row from RDBS
 

Step by step create and configure cosmos db SQL API data store

 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
 
Select your Azure subscription and  select your resource group.
 
Add unique account name and and select API Type.
 
In this article we will select SQL API (we will be storing jason type data).
 
One Azure cosmos db account is only used to store certain types of data.
 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
 
Select apply free tier discount.This will give us 400 Ru/s(Request units) and 5 GB of storage.
 
We are charged for Azure cosmos db based on Ru/s and underlying storage.
 
Keep the same remaining settings.
 
Click on review and create.
 
It will take 5-10 minutes to spin up new resource.
 

Provisional Throughput

 
In Azure cosmos db you are charged for the throughput you provision and this stoarge is consumed on an hourly basis.
 
Depending on the amount of throughput you provison the right amount of CPU memory and IOPS will be assigned.
 
You can assign throughput at container level or database level.
 
The cost to read 1 kb item is 1RU 
 
The other database opertions take their own amount of throughput depending on database operations.
 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
 
Click on create container and specify name.
 
This will be our database name. 
 
Throughput starts from 400.
 
We could also see estimated spend.
 
We are mentioning 400 RU for our database.
 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
 
Specify container id.
 
This will be the name of our database table.
 
Specify parition key. 
 
Data will be spent across multiple partions based on partion key.
 
Click on ok our table has now been created.
 
Azure Cosmos DB - Store And Retrieve JSON Data Types Using SQL API
 
Store json data in cosmos db and query using sql query 
 
Click on new item and copy paste below json format
  1. {  
  2.     "id": "2",  
  3.     "name": "prasad",  
  4.     "city": "mumbai"  
  5. } {  
  6.     "id": "1",  
  7.     "name": "ganesh",  
  8.     "city": "mumbai"  
  9. }  
Click on save we could see 2 items have been added to database.
 
Navigate through new SQL query and execute the below query.
  1. select * from employee   
  1. [{  
  2.     "id": "replace_with_new_document_id",  
  3.     "name": "ganesh",  
  4.     "city": "mumbai",  
  5.     "_rid": "mUQAAIVYAR4BAAAAAAAAAA==",  
  6.     "_self": "dbs/mUQAAA==/colls/mUQAAIVYAR4=/docs/mUQAAIVYAR4BAAAAAAAAAA==/",  
  7.     "_etag": "\"17009a5a-0000-0700-0000-5f92fa2e0000\"",  
  8.     "_attachments": "attachments/",  
  9.     "_ts": 1603467822  
  10. }, {  
  11.     "id": "2",  
  12.     "name": "prasad",  
  13.     "city": "mumbai",  
  14.     "_rid": "mUQAAIVYAR4CAAAAAAAAAA==",  
  15.     "_self": "dbs/mUQAAA==/colls/mUQAAIVYAR4=/docs/mUQAAIVYAR4CAAAAAAAAAA==/",  
  16.     "_etag": "\"17008069-0000-0700-0000-5f92faed0000\"",  
  17.     "_attachments": "attachments/",  
  18.     "_ts": 1603468013  
  19. }]  
Thus we have succesfully saved and retrieved json data from azure cosmos db using SQL ApI.
 
Adding more items.
 
By copying the above json format we can keep on adding more items to our Employee table.
 
At the right hand side top there is option to open Azure cosmos db operations window in full screen.
 
We can click on right hand top and click on open.
 
This will open full screen in new window.
 

Summary

 
In this article, we have learned how to create, configure, and deploy our first Azure cosmos db account.
 
We have seen some of the advantages of using Azure cosmos db service.
 
We have also seen how to add new items in our table and query using SQL query.
 
Thus we have store and retrieved json data using SQL API.
 
Thanks a lot for reading. I hope you liked this article. Please share your valuable suggestions and feedback. Write in the comment box in case you have any questions. Have a good day!


Similar Articles