Cosmos DB parameterised SQL query not working with double quoted parameter

1
votes

I have this query working fine in the Data Explorer - querying for the existence of a Dictionary item:

SELECT VALUE COUNT(c) FROM c WHERE not IS_DEFINED(c.Answers["2"])

Given a container with data set such as (simplified):

[
   {
       "Name": "Donald",
       "Answers": {
           "1": {
               "a": 3,
               "isCorrect": false
           },
           "2": {
               "a": 7,
               "isCorrect": false
           }
       },
   },
   {
       "Name": "Boris",
       "Answers": {
           "1": {
               "a": 2,
               "isCorrect": false
           }
       },
   },
]

But I'm now trying to parameterise this in code and it doesn't appear to work unless I hardcode the parameter (i.e. Don't parameterise).

This does not work:

var query = new QueryDefinition("SELECT VALUE COUNT(c) FROM c WHERE not IS_DEFINED(c.Answers[\"@quNo\"])")
    .WithParameter("@quNo", 2);

But this does work:

var query = new QueryDefinition("SELECT VALUE COUNT(c) FROM c WHERE not IS_DEFINED(c.Answers[\"2\"])");

Any ideas why the parameterisation isn't working? I presume it is because of the double quotes, but not sure how to work around it.

I've tried changing to a string literal too, e.g.

@"SELECT ... <snip> not IS_DEFINED(c.Answers[""@quNo""])";

but this also does not work.

UPDATE:

I have added a RequestHandler to track exactly what is being sent to Cosmos. When sending parameterised, the format is:

{
    "query": "SELECT VALUE [{\"item\": COUNT(c)}]\nFROM c\nWHERE not IS_DEFINED(c.Answers[\"@quNo\"])",
    "parameters": [
        {
            "name": "@quNo",
            "value": 2
        }
    ]
}

As per suggestions, I have tried the parameter value as integer or string, but neither work.

Setting the value directly in the query is the only thing that seems to work:

{
    "query": "SELECT VALUE [{\"item\": COUNT(c)}]\nFROM c\nWHERE not IS_DEFINED(c.Answers[\"2\"])",
    "parameters": [
    ]
}

I assume this must have something to do with escape characters...

1
c#azure-cosmosdbazure-cosmosdb-sqlapi

1 Answers

1
votes

I haven't tried it, but I believe this should work:

var query = new QueryDefinition("SELECT VALUE COUNT(c) FROM c WHERE not IS_DEFINED(c.Answers[@quNo])")
    .WithParameter("@quNo", "2");