CODEWITHSUNDEEP
.netazure-cosmosdbazure-cosmosdb-sqlapi
Vi100
Vi100

Reputation: 4203

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

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!

Upvotes: 4

Views: 3775

Answers (1)

Jason Pan
Jason Pan

Reputation: 21916

UPDATE

I find we can get result by below code.

enter image description here

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

Upvotes: 5

Related Questions