Query CosmosDB document using C#

Question

I have documents stored in cosmos db, I have multiple documents for a same "stationkey"(partition key), in this example stationkey "ABC" has more than one documents with "yymm" has "2018-02" & "2018-01" e.t.c,

query that i am trying is get all "avg" & "dd" fields along with "yymm" for the given stationkey and yymm filter combination

I am trying to query using C#, I am trying to get "avg", "dd" & "yymm" fields from "data" array, the query that I have written is giving entire "data" array.

var weatherQuery = this.docClient.CreateDocumentQuery<WeatherStation>(docUri, queryOptions)
            .Where(wq => wq.stationName == stationKey && lstYearMonthFilter.Contains(wq.yearMonth))
            .Select(s => s.data);

what is the best way to query specific fields in from a document array?

Rafat Sarosh · Accepted Answer · 2018-02-16 21:50:57Z

2

So you got the data in s => s.data. To get only the avg from the array you have to do another projection as following:

.Select (s => s.data.Select ( a => a.avg ))

Modifying my answer as you say you don't find 'Select' on 'data'.

Define a class MyDocument as such:

public class Datum
{
    [JsonProperty("dd")]
    public string dd;

    [JsonProperty("max")]
    public int max;

    [JsonProperty("min")]
    public int min;

    [JsonProperty("avg")]
    public int avg;
}

public class MyDocument : Document
{
    [JsonProperty("id")]
    public string id;

    [JsonProperty("data")]
    public Datum[] data;
}

modify your code accordingly

IDocumentQuery<MyDocument> query = client.CreateDocumentQuery<MyDocument>(UriFactory.CreateDocumentCollectionUri(_database, _collection),
            new FeedOptions { MaxItemCount = -1, EnableCrossPartitionQuery = true, MaxDegreeOfParallelism = 199, MaxBufferedItemCount = 100000})
            .Where(predicate)
            .AsDocumentQuery();

      while (query.HasMoreResults)
        {
            FeedResponse<MyDocument> feedResponse = await query.ExecuteNextAsync<MyDocument>();
            Console.WriteLine (feedResponse.Select(x => x.data.Select(y => y.avg)));
        }

HTH

edited Feb 16, 2018 at 21:50

answered Feb 16, 2018 at 19:52

Rafat Sarosh

9657 silver badges20 bronze badges

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

6 Comments

VInayK Over a year ago

Yes I already tried and getting an exception saying "Method 'Select' is not supported., documentdb-dotnet-sdk/1.20.1 Host/64-bit MicrosoftWindowsNT6.1.7601ServicePack1"

VInayK Over a year ago

Thanks for you updated response @rafat , I have written the query in a similar way but this way of query is actually dealing with entire document (cost more RU/s on cosmos db), what I am actually looking is to get only the required fields like "SELECT root.data.avg, root.data.dd FROM root WHERE <filter criteria>"

Rafat Sarosh Over a year ago

In that case write a query like this SELECT d.dd, d.avg FROM c join d in c.data where c.id = "ABC" and use "CreateDocumentQuery" to get what you want from CosmosDB. HTH

VInayK Over a year ago

thanks for the reply @rafat. Can we achieve the same with C#? also in this example using query vs C# which one is better ?

Rafat Sarosh Over a year ago

You write a c# code and pass this sql .. please look at createdocumentquery examples

|

Jason · Accepted Answer · 2018-02-21 00:36:15Z

1

You can select only specific fields from the array items using a double-nested anonymous class - see the altered SelectMany below. This will return yymm with every Datum, so may not be as efficient as just selecting the entire array - definitely measure the RU/s in both cases.

var weatherQuery = this.docClient.CreateDocumentQuery<WeatherStation>(docUri, queryOptions)
    .Where(wq => wq.stationName == stationKey && lstYearMonthFilter.Contains(wq.yearMonth))
    .SelectMany(x => x.data.Select(y => new { x.yymm, data = new[] { new { y.dd, y.avg } } }))
    .AsDocumentQuery();

var results = new List<WeatherStation>();

while (weatherQuery.HasMoreResults)
{
    results.AddRange(await weatherQuery.ExecuteNextAsync<WeatherStation>());
}

var groupedResults = results
    .GroupBy(x => x.yymm)
    .Select(x => new { x.First().yymm, data = x.SelectMany(y => y.data).ToArray() })
    .Select(x => new WeatherStation() { yymm = x.yymm, data = x.data });

answered Feb 21, 2018 at 0:36

Jason

3691 silver badge4 bronze badges

2 Comments

VInayK Over a year ago

Thanks for the reply Jason, Do you know what is the best way to find RU/s in C# request to cosmosDB?(just like above code that you have given)

Jason Over a year ago

The object returned from "await weatherQuery.ExecuteNextAsync<WeatherStation>()" has a RequestCharge property on it indicating the consumed RUs. To get the total you would need to add them up from all iterations of the loop. Search for 'RequestCharge' here for more info: azure.microsoft.com/en-us/blog/…

Collectives™ on Stack Overflow

Query CosmosDB document using C#

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related