Azure Cosmos DB for NoSQL: Exploring Parameterized Queries with Practical Examples

Azure Cosmos DB, a globally distributed, multi-model database service, is a powerful tool for developers aiming to build high-performance applications. One of its key features is the support for parameterized queries in SQL. In this blog post, we will explore how to use parameterized queries in Azure Cosmos DB with practical examples.

Understanding Parameterized Queries

Parameterized queries are a type of SQL query where placeholders are used for values and then, these placeholders are replaced with actual values at runtime. This feature is particularly useful to prevent SQL injection attacks, as it provides robust handling and escaping of user input. In Azure Cosmos DB, parameterized queries are expressed using the familiar @ notation.

Creating Parameterized Queries

Let’s consider a scenario where you have a collection of families, each with a ‘lastName’ and an ‘address’ property, which is an object containing a ‘state’ property. If you want to find all families with a specific last name and living in a specific state, you could use a parameterized query as follows:

SELECT *
FROM Families f
WHERE f.lastName = @lastName AND f.address.state = @addressState

SELECT *

FROM Families f

WHERE f.lastName = @lastName AND f.address.state = @addressState

In this query, @lastName and @addressState are placeholders that will be replaced with actual values at runtime.

Executing Parameterized Queries

To execute a parameterized query, you need to send a request to Azure Cosmos DB with the query and the parameters. The parameters are specified as a JSON array, where each parameter is an object with a ‘name’ and a ‘value’ property. The ‘name’ property corresponds to the placeholder in the query, and the ‘value’ property corresponds to the actual value that will replace the placeholder.

Here’s how you could execute the previous query with ‘Wakefield’ as the last name and ‘NY’ as the state:

{
    "query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND f.address.state = @addressState",
    "parameters": [
        {"name": "@lastName", "value": "Wakefield"},
        {"name": "@addressState", "value": "NY"},
    ]
}

{

"query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND f.address.state = @addressState",

"parameters": [

{"name": "@lastName", "value": "Wakefield"},

{"name": "@addressState", "value": "NY"},

]

}

Using Parameterized Queries with Different Types of Values

Parameter values in Azure Cosmos DB can be any valid JSON: strings, numbers, Booleans, null, even arrays or nested JSON. This flexibility allows you to use parameterized queries with a wide range of data types.

For instance, if you want to limit the number of results returned by a query, you could use a parameterized query with a number value as follows:

{
    "query": "SELECT TOP @n * FROM Families",
    "parameters": [
        {"name": "@n", "value": 10},
    ]
}

{

"query": "SELECT TOP @n * FROM Families",

"parameters": [

{"name": "@n", "value": 10},

]

}

In this query, @n is a placeholder that will be replaced with the number 10 at runtime, limiting the results to the top 10 families.

Conclusion

In conclusion, parameterized queries in Azure Cosmos DB provide a powerful and flexible way to create and execute SQL queries. They offer robust handling and escaping of user input, preventing accidental exposure of data through SQL injection. Whether you’re querying for specific data or limiting the number of results, parameterized queries can help you create more secure and efficient queries in Azure Cosmos DB.