如何在Cosmos Db中查询子文档(列表类型) [英] How to query sub document (list type) in Cosmos Db

问题描述

我在Azure CosmosDB中有一个集合，每个文档如下所示:

I have a collection in Azure CosmosDB, and each document looks like this:

{
  "id": "random",
  "TeacherName": "Ben",
  "Students": [
    {
      "Name": "John",
      "Telephone": ""
    },
    {
      "Name": "Mary",
      "Telephone": ""
    }
  ],
}

TeacherName是字符串，Students是学生列表

TeacherName is string, Students is a list of student

我需要做的:给定用户名(user1)，查询并返回所有文档，教师名称为"user1"或学生名称为"user1".

I need to do: Given a user name (user1), query and return all the documents, either teacher name is "user1" or there is a student with name "user1".

我尝试了一些选择，但无法做到.

I tried a few options, but cannot do it.

到目前为止，我找到的最接近的解决方案是使用.SelectMany()，但是我发现.SelectMany将进行联接并复制返回结果.

The closest solution I found so far is to use .SelectMany(), but I found .SelectMany will do a join and will duplicate the return results.

这是我的查询:

client.CreateDocumentQuery().SelectMany((x) => x.Students.Where(s=>s.Name == "user1" || x.TeacherName == "user1")   

如果仅上述样本文档在集合中，并且当我搜索用户名"Ben"时，将返回2条记录((结果数)*(学生数)).我必须在客户端删除重复项，并且分页有点坏.

If only the above sample document in the collection, and when I searched user name "Ben", 2 records will be returned ((number of result) * (number of students)). I have to remove the duplicate at client side and pagination is kind of broken.

是否可以发出单个查询来实现我所需要的?

Is it possible to issue a single query to achieve what I need?

推荐答案

client.CreateDocumentQuery().SelectMany((x) => x.Students.Where(s=>s.Name == "user1" || x.TeacherName == "user1")

实际上，您代码中的SelectMany方法类似于下面的sql:

Actually the SelectMany method in your code is similar to the sql as below:

SELECT c.id from c
join x  in c.Students
where c.TeacherName ='Ben' or x.Name = 'Ben'

输出

Output

[
  {
    "id": "1"
  },
  {
    "id": "1"
  }
]

如果有联接，将有重复的数据.据我所知，Azure Cosmos DB还不支持自动删除重复数据(例如传统数据库中的Distinct关键字).

If there's a join, there's going to be duplicate data. As far as I know, automatic removal of duplicate data is not supported (like Distinct Keywords in traditional database) by Azure Cosmos DB yet.

似乎无法删除查询SQL级别中的重复数据.

It seems that there is no way to remove the repeat data in the query SQL level.

如果您不想在本地循环中处理查询结果集，强烈建议您使用

If you don't want to handle with your query result set in the loop locally, I strongly suggest you using a stored procedure to handle with result data in Azure Cosmos DB to release the pressure on your local server.

或者，如果数据不是太大，可以直接在存储过程中完成查询.请参考以下js代码:

Or , you could complete the query directly in the Stored Procedure if your data is not too large.Please refer to the js code as below:

// SAMPLE STORED PROCEDURE
function sample(idsArrayString,courses) {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
        function (err, feed, options) {
            if (err) throw err;
            if (!feed || !feed.length) getContext().getResponse().setBody('no docs found');
            else {

                var returnResult = [];
                for(var i = 0;i<feed.length;i++){
                    var doc = feed[i];
                    if(doc.TeacherName == 'Ben'){
                        returnResult.push(feed[i]);
                        break;
                    }

                    var std = doc.Students;
                    for (var s in std) {
                        if(s.Name == 'Ben'){
                            returnResult.push(feed[i]);
                            break;
                        }
                    } 
                }
                getContext().getResponse().setBody(returnResult);
            }
        });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

---

更新答案:

---

Update Answer:

我查看了 Azure Cosmos DB定价详细信息 .然后，它并不表明存储过程比单个查询更昂贵.实际上，成本取决于Rus和RuS取决于查询的复杂性和并发性等.

I checked the Azure Cosmos DB pricing details. Then it doesn't show that stored procedure is more expensive than a single query. Actually, the cost depends on Rus and RuS depends on the complexity of the query and the amount of concurrency, etc.

您可以参考RU 文档 .另外，您可以通过http请求标头:x-ms-request-charge知道RU的费用.请查看此有用的跟踪: 如何计算Azure Cosmos服务器端脚本中使用的DB RU .

You could refer to the RUs document. Also , you could know the RUs charge by the http request header :x-ms-request-charge. Please see this useful trace: How to caculate the Azure Cosmos DB RU used in server side scripting.

希望它对您有帮助.

这篇关于如何在Cosmos Db中查询子文档(列表类型)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！