Query parameter not resolved in SqlQuery statement

[jeffhollan]

Users want to get a query parameter to resolve in a CosmosDB input binding expression:

"SELECT * FROM c.users WHERE c.CustomerId like {Query.customerId}

But it doesn't work per this: https://github.com/Azure/azure-functions-host/issues/2554#issuecomment-392084583

Would be good to support for consistency with other bindings

[jeffhollan]

/cc @ealsur fyi - not sure current backlog or planned work items for CosmosDB Extension but this could be a good one

[hannesne]

This still seems to be an issue? Should we at least update the documents?

[jeffhollan]

Is still an issue. It is documented here https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2?tabs=csharp#http-trigger-look-up-id-from-route-data-using-sqlquery and does even link to this issue.

[nzthiago]

@jeffhollan It's been a while since first raised via https://github.com/Azure/azure-functions-host/issues/2554 but customers running into it from time to time still... Workarounds:

• Use route paths
• Bind to DocumentClient
• Maintain your own static DocumentClient instance...

None are as nice as Query Params if Query Params is your project's default though

[hannesne]

@jeffhollan the page you linked to has the information buried in the issue discussion at the bottom. Is that what you refer to? In that position it's not really discoverable unless you a) know that is the problem that is happening and b) look at that page's issue discussions. It would be better to add a note to this section: https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2-input?tabs=csharp#http-trigger-look-up-id-from-route-data-using-sqlquery which is where we describe using the sqlquery.

[nzthiago]

@hannesne there's been an update to the docs page since then so @jeffhollan's link doesn't work. The new place is https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2-input?tabs=csharp#http-trigger-look-up-id-from-route-data-using-sqlquery see just above the Note section there, copied here:

• The example shows how to use a binding expression in the SqlQuery parameter. You can pass route data to the SqlQuery parameter as shown, but currently you can't pass query string values.

[hannesne]

Cool, that works well. Thanks!

[SogoGolf]

I was wondering why this kept failing then came across this open item. REALLY would like the ability to reference both Query and Body from inside the sqlQuery parameter on a Javascript binding.

eg.

 {
      "name": "documents",
      "type": "cosmosDB",
      "direction": "in",
      "partitionKey": "type",
      "databaseName": "xxxx",
      "collectionName": "yyyy",
      "sqlQuery": "SELECT c.id FROM c where c.type = 'round' and ARRAY_LENGTH(c.holeScores) = {Body.numberHoles}",
      "connectionStringSetting": "xxxx_DOCUMENTDB"
  }

[jeroenvdmeer]

I ran into the same issue when using the JavaScript runtime with the Cosmos DB binding below and the following URL: /api/get-themes?id=1

{
      "name": "themes",
      "type": "cosmosDB",
      "direction": "in",
      "databaseName": "db",
      "collectionName": "collection",
      "sqlQuery": "SELECT c.id, c.name FROM c WHERE c.id = {Query.id}",
      "connectionStringSetting": "cosmos"
}

[2021-01-16T08:40:09.321Z] System.Private.CoreLib: Exception while executing function: Functions.ListThemes. Microsoft.Azure.WebJobs.Host: Error while accessing 'id': property doesn't exist.

[ConDar15]

I also ran into this issue. For me it was when using an EventGridTrigger and trying to access the properties of the events data, e.g. {data.referenceId}. This is a particularly annoying issue as this works as expected when put on the Id or PartitionKey properties, and has taken up about half a day of digging to find out what the issue is, and now I'll have to work around it with an IDocumentCollection rather than having nice clean bindings in the attributes.

[pnda489]

Any Timeline or Updates on this? Can't really make use of the bindings without this.

If you don't won't to fix this please close this issue.

[xiongyu0523]

I was trying hard to find answers and just figure out it has bee fixed already. User can just refer to the query parameter use its key in sqlQuery as route parameter. No need "Query." prefix.

say http://yourapi/a/b/c?myparam=myid

"sqlQuery": "SELECT * FROM c WHERE c.id = {myparam}"

I don't find a document tell this, or anyone come here to close the ticket, that's terrible..

[Charizard13]

Hey xiongyu0523, did you make it work? Because it throws me an error The 'GetPreviousEvents' function is in error: Microsoft.Azure.WebJobs.Host: Error indexing method 'GetPreviousEvents'. Microsoft.Azure.WebJobs.Host: Unable to resolve binding parameter 'departmentId'. Binding expressions must map to either a value provided by the trigger or a property of the value the trigger is bound to, or must be a system binding expression (e.g. sys.randguid, sys.utcnow, etc.).

[mentorfloat]

I was trying hard to find answers and just figure out it has bee fixed already. User can just refer to the query parameter use its key in sqlQuery as route parameter. No need "Query." prefix.

say http://yourapi/a/b/c?myparam=myid

"sqlQuery": "SELECT * FROM c WHERE c.id = {myparam}"

I don't find a document tell this, or anyone come here to close the ticket, that's terrible..

Neither SqlQuery = "SELECT * FROM c WHERE c.User = {Query.user}" nor SqlQuery = "SELECT * FROM c WHERE c.User = {user}" worked with an url of http://yourapi/a?user=myid. I'm using the latest SDK, Microsoft.Azure.WebJobs.Extensions.CosmosDB 3.0.10. Route path is still the way to go.