Pass multi-value / array GET parameter to Azure Function and use it in CosmosDB

Question

I have a Azure function (http triggered) which returns documents from a CosmosDB (SQL API).

CosmosDB has been integrated and works well when i define a SQL QUERY like:

SELECT * FROM c where c.id = {documentID}

when i trigger a http request with the following url i will get the document with ID 1 from the database, as expected.

blub.azure.net/API/myFunc?documentID=1

now i want to use multiple IDs in the parameterized SQL Query. How i am supposed to do this with Azure. Normally i would expect to use something like the following URL and SQL Query to get the desired result, but it will not work:

blub.azure.net/API/myFunc?documentIDs=1&documentIDs=2

SELECT * FROM c WHERE c.id IN ({documentIDs})

NOTE: i know that i can also pass a json string and decode it in the function but the cosmodb has more than 15 million entries and i can't pass it to the function, the filtering should be done on sql side. So i need to use the query feature to prefilter it before it will bound to the function.

my function.json:

{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "res"
    },
    {
      "type": "documentDB",
      "name": "lastDataPoints",
      "databaseName": "dp-db-04",
      "collectionName": "lastDataPointsCollection",
      "connection": "ldpdbconnectionstring",
      "direction": "in",
      "sqlQuery": "SELECT * FROM c WHERE c.id = {documentID}"
    }
  ],
  "disabled": false
}

This is something you're going to have to implement in your Azure Function. Your question is not very specific, but if the question really is "How can I get multiple documents with multiple IDs" there are two ways I know of. I recommend the first 1. Do N reads/gets as defined here:docs.microsoft.com/en-us/rest/api/cosmos-db/get-a-document 2. Perform a query with a logical OR inside of it: docs.microsoft.com/en-us/rest/api/cosmos-db/query-documents — Joshua Krstic
Thanks for your opinion. Azure functions allows you to integrate a cosmosDB collection into the function, directly. The data is then bound directly into the context as a Variable. You can use the http query parameter inside the SQL query of your integrated cosmosDB collection. I described how it works in the question. Your solutions would work but will have a huge latency delay due additonal http requests. why using a nested http request when SQL can directly be addressed. docs.microsoft.com/en-Us/azure/azure-functions/… — cinatic

Alexey Rodionov Alexey Rodionov · Accepted Answer · 2018-05-24T00:28:19

There is no way to add array parameter.

"In order to prevent injection attacks, any binding string used in the SqlQuery property is replaced with a SqlParameter before being sent to your Document DB database". From:

https://github.com/Azure/azure-webjobs-sdk-extensions/blob/4fabead81601aef32f87067dd7cb60bbf86681db/README.md#sql-query-support

Pass multi-value / array GET parameter to Azure Function and use it in CosmosDB

1 Answers