在Azure Cosmos DB中是否有子句?如何使用它? [英] Is there having clause in azure cosmos db? How to use it?

问题描述

我试图编写一个查询，从所有文档中查找MAX值.情况类似于我有100个学生文档，其中学生姓名，卷号以及该科目数组及其相应分数内的测试数组.因此，我从所有文档中都获得了主题物理学的最高分.但是我没有学生卷号.我正试图找出答案.

  TestDoc是:学生[StudenName:"A"，学生卷编号:1，id:"1"，学生添加:"---"，测试1:[{子名称:"S1"，分数:20}，{子名称:"S2"，分数:30}，...]，测试2:[同上]，]，[学生2]，等等 

我正在使用的查询是:从c.test1中的c join test中选择MAX(s.Marks).

解决方案

根据您的描述，您想在天蓝色的cosmosdb查询中实现 GROUP BY 之类的功能.

根据我的经验，SQL中的 azure cosmosdb聚合功能仅限于 COUNT，SUM，MIN，MAX，AVG 个功能.

对于您评论中的第二种情况，我删除了上面SQL的 where子句.

  SELECT c.StudentRollNo，test1.标记为标记FROM c在c.Test中加入test1 

和结果集，例如:

这仅适用于一个测试.如果要查询多个测试，则可以使用存储过程.

您还可以在下面引用SO线程:

1. Azure DocumentDB-按聚合分组

2.按DocumentDB中的字段分组

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

解决方案

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'

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:

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

这篇关于在Azure Cosmos DB中是否有子句?如何使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！