In my previous post, I wrote about getting started with Azure Cosmos DB. In this post, let us see basic SQL's to query the JSON documents.
Microsoft Azure Cosmos DB supports querying documents using SQL as a JSON query language on SQL API accounts. Azure Cosmos DB is truly schema-free.
To show some examples, I am going to create two sample JSON documents:
Difference in the above JSON documents are Doc1 does not have state property inside address and inside cars -> Doc1 has Variation property and Doc2 has models property.

--In the From clause, we can mention c or collection name - JsonDocs in this examples







Reference:
Microsoft Azure Cosmos DB supports querying documents using SQL as a JSON query language on SQL API accounts. Azure Cosmos DB is truly schema-free.
To show some examples, I am going to create two sample JSON documents:
--Doc1 { "id": "Doc1", "name": "Sathya", "age": 29, "cars": [ { "name": "Ford", "variation": [ "Fiesta", "Focus", "Mustang" ] } ], "address": [ { "county": "UK", "city": "London" } ] }
--Doc2 { "id": "Doc2", "name": "Shalu", "age": 27, "cars": [ { "name": "BMW", "models": [ "320", "X3", "X5" ] } ], "address": { "state": "NY", "county": "Manhattan", "city": "NY" } }
Difference in the above JSON documents are Doc1 does not have state property inside address and inside cars -> Doc1 has Variation property and Doc2 has models property.
--To query the documents and filter based on document unique Ids SELECT * FROM c WHERE c.id = "Doc1"
--In the From clause, we can mention c or collection name - JsonDocs in this examples
-- To get segment of Json document in different shape SELECT {"DocumentId":c.id,"CustomerName":c.name,"Age":c.age} AS CarOwnerDetail FROM c
--To Sort output of documents SELECT * FROM c ORDER BY c.age DESC
--To query subdocuments SELECT * FROM JsonDocs.address
--Adding another object inside cars in doc2 by manually editing the document { "name": "Audi", "models": [ "A6", "A8", "Q2" ] }
--To query based on Index value SELECT * FROM JsonDocs.cars[1]
--To query based on IN keyword SELECT * FROM JsonDocs S WHERE S.address.city IN ('TX', 'NY')
-- Selecting nested properties SELECT c.address.state, c.address.city, c.address.county FROM c WHERE c.id = "Doc2"
--Return top N documents SELECT TOP 1 * FROM c
--Use of Aggregate functions, to get total no. of cars SELECT VALUE COUNT(c.name) FROM c IN JsonDocs.cars --VALUE is used to resturn JSON values
-- JOIN Example SELECT c.name AS CarNames FROM JsonDocs J JOIN c IN J.cars
--Modifying models inside cars in doc2 as key-value pairs by manually editing the document "cars": [ { "name": "BMW", "models": [ {"no" : "320"}, {"no":"X3"}, {"no":"X5"} ] }, { "name": "Audi", "models": [ {"no": "A6"}, {"no": "A8"}, {"no": "Q2"} ]
--Another JOIN Example SELECT c.name AS CarName,m.no AS ModelNumber FROM JsonDocs J JOIN c IN J.cars JOIN m IN c.models
--There are various built-in functions SELECT c.name AS CarName,m.no AS ModelNumber FROM JsonDocs J JOIN c IN J.cars JOIN m IN c.models WHERE ARRAY_CONTAINS(c.models,{"no": "A6"})
SELECT c.id,c.name FROM c WHERE STARTSWITH(c.name, "S")
No comments:
Post a Comment