SQLQuery not returning document queried

Question

Azure Function App with Cosmos DB + nodejs not working with SQLQuery (Input). No document returned.

I have successfully gotten an Id/PartionKey combo to return a document. I have tried many combos of SQL queries in the input's SQLQuery field such as from the docs to no avail. No error nor document is returned. I would expect my query to be:

SELECT * from Users where Users.UserName = '12345'

I have tried with and without a value in single quotes. I've tried hard coded (no binding) queries such as:

SELECT * from Users where Users.UserName = 12345

And ones with binding (with and without single quotes around {userName}):

SELECT * from Users where Users.UserName = {userName}

I have tried using {userName} by putting it into the routing:

Account/login/{userName}

and I have also tried using userName from the Query (with or without single quotes):

SELECT * from Users where Users.UserName = '{Query.userName}'

Partion key is: /id

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ]
}

I have done many hours of searches and reading and I understand binding may not be supported fully with the SQLQuery, however, even hard coding a value fails to return a document.

Sample document:

{
    "id": "e90ece01373e5d011fdaef2c20d9717b",
    "UserName": "17002",
    "password": "newpassword",
    "Address": "123 West ",
    "state": "FL",
    "city":  "mycity",
    "zip": "32222",
    "phoneNumber": "3215551212",
    "emailId": "email@email.com",
    "refNo": "0",
    "aboutUs": "someplace",
    ........
}

Function.json:

{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "post",
        "get"
      ],
      "route": "Account/login/{userName}"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "res"
    },
    {
      "type": "cosmosDB",
      "name": "myInput",
      "databaseName": "cdel",
      "collectionName": "Users",
      "connectionStringSetting": "cdel_DOCUMENTDB",
      "direction": "in",
      "sqlQuery": "SELECT * FROM Users where Users.UserName = {userName}"
    }
  ]
}

index.js:

module.exports = async function (context, req, myInput) {
    context.log('JavaScript queue trigger function processed work item');
    context.log('Passed in: ' + context.bindingData.userName);
    if (!myInput)
    {
        context.log("UserName not found");
        context.res = {
            status: 400,
            body: "UserName not found."
        }
    }
    else
    {
        context.log(req.query.password);
        context.log(myInput.password);
        context.log(context.bindings.myInput.password);

        if (myInput.password != req.query.password) {
            context.res = {
                status: 400,
                body: "Invalid logon."
            }
        }
        else {
              .....
        }
    }
    context.done();
};

Expect single document to be returned. None is returned. myInput.password = undefined

Is there a query monitor of sorts I haven't found so I can see what is happening?

Thank you in advance for help.

Answer 1

Cosmos is returning collection, you need to grab first element from it.

context.bindings.myInput[0].password

So the code is

module.exports = async function (context, req) {
    context.log('password: ' + req.body.password);
    if (context.bindings.myInput.length > 0)
        context.log('password cosmos: ' + context.bindings.myInput[0].password);
    context.res = {
        body: "OK"
    };
};

Answer 2

Try

select *
from Users
where UserName = '12345678'