javascript - 通过复杂类型 cosmosDB azure 的存储过程进行批量更新

Question

我尝试通过服务器端的存储过程更新 cosomosdb 中的大量复杂文档。我的客户端是用C#开发的。

我想实现存储过程，将类(class)详细信息更新到学生列表，我想更新属于给定 courseDetiels 的 ids 列表的所有学生的类(class)详细信息

1. 如何创建查询来返回 ID 包含在 ids 参数中的所有文档。

2.如何更新复杂对象例如:

   public class CourseDetiels : Microsoft.Azure.Documents.Resource
{
    [JsonProperty("name")]
    public String CName { get; set; }

    [JsonProperty("finalGrade")]
    public double FinalGrade{ get; set; }
    [JsonProperty("GradesInWorks")]
    public double[] GradesInWorks{ get; set; }

}

我需要在学生类(class)的类(class)字典中仅更新用户在特定学期修读的类(class):

    public class Student: Microsoft.Azure.Documents.Resource
{
    [JsonProperty(PropertyName = "id")]
    public String ID { get; set; }
    [JsonProperty(PropertyName = "Courses")]
    public Dictionary<string, CourseDetiels> Courses{ get; set; }

}

这是我想写的伪代码......

function UpdateCourses(ids,courseDetiels) {

   var collection = getContext().getCollection();
   var collectionLink = collection.getSelfLink();
   var response = getContext().getResponse();
   var docCount = 0;
   var counter = 0;

   UpdateCoursesByIds(ids,complexObject);

   function UpdateCoursesByIds(ids,complexObject,continuation) {
        var query = {
            query: "select * from root c where ...." **//how to select only documents that contains in ids??**
        };

        var requestOptions = {
            continuation: continuation
        };

        var isAccepted =
            collection
            .queryDocuments(collectionLink,
                            query,
                            requestOptions,
                            function queryCallback(err, documents, responseOptions) {
                                     if (err) throw err;
                                     if (documents.length > 0) {
                                        // If at least one document is found, update it.
                                        docCount = documents.length;
                                        for (var i=0; i<docCount; i++){
                                            UpdateDoc(documents[i]);
                                        }
                                        response.setBody("Updated " + docCount + " documents");
                                      }
                                      else if (responseOptions.continuation) {
                                        UpdateCoursesByIds(responseOptions.continuation);
                                      } else {
                                             throw new Error("Document not found.");
                                             }
                            });

        if (!isAccepted) {
            throw new Error("The stored procedure timed out");
        }
    }

    function UpdateDoc(document) {
        //Optimistic concurrency control via HTTP ETag.
        var requestOptions = { etag: document._etag };

        **//I NEED TO UPDATE ONLY THE COURSES THAT TAKEN ....**

        var isAccepted = collection
                         .replaceDocument(document._self,
                                          document,
                                          requestOptions,
                                          function replaceCallback(err, updatedDocument, responseOptions) {
                                                   if (err) throw err;
                                                   counter++;
                                           });

        // If we hit execution bounds - throw an exception.
        if (!isAccepted) {
            throw new Error("The stored procedure timed out");
        }
    }
}

谢谢。

Answer 1

1.How I create query that return all the document with id that contain in the ids parameter.

您可以在 Azure Cosmos 中使用 IN 运算符 SQL APIs如下:

SELECT * FROM c WHERE c.id in ("1","2",....,"10")

var idsArrString="";
for(var i = 0;i<ids.length;i++){
   idsArrString+="'"+ids[i]+"',";
}

idsArrString=idsArrString.substr(0,idsArrString.length-1);

var query = {
   query: "select * from root c where c.id in ("+ idsArrString + ")";
};

2.How I can update complex-object

根据您的需要，我创建了一个存储过程，如下所示:

    function sample(idsArrayString,courses) {
        var collection = getContext().getCollection();

        // Query documents as you want
        var isAccepted = collection.queryDocuments(
            collection.getSelfLink(),
// 1:Please replace the ("1","2") with your idsArrayString

            'SELECT * FROM root r where r.id in ("1","2")',
            function (err, feed, options) {
                if (err) throw err;
                if (!feed || !feed.length) getContext().getResponse().setBody('no docs found');
                else {
                    for(var i = 0;i<feed.length;i++){
                        var doc = feed[i];
// 2:Please replace "maths" with your courses obj
                        doc.course = "maths";
                        collection.replaceDocument(doc._self,doc,                           function(err) {
                                 if (err) throw err;
                     });
                    }
                    getContext().getResponse().setBody(JSON.stringify("success"));
                }
            });

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

请注意，代码中的两个注释将替换为您自己的参数。

然后您可以在 C# 代码中执行存储过程:

// execute the stored procedure
client.executeStoredProcedureAsync('dbs/testdb/colls/testColl/sprocs/<your stored procedure id>')
    .then(function (response) {
        console.log(response.result); 
    }, function (err) {
        console.log("Error", error);
    });

更多详情请引用此official tutorial .

希望对您有帮助。

<小时/>

更新答案:

您可以使用上述评论 2 更新文档中的类(class)详细信息，如下所示:

doc.courseDetails.finalGrade = "50";

请引用document中提到的示例代码如下我提供了

client.executeStoredProcedureAsync('dbs/testdb/colls/testColl/sprocs/createMyDocument',
              <your arguments>);

<小时/>

更新答案 2:

我按照您提供的方式创建了示例文档。

{
    "id": "4",
    "StudenName": "Jay",
    "Courses": {
        "Math": {
            "Grade": "A",
            "Place": "NYU"
        },
        "Phys": {
            "Grade": "B",
            "Place": "MIT"
        }
    },
    "Rank": "AA"
}

然后我修改上面存储过程 JavaScript 代码中的注释 2，将 Grade 替换为“C”。

for(var i = 0;i<feed.length;i++){
                    var doc = feed[i];
                    var cources = doc.Courses;
                    for (var key in cources) {
                        if (cources.hasOwnProperty(key)) {
                            console.log(key + " -> " + cources[key]);
                            cources[key].Grade ="C";
                        }
                    }
                    doc.Courses = cources;
                    collection.replaceDocument(doc._self,doc, 
                    function(err) {
                             if (err) throw err;
                 });
                }

更改的文档:

{
    "id": "4",
    "StudenName": "Jay",
    "Courses": {
        "Math": {
            "Grade": "C",
            "Place": "NYU"
        },
        "Phys": {
            "Grade": "C",
            "Place": "MIT"
        }
    },
    "Rank": "AA"
}