In Cosmos DB, there is a wide range of SQL operators ranging from arithmetic operators, bit-wise operators to string concatenation using || (concatenate). All these operators can be used as the SQL queries. Let us look at the different operators supported by Cosmos DB SQL.
Bit-wise Operators — (| & ^ < >> <<<)
Arithmetic operators — (+ — * / %)
Logical operators — (AND, OR)
Comparison — (= <>≥≤)
Ternary & Coalesce — (? : ??)
String — ||(concatenate)
In Cosmos DB SQL, there are a no. of supported built-in functions that are very useful to work across various schemas.
MATH- CEILING, LOG, FLOOR
Type check- IS_ARRAY, IS_BOOL, IS_NULL
String- CONTAINS, SUBSTRING, UPPER
Array- ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH
Aggregate- (COUNT, SUM, MIN, MAX)
Spatial- (ST_DISTANCE, ST_ISVALID, ST_WITHIN)
These type checking functions come handy when we are working with various schemas and are very useful. They can check if a certain property exists and what is the type of that property.
We have different functions for working with strings, parsing them, using arrays for testing what they contain, length etc. Also, when you have data in GeoJSON format, it supports the format by using queries for Distance, intersection.
There are variety of ways by which we can project results of our SQL queries by using different combinations inside the database.
Till now, in our previous articles, we have always used Data Explorer to query our results. But if we go down in the list on left, we can see three more explorers.
- Document explorer
- Query Explorer
- Script Explorer
To work with queries, it is better to use Query Explorer to get better results. Mainly because it lets you load a script file also helping to run multiple queries.
Now the query explorer gives you the option to choose Collection, because the scope of running the queries is within collections.
Lets us try to understand the query that we here now.
SELECT * FROM c
As per our understanding of SQL relational databases, SELECT * is used to select all the data from the table, and this is not the same with the SQL in Cosmos DB.
In Cosmos DB, this query SELECT * means that select the data exactly as it is present in the database. Same as it is stated in the database.
Now, let us try to understand the FROM clause.
We have seen that queries always run within the scope of collections and we cant join across the documents inside a collection.
This means it is also okay if we do not use FROM clause at all. But then, if we do not use the FROM clause, we don’t actually query documents but expressions.
These expressions that are queried will return scalar values, which is why such queries are known as Scalar expression queries.
In this blog post, we saw the use of SQL operators and functions inside the Cosmos DB SQL. We also saw how to run queries, we can use Query Explorer of Cosmos DB in place of Data Explorer which we have been using for a while in our previous blog posts.
In the end, we saw how the query to fetch the data has a different meaning from the one in relational database queries of SQL.