Azure CosmosDB:存储过程基于查询删除文档 [英] Azure CosmosDB: stored procedure delete documents based on query

问题描述

目标是输入一个简单的字符串查询，例如

The goal is to input a simple string query like

SELECT * 
FROM c 
WHERE c.deviceId = "device1"

，所有生成的提取文档都需要删除.

and all resulting fetched documents need to be deleted.

我发现有关使用存储过程执行此操作的旧文章，但是我无法使其在新" UI上正常工作.

I have found very old posts about doing this with a stored procedure, but I can't get it to work properly with the "new" UI.

非常感谢.

编辑:我感觉@ jay-gong指出了正确的方向，但是我遇到了他的解决方案问题:

EDIT: I feel like @jay-gong pointed to the correct direction but I encountered a problem with his solution:

我可以正确创建存储过程，但是当我尝试执行该存储过程时，它会询问我提供的分区键，但是执行后，它不会删除任何文档.

I can correctly create the stored procedure but when I try to execute it it asks for the partition key, which I give but after executing, it doesn't delete any document.

该集合只有几个文档，其分区键为/message/id，这是我在分区键字段中写的.

The collection just has a few documents and its partition key is /message/id which is what I wrote in the partition key field.

推荐答案

因为cosmos db不支持通过SQL删除文档(

Since cosmos db does not support deleting documents by SQL (Delete SQL for CosmosDB), you could query the documents and delete them by Delete SDK one by one. Or you could choose bulk operation in stored procedure.

您可以完全按照存储过程进行批量删除 ="nofollow noreferrer">示例代码来实现您的要求.

You could totally follow the stored procedure bulk delete sample code to implement your requirements which works for me.

function bulkDeleteProcedure(query) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var response = getContext().getResponse();
    var responseBody = {
        deleted: 0,
        continuation: true
    };

    query = 'SELECT * FROM c WHERE c.deviceId="device1"';

    // Validate input.
    if (!query) throw new Error("The query is undefined or null.");

    tryQueryAndDelete();

    // Recursively runs the query w/ support for continuation tokens.
    // Calls tryDelete(documents) as soon as the query returns documents.
    function tryQueryAndDelete(continuation) {
        var requestOptions = {continuation: continuation};

        var isAccepted = collection.queryDocuments(collectionLink, query, requestOptions, function (err, retrievedDocs, responseOptions) {
            if (err) throw err;

            if (retrievedDocs.length > 0) {
                // Begin deleting documents as soon as documents are returned form the query results.
                // tryDelete() resumes querying after deleting; no need to page through continuation tokens.
                //  - this is to prioritize writes over reads given timeout constraints.
                tryDelete(retrievedDocs);
            } else if (responseOptions.continuation) {
                // Else if the query came back empty, but with a continuation token; repeat the query w/ the token.
                tryQueryAndDelete(responseOptions.continuation);
            } else {
                // Else if there are no more documents and no continuation token - we are finished deleting documents.
                responseBody.continuation = false;
                response.setBody(responseBody);
            }
        });

        // If we hit execution bounds - return continuation: true.
        if (!isAccepted) {
            response.setBody(responseBody);
        }
    }

    // Recursively deletes documents passed in as an array argument.
    // Attempts to query for more on empty array.
    function tryDelete(documents) {
        if (documents.length > 0) {
            // Delete the first document in the array.
            var isAccepted = collection.deleteDocument(documents[0]._self, {}, function (err, responseOptions) {
                if (err) throw err;

                responseBody.deleted++;
                documents.shift();
                // Delete the next document in the array.
                tryDelete(documents);
            });

            // If we hit execution bounds - return continuation: true.
            if (!isAccepted) {
                response.setBody(responseBody);
            }
        } else {
            // If the document array is empty, query for more documents.
            tryQueryAndDelete();
        }
    }
}

此外，据我所知，存储过程有5秒的执行限制.如果遇到超时错误，可以将延续令牌作为参数传递到存储过程中，并多次执行存储过程.

Furthermore, as I know, stored procedure has 5 seconds execute limitation. If you crash into the time out error, you could pass the continuation token as parameter into stored procedure and execute stored procedure several times.

更新答案:

Update Answer:

对于存储过程中的分区集合，分区键是必需的.(请参阅详细说明:

Partition key is necessary for the partitioned collection in the stored procedure.(Please refer to the detailed explanation :Azure Cosmos DB asking for partition key for stored procedure.)

因此，首先，以上代码需要您的分区键.例如，您的分区键定义为/message/id，数据如下:

So, firstly,above code needs your partition key.For example, your partition key is defined as /message/id and your data as below:

{
    "message":{
        "id":"1"
    }
}

然后，您需要将pk作为message/1传递.

Then you need to pass the pk as message/1.

很显然，您的查询sql跨分区，建议您采用 http触发azure函数而不是存储过程.在该函数中，您可以使用cosmos db sdk代码进行查询和删除操作.别忘了将EnableCrossPartitionQuery设置为true .请参考这种情况: Azure Cosmos数据库要求存储过程的分区键.

Obviously,your query sql crosses partitions,I suggest you adopt http trigger azure function instead of stored procedure.In that function,you could use cosmos db sdk code to do the query and delete operations.Don't forget set the EnableCrossPartitionQuery to true. Please refer to this case:Azure Cosmos DB asking for partition key for stored procedure.

这篇关于Azure CosmosDB:存储过程基于查询删除文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！