Customize SQL query input binding in Azure Function (TypeScript)

Issue

I'm using input bindings in my Azure Function (TypeScript) to connect to Cosmos DB. It looks something like this:

{
    "type": "cosmosDB",
    "direction": "in",
    "name": "docsIn",
    "databaseName": "books",
    "collectionName": "books",
    "connectionStringSetting": "CosmosDbConnectionString",
    "sqlQuery": "SELECT * FROM b"
  }

My problem is that I need to customize sqlQuery extensively before it's actually run. The simple substitution system they have available isn't enough. I.e., this won't work: SELECT * FROM b WHERE b.id = {id}.

Is there a way in TypeScript to grab a connection to the database without actually running any queries, so I can run the query in my function once it's ready?

My assumption is that the only way to do this is to make a chain of Durable Functions: 1 function to construct the query, and a 2nd function to run it as an input binding. Is that my only option?

---

EDIT: Thomas asked for an example of the input payload and the resulting query that needs to run.

Input:

...com?q="teh Lord of the Ringgs"

Query:

SELECT *
FROM b
WHERE ARRAY_CONTAINS(b.tokens, "lord")
AND ARRAY_CONTAINS(b.tokens, "ring")

So the processing steps were something like this:

1. Convert to lowercase
2. Tokenize (split on spaces)
3. Correct common misspellings
4. Throw out stop words (the, of)
5. Stem words (remove suffixes: rings -> ring)

That seems to be more than any SQL query language supports, which is why I need code-level access to the payload in order to form the query.

Solution

I discovered that the normal JavaScript package for Costmos DB access works within Azure Functions as well. So instead of using an input binding, I just needed to import the @azure/cosmos package and use it directly.

This was my reference: https://docs.microsoft.com/en-us/azure/developer/javascript/how-to/with-database/use-sql-api-as-cosmos-db.

Steps:

1. npm install @azure/cosmos
2. npm install --save-dev @types/node (I don't know why this was needed, but I got errors otherwise. Maybe the Azure Functions runtime for TypeScript is a modified version of node?)
3. Use the CosmosClient class as described in the link above

Note: Because you're creating the connection yourself, make sure to follow the guidelines here to minimize processing time and connection exhaustion: https://docs.microsoft.com/en-us/azure/azure-functions/manage-connections#azure-cosmos-db-clients

Answered By - icanfathom