Azure-docs: Cosmos DB Input SQL Query requires values as String - Javascript

[itsknob]

I'm not sure if this is the intended behavior, but I spent a better part of the day trying to send an Azure Storage Queue Message to an Azure Function using a Cosmos DB input, utilizing the SQL Query option, to get specific documents, and I think I have found an issue.

When I tried to query documents in the form {"String": Number} where the database's document match that form, the query was unsuccessful and returned an empty array. However, when querying the data directly in the Cosmos DB section of the Azure Portal, a similar query (I.E. Select * from c where c.mrnum = 157739 ) would succeed. This lead me to believe that a query with just a number should work when the form of the data is the same.

Here are examples that did and did not work.

Azure Function - Cosmos DB Input - SQL Query:

SELECT * FROM d where d.mrnum = {mrnum}

Azure Storage Queue - Message:

/* Did not work */
{"mrnum": 157739}

/* Did work (when documents matched)*/
{"mrnum": "157739"}

Documents:

/* Did not allow query */
{
    "mrnum": 157739,
    "etc": "..."
},
{
    "mrnum": 163358,
    "etc": "..."
}

/* Did allow query (when queue message matched) */
{
    "mrnum": "157739",
    "etc": "..."
},
{
    "mrnum": "163358",
    "etc": "..."
}

Function:

module.exports = function (context, message, docs) {
    context.log(docs);
    context.done();
}

If this is the intended behavior, then I believe this should be clearly stated in the documentation so that people don't run into this same issue.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: fa5bcb79-2af8-3b0e-c78c-0c89a2535e15
• Version Independent ID: 31b87687-7841-f46f-8c97-38898e7b97db
• Content: Azure Cosmos DB bindings for Functions 2.x
• Content Source: articles/azure-functions/functions-bindings-cosmosdb-v2.md
• Service: azure-functions; cosmos-db
• GitHub Login: @craigshoemaker
• Microsoft Alias: cshoe

[Alberto-Vega]

@itsknob Thanks for the feedback! We are currently investigating and will update you shortly.

[PramodValavala-MSFT]

@itsknob Thanks for reporting this! This seems to be a problem when dealing with numbers specifically.

I have created an issue - Azure/azure-webjobs-sdk-extensions#533 to further investigate.

[PramodValavala-MSFT]

@itsknob With respect to this document and the issue at hand, there is no doc update required at the moment.

We will now proceed to close this thread. If there are further questions regarding this matter, please tag me in your reply. We will gladly continue the discussion and we will reopen the issue.

[PramodValavala-MSFT]

@itsknob While we investigate this issue further, here is a workaround you can try out

1. Create a User Defined Function in Cosmos DB with this code

function ConvertToNumber(str){
    return Number(str);
}

1. And use it in your function.json like this

{
  "bindings": [
    {
      "name": "myQueueItem",
      "type": "queueTrigger",
      "direction": "in",
      "queueName": "js-queue-items",
      "connection": "<connection-string-setting-name>"
    },
    {
      "type": "cosmosDB",
      "name": "inputDocument",
      "databaseName": "inDatabase",
      "collectionName": "d",
      "connectionStringSetting": "<connection-string-setting-name>",
      "sqlQuery": "select * from c where c.num = udf.ConvertToNumber({num})",
      "direction": "in"
    }
  ]
}

This workaround was shared initially on StackOverflow by Amor.

[itsknob]

Thank you for investigating this issue and the workaround.