Microsoft Azure ComosDB SQL Query Cheat Sheet and Examples

I have recently had the opportunity to use Cosmos DB in Microsoft Azure for a project , so have started to pull together a cheat sheet for all the different things I find out on my travels - hope you find it useful!

 

SQL Query Where Cosmos DB Contains Or Like

SELECT * FROM c WHERE CONTAINS(c.id, "RK0305500")

 

SQL Query Where Cosmos DB Ends With a String

SELECT * FROM c WHERE ENDSWITH(c.id, "_somestring")

 

SQL Query Where Cosmos DB Field Name Contains a Space

SELECT * FROM c WHERE CONTAINS(c["Product Level"], "Level 1")

 

Total Document Count In Azure DB Collection

SELECT VALUE COUNT(1) FROM c

 

Select Multiple Nested Properties From Documents in CosmosDB

In the example below, 'parent' is the parent item, and 'ChildProducts' is an array of properties off each parent. These child arrays hold the 'Some Property Name' property.

SELECT child["Some Property Name"] FROM parent JOIN child IN parent.ChildProducts WHERE parent.id = "Some Id"

Alternatively:-

SELECT * FROM c WHERE ARRAY_CONTAINS(c.Intents, 'Question_StockGeneral',true)

 

Select Entire Parent Record Using Property on Child in CosmosDB

SELECT VALUE parent FROM parent JOIN child IN parent.ChildProducts WHERE child.id= "some id"

 

Select Distinct Value From Child Array Property in CosmosDB

SELECT distinct VALUE ChildArrayName from c join ChildArrayName in c.ChildArrayName where ...

 

Field Name Alias in CosmosDB

SELECT c["Some Field"] as SomeFieldAliasName from c

 

Null Value Select Coallesce in CosmosDB

SELECT c["Some Field"] ?? "" from c