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?

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