In the blog post about SQL operators and functions, we saw an introduction to Scalar Expression Queries. Let us see what that actually means.
When selecting data from a collection, we use Select * FROM collection, but in case you do not want to mention collection name since it doesn’t help you join across documents, you can emit the FROM clause and run the query without it.
This leads to querying expressions and not documents. Now querying the expressions result in a scalar value, and thus, the queries resulting in a scalar value are known as Scalar Expression Queries.
Let’s see how to run them now.
Lets say we have some query data inside the query explorer, which look like:
SELECT name
SELECT “hello””
SELECT VALUE “Welcome”
As you can see, none of these queries contain the FROM clause, and therefore, these are known as Scalar Expression queries.
Now when you try to run any of these queries, say
SELECT “hello”
You get the result as:
[
{
$1: “Hello”
}
]
Now, since we didn’t provide any name for the query, Cosmos DB generated an identifier by itself. In this case you can give the name as using the query:
SELECT “Hello” AS word
This will give results as:
[
{
“word”: “Hello”
}
]
Now since this is an object as a result of running this expression, you can use the query below to get another better result.
SELECT VALUE “Hello”
This will give the result as:
[
“Hello”
]
To select two objects, we use:
SELECT “Hello”, “Hi”
This gives the results as:
[
{
“$1”: “Hello”,
“$2”: “Hi”
}
]
These can again be replaced with our own words then.
SELECT
“Hello” as word1,
“Hi” as word2
We can also shape the JSON object and nest them inside the array of strings.
For example,
SELECT
{
“word1”: “Hello”,
“word2”: “Hi”
}
AS words
The result in this will be the nested one:
[
{
“words”:
{
“word1”: “Hello”,
“word2”: “Hi”
}
}
]
Similarly, we can use many more queries without actually having to use the FROM clause and running the queries with expressions only.
SUMMARY
In this blog post, we learnt what are Scalar Expression Queries and how we can use them to query expressions without using the FROM clause actually. We also saw how we can nest the data and shape our JSON objects flexibly with Scalar Expression Queries.
Leave a Reply