CosmosDb throws Exception on Query with 'array_contains' with an array?

Question

I am trying to get multiple documents from CosmosDB by passing array of ids. This is the exception I get:

Message: {"errors":[{"severity":"Error","location":{"start":88,"end":88},"code":"SC1002","message":"Syntax error, unexpected end-of-file."}]}, Windows/10.0.19043 documentdb-netcore-sdk/2.11.2

Here is my code ( I tried different ways of passing the parameters) :


IList<string> ids = new List<string>();                
ids.Add("8D5E4144");                    
ids.Add("A5964F");
ids = devices.Select(x => x.DeviceId).ToList();
    
 SqlQuerySpec spec2 = new SqlQuerySpec
 {
      QueryText = "SELECT * FROM c WHERE (c.ttId = @tId And array_contains(@deviceids, c.deviceId)",
      Parameters = new SqlParameterCollection()
                     {
                          new SqlParameter("@tenantId", tId.ToString()),
                          new SqlParameter("@deviceids", ids)
                      }
                    };
    
    IDocumentQuery<DeviceDBSchema> devicesQuery2 = 
                 CreateDocumentQuery<DeviceDBSchema>(
                  sqlQuerySpec: spec2,
                  feedOptions: new FeedOptions()
                    {
                         MaxItemCount = (int?)maxResponseCount,
                         PartitionKey = new PartitionKey(tId.ToString()),
                         RequestContinuation = continuationToken
                     }).AsDocumentQuery();
    
                    var devicesResults2 = 
                        await devicesQuery2.ExecuteNextAsync<MySchema>(cancellationToken);

Any help or insights on how to fix this will be great. Thanks.

Not sure if any other issues but your query has two opening brackets and one closing one — Martin Smith
Few questions: 1) Your query expects @tId parameter however your parameter is named @tenantId. Is that a typo? 2) Why are you doing ids = devices.Select(x => x.DeviceId).ToList(); when you're creating a list of ids before that? Also, as @MartinSmith said, you have a missing closing bracket. — Gaurav Mantri

Scooby Scooby · Accepted Answer · 2021-06-08T23:49:47

0

votes

The issue was due to missing closing bracket in the query.

CosmosDb throws Exception on Query with 'array_contains' with an array?

1 Answers