Cosmos DB Array Query Doesn't Work in .NET SDK

Question

I'm attempting to get all distinct category values used in a container, where categories is an array of strings.

Looking at the Cosmos DB SQL docs, I've constructed the following query which works in the Emulator when I execute the SQL and returns an array of distinct categories as expected.

SELECT DISTINCT * FROM c IN s.categories

Results in

[
    "Running",
    "Rugby",
    "Icon"
]

However, if I use the same query via the Cosmos .NET SDK I get an error

"Identifier 'session' could not be resolved."

Why would this query work in the Emulator but not via the SDK? Am I doing something wrong or this is an issue with the SDK?

Sample data:

{
"name": "Sample Entity",
"description": "<p></p>",
"startDateUtc": "2020-07-13T19:10:00Z",
"endDateUtc": "2020-07-13T20:00:00Z",
"categories": [
    "Running",
    "Icon"
],
"id": "h3foyhfk0a3betj1",
"dateCreated": "2020-07-15T06:33:57.9406583Z",
"lastUpdated": "2020-08-13T17:04:18.6182079Z" }

The SDK Code:

var query = new QueryDefinition("SELECT DISTINCT * FROM c IN s.categories");
var results = new List<TEntity>();
            
var resultSetIterator = _container.GetItemQueryIterator<TEntity>(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(partitionKey) });
while (resultSetIterator.HasMoreResults)
{
      var response = await resultSetIterator.ReadNextAsync();
      results.AddRange(response);
      if (response.Diagnostics != null)
      {
           _logger.LogTrace($"\nQueryWithSqlParameters Diagnostics: {response.Diagnostics}");
      }
}

return results;

Using the same collection? Is your SDK version using your local emulator? — mxmissile
– mxmissile, Commented Aug 13, 2020 at 21:16

buymeacoffee · Accepted Answer · 2020-08-14 05:34:52Z

3

Remove , requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(partitionKey).

Then you will get result. There maybe wrong in requestOptions when you use.

var query = new QueryDefinition("SELECT DISTINCT * FROM c IN s.categories");
var results = new List<dynamic>();

var resultSetIterator = container.GetItemQueryIterator<dynamic>(query);
while (resultSetIterator.HasMoreResults)
{
    var response = await resultSetIterator.ReadNextAsync();
    results.AddRange(response);
    if (response.Diagnostics != null)
    {
        Console.WriteLine($"\nQueryWithSqlParameters Diagnostics: {response.Diagnostics}");
    }
}

answered Aug 14, 2020 at 5:34

buymeacoffee

22.6k2 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RichMercer Over a year ago

Thank you, that was really helpful. I didn't even think about the partition key adjusting the query. Would there be a way to do the same query limited to only one partition? In this case I don't want a cross-partition query.

Collectives™ on Stack Overflow

Cosmos DB Array Query Doesn't Work in .NET SDK

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related