CODEWITHSUNDEEP
c#searchazure-cosmosdb
satish kumar V
satish kumar V

Reputation: 1755

Query in DocumentDB for Searching

I have a documents something like

{ id : "123", name : "sai", marks : {English : 50, Maths : 60, Science :90} }

marks may contain any no of subjects and with any name. (schema is not fixed)

I want the list of students who failed in any one subject (<35 in any subject)

Is there any method to do this or need to change schema to get the results?

Any help would be greatly appreciated.

Upvotes: 1

Views: 469

Answers (1)

Ryan CrawCour
Ryan CrawCour

Reputation: 2728

So with a slight modification to your schema my document now looks like this - 

{id :  "123", name :  "sai", marks : [
        {Subject :  "English", Grade :  50},
        {Subject :  "Maths", Grade :  60},
        {Subject :  "Science", Grade :  90}
    ]
}

Then a custom IndexingPolicy IncludePath has to be added that allows for Range queries like this - 

    {
        Path :  /"marks"/[]/"Grade"/?,
        IndexType :  Range,
        NumericPrecision :  3
    }

Once this is in place, you can run the following query

    SELECT VALUE students FROM students JOIN marks IN students.marks WHERE marks.Grade < 60

If you wanted to do this in .NET with LINQ then let me know and I will post the LINQ for this query.

If you don't add the RANGE index on the Grade field you could still do this query but you would have to specify the "Allow Scan in Query" option which would force a scan. Not great for performance, this way is much better.

If you can't change your schema to what I have, then let me know and I will play a bit more with the query

Upvotes: 2

Related Questions