1

I was trying to write a query which is finding MAX value from all documents. The scenario is something like I have 100 Students Documents, in which student Name, roll number as well as array of Tests inside that array of Subject and its respective marks. So, I am getting highest marks among subject physics from all documents. But I am not getting it with student roll number. That I was trying to find out.

TestDoc is:

    Student[
    StudenName:"A",
    StudentRollNo :1,
    id:"1",
    StudentAdd:"---",
    Test1:[
    {
      SubName:"S1",
      Marks:20
    },
    {
      SubName:"S2",
      Marks:30
    },
    ...

    ],
    Test2:
    [
     Same as above
    ],         
    ],

    [
    STUDENT2
    ] ,

and so on

Query I am using is: select MAX(s.Marks) from c join test in c.Test1 join s in test.marks

Peter Pan
  • 23,476
  • 4
  • 25
  • 43
Shraddha
  • 115
  • 2
  • 11
  • You don't actually have 100 documents; you have one document with an array of 100 students. Was that intentional? – David Makogon Sep 21 '17 at 20:52
  • No I have 100 documents of students. – Shraddha Sep 22 '17 at 05:23
  • I think the way I have givn TestDoc is wrong. I have 100 students document. – Shraddha Sep 22 '17 at 05:28
  • What am finding is if I can get array of marks with student ID as well. Something like : { Marks : 33 , studentId : 1 },{Marks : 23 , studentId : 1 },{Marks : 38 , studentId : 2 }.... Using join Is it possible? – Shraddha Sep 22 '17 at 06:43

1 Answers1

2

According to your description, you want to implement function like GROUP BY in azure cosmosdb queries.

Per my experience, azure cosmosdb aggregation capability in SQL limited to COUNT, SUM, MIN, MAX, AVG functions. GROUP BY or other aggregation functionality are not be supported in azure cosmosdb now.

However, stored procedures or UDF can be used to implement your aggregation requirement.

You could refer to a great package documentdb-lumenize based on DocumentDb stored procedure.

For your first scenario in your post,I created two student documents in my azure cosmosdb account.

[
  {
    "id": "1",
    "StudenName": "A",
    "StudentRollNo": 1,
    "Test": [
      {
        "SubName": "S1",
        "Marks": 20
      },
      {
        "SubName": "S2",
        "Marks": 30
      }
    ],
  },
  {
    "id": "2",
    "StudenName": "B",
    "StudentRollNo": 2,
    "Test": [
      {
        "SubName": "S1",
        "Marks": 10
      },
      {
        "SubName": "S2",
        "Marks": 40
      }
    ],
  }
]

then I put the resultset searched by SQL below to the documentdb-lumenize mentioned above to get the max S2 mark.

SELECT  c.StudentRollNo,test1.Marks as mark FROM c
join test1 in  c.Test
where test1.SubName='S2'

enter image description here

For your second scenario in your comment,I removed the where clause of the SQL above.

SELECT  c.StudentRollNo,test1.Marks as mark FROM c
join test1 in  c.Test

and resultset like:

enter image description here

This applies only to one test.If you want to query multiple tests, you could use stored procedure.

You could also refer to SO threads below:

1.Azure DocumentDB - Group By Aggregates

2.Grouping by a field in DocumentDB

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • Result you are getting is different than me. – Shraddha Sep 26 '17 at 08:30
  • { "cubeConfig": { "groupBy": "StudentRollNo", "field": "mark", "f": "max" }, "filterQuery": "SELECT c.StudentRollNo,test1.Marks as mark FROM c join test1 in c.Test", "continuation": null, "stillQueueing": true, "savedCube": { "config": { "groupBy": "StudentRollNo", "field": "mark", "f": "max" }, – Shraddha Sep 26 '17 at 08:32
  • "cellsAsCSVStyleArray": [ [ "StudentRollNo", "_count", "mark_max" ], [ 1, 2, 30 ], [ 2, 2, 40 ] ], "summaryMetrics": {} }, "example": { "StudentRollNo": 1, "mark": 20 } } – Shraddha Sep 26 '17 at 08:32
  • This is what I am getting. I am satisfied with documentdb-lumenize. I need to try more things with it. – Shraddha Sep 26 '17 at 08:33
  • It's great for you to get query results by documentdb-lumenize.Thanks for your share. – Jay Gong Sep 26 '17 at 08:37