Post
Ask Question

Quick Start With Azure Cosmos DB - Day Five

In this article, we will see how we can execute the SQL queries on JSON documents and get filtered results.

Please go through my previous articles of this series to get a complete understanding of the topic.

So far, we have created Cosmos DB and documents under collection. Now, we need to check how we can fetch the records using SQL queries.

  • Log into the Azure portal.
  • Select Azure Cosmos DB Service and select the database, i.e., Recharge in our case.
  • Expand the Prepaid collection and click on Documents, which will load the first 100 documents. In order to load more documents, you should click on the "Load More" link which will load another 100 documents.
  • On top of the documents list, we can see this SQL query - "SELECT * FROM c".
  • Click on the "Edit Filter" button.

    Query On Cosmos DB And Download Documents

Once we click the "Edit Filter" button, it will enable the textbox to write the query and convert it into the "Apply Filter" button.

Write your filter query in the textbox and click on the "Apply Filter" button.

Query On Cosmos DB And Download Documents 

The filtered result will be displayed. Just click on id to explore the document.

Query On Cosmos DB And Download Documents 

Now, let's see how we can download the complete dump or our filtered result in the form of JSON document. For this, we need to download a free migration tool from the Microsoft website.

In order to connect this tool to our Cosmos DB, a connection string is required which we can get from the Azure portal.

Under Settings, click on Keys where we get the URI, two keys, and two connection strings. You can use any of the connection strings to connect to Cosmos DB. The question may arise why two sets of keys and connection strings are given. The reason behind is that you can use one connection string while changing the other in production.
Query On Cosmos DB And Download Documents 

Select "DocumentDB" for the "Import from" field.

Copy and paste the connection string and append the "Database=Recharge" separated by a semi colon (;) at last.

Enter Collection as 'Prepaid'.

Enter your SQL query and click on the "Next" button.

Query On Cosmos DB And Download Documents 

Select JSON file as the "Export" option to export them as JSON document.

Select "Local file" radio button and select the path where you want to save the .json document.

Check the "Prettify JSON" checkbox and click the "Next" button.

Query On Cosmos DB And Download Documents 

Select the Error Log file path to capture the error info if any error occurrs and click the "Next" button.

Query On Cosmos DB And Download Documents 

Verify the provided information and click on "Import".

Query On Cosmos DB And Download Documents 

The imported results will be displayed with elapsed time and total transferred documents.

Query On Cosmos DB And Download Documents 

Explore to the given local path of the .json file and open the file.

You can find all the documents in JSON array format.

Transaction.json

  1. [  
  2.   {  
  3.     "id": "3d3e9785-66ff-4560-8a16-99fa80c69401",  
  4.     "Operator": "JIO",  
  5.     "Provider": "CyberPlat",  
  6.     "Region": "Telengana",  
  7.     "Mobile": "9769496026",  
  8.     "Amount": "500",  
  9.     "Status": "Pending",  
  10.     "ExecutionTime": "500 ms",  
  11.     "CreatedDate": "2018-06-23T22:01:44.3895961+05:30"  
  12.   },  
  13.   {  
  14.     "id": "ef200d4d-e3ae-4437-9b4c-c017b21f80fa",  
  15.     "Operator": "Idea",  
  16.     "Provider": "Paytm",  
  17.     "Region": "Gujarat",  
  18.     "Mobile": "9879752926",  
  19.     "Amount": "100",  
  20.     "Status": "Success",  
  21.     "ExecutionTime": "200 ms",  
  22.     "CreatedDate": "2018-06-23T22:01:43.670247+05:30"  
  23.   },  
  24.   {  
  25.     "id": "7da59e87-bb7a-4663-b28c-5fcaf5e78eb2",  
  26.     "Operator": "Airtel",  
  27.     "Provider": "Paytm",  
  28.     "Region": "Maharashtra",  
  29.     "Mobile": "9879752926",  
  30.     "Amount": "500",  
  31.     "Status": "Fail",  
  32.     "ExecutionTime": "500 ms",  
  33.     "CreatedDate": "2018-06-23T22:01:43.4771099+05:30"  
  34.   },  
  35.   {  
  36.     "id": "2098aa87-09f6-4284-9794-06619a3f26fd",  
  37.     "Operator": "Airtel",  
  38.     "Provider": "Paytm",  
  39.     "Region": "Telengana",  
  40.     "Mobile": "9825134178",  
  41.     "Amount": "500",  
  42.     "Status": "Pending",  
  43.     "ExecutionTime": "200 ms",  
  44.     "CreatedDate": "2018-06-23T22:01:43.322699+05:30"  
  45.   },  
  46.   {  
  47.     "id": "153211d2-8227-46b9-8f4f-7e7bedbddd87",  
  48.     "Operator": "Airtel",  
  49.     "Provider": "Paytm",  
  50.     "Region": "Gujarat",  
  51.     "Mobile": "9879752926",  
  52.     "Amount": "200",  
  53.     "Status": "Fail",  
  54.     "ExecutionTime": "500 ms",  
  55.     "CreatedDate": "2018-06-23T22:01:43.1086247+05:30"  
  56.   },  
  57.   {  
  58.     "id": "236ca011-7e7a-423e-9b4e-7ab1ec771f1e",  
  59.     "Operator": "Airtel",  
  60.     "Provider": "CyberPlat",  
  61.     "Region": "Telengana",  
  62.     "Mobile": "9879752926",  
  63.     "Amount": "200",  
  64.     "Status": "Fail",  
  65.     "ExecutionTime": "200 ms",  
  66.     "CreatedDate": "2018-06-23T22:01:42.9233247+05:30"  
  67.   },  
  68.   {  
  69.     "id": "f0a34736-1f44-4ce2-b65d-85993a9dfa8c",  
  70.     "Operator": "Airtel",  
  71.     "Provider": "Euronet",  
  72.     "Region": "Telengana",  
  73.     "Mobile": "9879752926",  
  74.     "Amount": "500",  
  75.     "Status": "Pending",  
  76.     "ExecutionTime": "200 ms",  
  77.     "CreatedDate": "2018-06-23T22:01:42.7441271+05:30"  
  78.   },  
  79.   {  
  80.     "id": "0864f091-213b-4f3e-8e5d-a1be181c347a",  
  81.     "Operator": "JIO",  
  82.     "Provider": "Euronet",  
  83.     "Region": "Telengana",  
  84.     "Mobile": "9769496026",  
  85.     "Amount": "200",  
  86.     "Status": "Fail",  
  87.     "ExecutionTime": "500 ms",  
  88.     "CreatedDate": "2018-06-23T22:01:42.5671958+05:30"  
  89.   },  
  90.   {  
  91.     "id": "21069cf0-4736-44c8-b90b-4f877614cc04",  
  92.     "Operator": "Vodafone",  
  93.     "Provider": "Paytm",  
  94.     "Region": "Telengana",  
  95.     "Mobile": "9825134178",  
  96.     "Amount": "500",  
  97.     "Status": "Pending",  
  98.     "ExecutionTime": "200 ms",  
  99.     "CreatedDate": "2018-06-23T22:01:42.3832615+05:30"  
  100.   },  
  101.   {  
  102.     "id": "e060a604-b1e6-4f8e-bbce-44204922db8c",  
  103.     "Operator": "Vodafone",  
  104.     "Provider": "Euronet",  
  105.     "Region": "Telengana",  
  106.     "Mobile": "9879752926",  
  107.     "Amount": "200",  
  108.     "Status": "Success",  
  109.     "ExecutionTime": "200 ms",  
  110.     "CreatedDate": "2018-06-23T22:01:36.8072284+05:30"  
  111.   },  
  112.   {  
  113.     "id": "4bd8a242-55bf-416e-a50c-e7c1a9439d7c",  
  114.     "Operator": "Airtel",  
  115.     "Provider": "CyberPlat",  
  116.     "Region": "Gujarat",  
  117.     "Mobile": "9879752926",  
  118.     "Amount": "200",  
  119.     "Status": "Success",  
  120.     "ExecutionTime": "500 ms",  
  121.     "CreatedDate": "2018-06-23T21:57:39.665767+05:30"  
  122.   },  
  123.   {  
  124.     "id": "ee5aec6d-d403-4b46-a918-84eeed910b9b",  
  125.     "Operator": "Idea",  
  126.     "Provider": "Paytm",  
  127.     "Region": "Maharashtra",  
  128.     "Mobile": "9879752926",  
  129.     "Amount": "100",  
  130.     "Status": "Pending",  
  131.     "ExecutionTime": "500 ms",  
  132.     "CreatedDate": "2018-06-23T21:55:40.2549335+05:30"  
  133.   },  
  134.   {  
  135.     "id": "91b762c7-01bc-472d-90eb-cbb1bda06949",  
  136.     "Operator": "Vodafone",  
  137.     "Provider": "Euronet",  
  138.     "Region": "Gujarat",  
  139.     "Mobile": "9769496026",  
  140.     "Amount": "500",  
  141.     "Status": "Success",  
  142.     "ExecutionTime": "200 ms",  
  143.     "CreatedDate": "2018-06-23T17:27:10.2003496+05:30"  
  144.   }]  


Similar Articles