CosmosDBInput binding of Azure Function is not using path Variable defined in HttpTrigger

Question

I have created a function with Kotlin using azure-functions-kotlin-archetype. I have created a Http Trigger and a cosmos input binding to read data from the cosmos. I have mentioned sql query also to fetch the data. I want to pass path variable to the http trigger which should be used as parameter in the query. As per Microsoft documentation I have defined the path parameter accordingly. https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2-input?tabs=java#http-trigger-get-multiple-docs-from-route-data-using-sqlquery-java

But I am getting blank response, means no data is fetched. If I hard code the parameter in the Sql query, I able to fetch the data. Can any one please tell me the issue here. The Function Code is as below

@FunctionName("GetData")
    fun run(
            @HttpTrigger(
                    name = "req",
                    methods = [HttpMethod.GET],
                    authLevel = AuthorizationLevel.ANONYMOUS,
            route = "/api/getItems/{id}/{qnt}"
            )
            request: HttpRequestMessage<Optional<String>>,
            @CosmosDBInput(
                name = "cosmosdb",
                databaseName = "item-db",
                collectionName = "item",
                sqlQuery = "SELECT * FROM ITEM n where n.id= {id} "
                             +"and n.qnt = {qnt}",
              connectionStringSetting = "Cosmos_DB_Connection_String"
            )
            rs: Array<String>,
            context: ExecutionContext): HttpResponseMessage {



            return request
                    .createResponseBuilder(HttpStatus.OK)
                    .body(rs)
                    .build()
        }

}

Hi, may I know if you still use route when you request function with hard code sqlQuery ? — Hury Shen
@HuryShen Yes, I use route always. No change except instead of taking value from route, I hard coded it. — shoubhgh
Ok, I will test it in my side. Could you please have a try to remove the / before api/getItems/{id}/{qnt} in your route ? — Hury Shen
@HuryShen Thats a mistake in the typing, the / infront of api is not present in the actual code. — shoubhgh
Could you please provide a sample of the value of id and qnt, also please provide a sample of sqlQuery when you request it hard code. — Hury Shen

Hury Shen Hury Shen · Accepted Answer · 2020-08-11T07:50:06

According to the sample query you provided: SELECT * FROM ITEM n where n.id= {'ID1'} " +"and n.qnt = {1}, it seems the id is string and qnt is a number. When we get the parameters from route, it will treat the parameters as string. So if you use the parameters from route in sqlQuery directly, it will select the data like this sql: SELECT * FROM ITEM n where n.id = 'ID1' and n.qnt = '1'. So no data is fetched.

You need to get the parameter qnt as a number but not as string from route, so please use route = "api/getItems/{id}/{qnt:int}".

===========================Update=========================

Add this update for other communities reference:

Use the method StringToNumber() in sql query like sqlQuery = "SELECT * FROM ITEM n where n.id= {id} " + "and n.qnt = StringToNumber({qnt})".

CosmosDBInput binding of Azure Function is not using path Variable defined in HttpTrigger

1 Answers