Can the SQL query for an Azure Function's Cosmos DB input binding be called dynamically, with variables, from within the function?

Question

I have a collection of temperature values with timestamps in Azure Cosmos DB from which I want to query the average temperature value from a particular upload date.

I currently have a working solution using the @azure/cosmos client library, which allows me to dynamically build and execute queries against my temperature collection from an Azure Function.

I was wondering if it was possible to execute dynamic SQL queries against a Cosmos DB input binding, rather than using the cosmos client library, simply for performance benefits?

Also, if this is possible, would using the input binding actually afford a performance benefit?

Answer 1

Well it depends on what you meant with dynamic. If you meant the query changes based on input from your request(HttpTrigger) then the answer is yes it can. Example:

SELECT * FROM c WHERE c.temperature > {temp} AND (c.expired?? false) = false

Here {temp} is replaced with data in your request body eg.

{
  "deviceId": "22dsa2d55f",
  "temp": 55.3665,
  "time": "2020-08-14T15:06:31.277Z"
}

As far as I'm aware there is no performance benefit. It is only advertised as reducing code complexity by eliminating the need for the io code.

Answer 2

You can't use dynamic filters on the input bindings. The input binding is used in the context of you want to trigger your function by the Cosmos DB (e.g. when a given document is updated or deleted or created). It is not meant to do filtering. What you can do is apply your filtering logic on your function, to decide which documents you want to process and the ones to skip. In terms of filtering directly against Cosmos DB, you can still use the SDK or the API.