Cheat Sheet for Cosmos DB SQL queries

Cosmos DB SQL queries.md

Notes

• Search text is case sensitive
• Try to force attribute and text in your search to be in the same case
• Wildcard search use STARTSWITH and ENDSWITH
• two different types of nulls:
• missing elements
• actual null values.
• Use IS_NULL()and IS_DEFINED() functions, to detect missing attributes and ternary logic
• Cosmos DB SQL currently does not have an equivalent of SQL LEFT() function. Instead, use SUBSTRING()
• There is no Year() function in Cosmos DB SQL, use SUBSTRING() to the date string and isolate
• Use Truncate to Round numeric values

Word Search

SELECT  s.Cars, s.Class 
FROM s
WHERE   CONTAINS(s.Cars, "ECO")

Results:

[
{
    "Cars": "LE ECO",
    "Class": "ELECTRIC"
},
{
    "Cars": "ECO PREMIUM",
    "Class": "SEDAN"
}

]

Word Search (Substring)

SELECT  s.cars
FROM    s
WHERE   STARTSWITH(s.cars, "Golf")
    OR
    ENDSWITH(s.cars, "Diesel")

Results:

[
{
    "CustomerName": "Golf GTI"
},
{
    "CustomerName": "Golf  Passat"
},
{
    "CustomerName": "All cars Diesel"
}
]

Case-insensitive Search

SELECT  s.CustomerName
FROM    s
WHERE   CONTAINS(UPPER(s.CustomerName),  “eco")

Null Handling in Cosmos DB SQL

SELECT  s.InvoiceNumber
   ,s.Cost + s.RepairsCost 
   + s.PartsCost + s.TransportInCost 
   + (IS_NULL(s.LineItemDiscount) ? 0 : s.LineItemDiscount) 
       AS Costs
FROM   s

    •	(IS_NULL(s.LineItemDiscount) ? 0 : s.LineItemDiscount) -> If s.LineItemDiscount is NULL, then 0, otherwise s.LineItemDiscount
    •	Problem, if attribute for s.LineItemDiscount is set to NULL, the Cost value will return nothing: **  GBPGB003, EURDE004, EURFR005

Results:

[
{
    "InvoiceNumber": "GBPGB011",
    "Costs": 7425
},
{
    "InvoiceNumber": "GBPGB011",
    "Costs": 66400
},
{
    "InvoiceNumber": "GBPGB001" ,
    "Costs": 56425
},
{
    "InvoiceNumber": "GBPGB002" ,
    "Costs": 185650
},
{
    "InvoiceNumber": "GBPGB003"
  
},
{
    "InvoiceNumber": "EURDE004"
},
{
    "InvoiceNumber": "EURFR005"
}
]

Use IS_DEFINED() to detect if the property has been assigned a value

SELECT  s.InvoiceNumber
   ,s.Cost + s.RepairsCost 
   + s.PartsCost + s.TransportInCost 
   + (IS_DEFINED(s.LineItemDiscount) ? s.LineItemDiscount : 0) 
       AS Costs
FROM   s

Results:

[
{
    "InvoiceNumber": "GBPGB011",
    "Costs": 7475
},
{
    "InvoiceNumber": "GBPGB011",
    "Costs": 66900
},
{
    "InvoiceNumber": "GBPGB001 ,
    "Costs": 59125
},
{
    "InvoiceNumber": "GBPGB002"
},
{
    "InvoiceNumber": "GBPGB003",
    "Costs": 16410
},
{
    "InvoiceNumber": "EURDE004",
    "Costs": 10600
},
{
    "InvoiceNumber": "EURFR005",
    "Costs": 17970
}

]

SUBSTRING() for LEFT()

SELECT  s.CustomerName ,s.SalePrice
FROM    simplecars AS s
WHERE   SUBSTRING(s.CustomerName, 0, 1) = "M"

Results:

[
{
    "CustomerName": "Magic Motors",
    "SalePrice": 65000
}

]

SUBSTRING() for RIGHT()

SELECT  s.CustomerName ,s.SalePrice
FROM    simplecars AS s
WHERE   SUBSTRING(REVERSE(s.CustomerName), 0, 1) = "r"

Results:

[
{
    "CustomerName": "WunderKar",
    "SalePrice": 11500
}
]

For more than one character at the right of the string, use reverse string. Or, use a double reverse string to make it easier to read.

SELECT  s.CustomerName ,s.SalePrice
FROM    saleselements AS s
WHERE   REVERSE(SUBSTRING(REVERSE(s.CustomerName), 0, 3)) = "Kar"

Impersonate T-SQL YEAR(), MONTH(), and DAY()

SELECT s.SaleDate
FROM   s
WHERE  SUBSTRING(s.SaleDate, 0, 4) = "2015"
The Month
    SELECT s.SaleDate
FROM   s
WHERE  SUBSTRING(s.SaleDate, 5, 2) = "02"

The Date

SELECT s.SaleDate
FROM   s
WHERE  SUBSTRING(s.SaleDate, 8, 2) = "25"

Operators

The operators >=, <=, <> or != IN for a set of years, etc. Rounding up to nearest 1000 TRUNC() function to round up to the nearest thousand:

SELECT  TRUNC(ROUND(s.TotalSalePrice / 1000)) * 1000 AS RoundedUp
FROM    s

Results:

[
{
    "RoundedUp": 89000
},
{
    "RoundedUp": 89000
},
{
    "RoundedUp": 65000
},
{
    "RoundedUp": 220000
},
{
    "RoundedUp": 20000
},
{
    "RoundedUp": 12000
},
{
    "RoundedUp": 20000
}
]