Obtain a list of dynamic objects from a CosmosDB database with .NET SDK v.4

Question

I'm trying to query a database where the stored entities have different properties and I want to retrieve some fields from this information with diferently shaped queries (thats the point of using an schemaless database) but I'm having no luck with CosmosDB.

As I don't have a class (nor I want) for each kind of query result, I want to get them as a list of dynamics but I don't know how to retrieve them. I've tried:

var queryResultSetIterator = container.GetItemQueryIterator<dynamic>(queryDefinition, null, new QueryRequestOptions() { PartitionKey = new PartitionKey(idParticion), MaxItemCount = -1 });

List<dynamic> results = new List<dynamic>();

await foreach (dynamic item in queryResultSetIterator)
{
    results.Add(item);
}

But the items I get are of type System.Text.Json.JsonDocument so it doesn't work as expected;

If I try with ExpandoObjects I can't deserialize the results with new new System.Text.Json serializer, because as of today it doesn't support deserializing to ExpandoObjects:

results.Add(JsonSerializer.Deserialize<ExpandoObject>(item));

The only way it works for now is using the old Newtonsoft Json serializer but it seems to me an overkill to have both libraries present on the application, with all of that implies.

Any other idea of how to obtain a dynamic projection from CosmosDB?

Thanks in advance!

Has your problem been solved? Is there any progress? – Jason Pan Sep 23 '20 at 09:41 — Jason Pan, Sep 23 '20 at 09:41

Jason Pan · Answer 1 · 2020-09-03T04:36:59.637

UPDATE

I find we can get result by below code.

We can view the details of result[0] from Dynamic View. So I try to find way to convert dynamic view as an object. It is recommand me to use reflection to solve it.

You can search it by google with keywords dynamic view as object c#.

private static async Task QueryItemsAsync(CosmosClient cosmosClient)
{
    var sqlQueryText = "select *from c";//"SELECT{ 'name': f.name,'description': f.description,'startDateUtc': f.startDateUtc,'categories': f.categories} MyJasonResult FROM c f";

    CosmosContainer container = cosmosClient.GetContainer(Program.DatabaseId, Program.ContainerId);

    QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);

    List<dynamic> result = new List<dynamic>();
    await foreach (dynamic item in container.GetItemQueryIterator<dynamic>(queryDefinition))
    {
        string itemstring = item.ToString();
        var kk = JsonConvert.DeserializeObject<ExpandoObject>(itemstring);
        result.Add(kk);

        Console.WriteLine("\tRead {0}\n", item);
    }
}

PRIVIOUS

You can try to use below code. Which can help you solve the issue.

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}");
    }
}

The following post should help you:

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

Thanks for suggestion but on the .NET SDK v4 there's no HasMoreResults method, instead of it there's an awaited foreach, so I don't know how to get the response.Diagnostics object... — Vi100, Sep 02 '20 at 18:23
Is stated in the question that I don't want to use the old Newtonsoft Json deserializer, so your answer doesn't help too much (I already did it that way). Thanks, anyway I upvote it, but can't mark it as accepted, since it's not what I was asking for... — Vi100, Sep 23 '20 at 18:31

Obtain a list of dynamic objects from a CosmosDB database with .NET SDK v.4

1 Answers1