DocumentDB-查询结果顺序 [英] DocumentDB - query result order

问题描述

我正在使用Azure DocumentDB存储有关用户的信息.这些文档包含一个名为 date_created 的属性.我想向documentDB发送查询，以检索存储在此集合中的所有用户.我还希望将结果从最新到最旧排序.

I am using Azure DocumentDB to store information about users. These documents contain a property called date_created. I'd like to send a query to documentDB to retrieve ALL users stored within this collection. I also want the result to be ordered from newest to oldest.

我目前在DocumentDB中实现了一个存储过程，该过程遍历集合并根据date属性对结果进行排序.但是，我有2个问题:

I currently implemented a stored procedure in DocumentDB that goes through the collection and sorts the results based on the date property. However, I have 2 questions:

1. 返回多个文档进行查询时，DocumentDB使用的默认顺序是什么?

2. 我知道DocumentDB尚不支持排序控件，但是如果我使用存储过程来排序结果，是执行完整的集合扫描，还是数据库至少利用了属性的索引我要排序吗?有关信息，我正在使用的存储过程就是此过程(从产品团队提供的Azure示例中进行了轻微修改):

1. When returning multiple documents for a query, what is the default ordering used by DocumentDB?

2. I understand that ordering control is not yet supported by DocumentDB, but if I use a store procedure to order the results, am I executing a full collection scan, or is the database at least leveraging the index of the property I am sorting for? For information, the store procedure I am using is this one (slightly modified from the Azure samples provided by the product team):

function orderBy(filterQuery, orderByFieldName, continuationToken) { 
// HTTP error codes sent to our callback funciton by DocDB server. 
var ErrorCode = { 
    REQUEST_ENTITY_TOO_LARGE: 413, 
}
var collection = getContext().getCollection(); 
var collectionLink = collection.getSelfLink(); 
var result = new Array(); 

tryQuery({}); 

function tryQuery(options) { 
    var isAccepted = (filterQuery && filterQuery.length) ? 
        collection.queryDocuments(collectionLink, filterQuery, options, callback) : 
        collection.readDocuments(collectionLink, options, callback) 

    if (!isAccepted) throw new Error("Source dataset is too large to complete the operation."); 
} 

/** 
* queryDocuments callback. 
* @param {Error} err - Error object in case of error/exception. 
* @param {Array} queryFeed - array containing results of the query. 
* @param {ResponseOptions} responseOptions. 
*/ 
function callback(err, queryFeed, responseOptions) { 
    if (err) { 
        throw err; 
    } 

    // Iterate over document feed and store documents into the result array. 
    queryFeed.forEach(function (element, index, array) { 
        result[result.length] = element; 
    }); 

    if (responseOptions.continuation) { 
        // If there is continuation, call query again providing continuation token. 
        tryQuery({ continuation: responseOptions.continuation }); 
    } else { 
        // We are done with querying/got all results. Sort the results and return from the script. 
        result.sort(compare); 

        fillResponse(); 
    } 
} 

// Compare two objects(documents) using field specified by the orderByFieldName parameter. 
// Return 0 if equal, -1 if less, 1 if greater. 
function compare(x, y) { 
    if (x[orderByFieldName] == y[orderByFieldName]) return 0; 
    else if (x[orderByFieldName] < y[orderByFieldName]) return 1; 
    return -1; 
} 

// This is called in the very end on an already sorted array. 
// Sort the results and set the response body. 
function fillResponse() {
    var page_size = 20;
    // Main script is called with continuationToken which is the index of 1st item to start result batch from. 
    // Slice the result array and discard the beginning. From now on use the 'continuationResult' var. 
    var continuationResult = result; 
    if (continuationToken) continuationResult = result.slice(continuationToken); 
    else continuationToken = 0;

    if (page_size > continuationResult.length ) {
        page_size = continuationResult.length;
    }

    // Get/initialize the response. 
    var response = getContext().getResponse(); 
    response.setBody(null); 

    // Take care of response body getting too large: 
    // Set Response iterating by one element. When we fail due to MAX response size, return to the client requesting continuation. 
    var i = 0;
    var final_result = [];
    for (; i < page_size; ++i) { 
        try { 
            // Note: setBody is very expensive vs appendBody, use appendBody with simple approximation JSON.stringify(element).
            final_result.push(continuationResult[i]);                   
        } catch (ex) { 
            if (!ex.number == ErrorCode.REQUEST_ENTITY_TOO_LARGE) throw ex; 
            break; 
        } 
    } 

    /* Now next batch to return to client has i elements. 
    // Slice the continuationResult if needed and discard the end. */
    var partialResult = continuationResult; 
    var newContinuation = null; 
    if (i < continuationResult.length) { 
        partialResult = continuationResult.slice(0, i); 
    }

    // Finally, set response body.          
    response.setBody({ result: final_result, continuation: i }); 
} 

}

推荐答案

更新:自2015年7月起-DocumentDB在查询中支持ORDER BY

目前，您需要在客户端或存储过程上对集合进行排序(尽管我建议您在客户端上对集合进行排序，因为存储过程已经限制了执行).

At the moment, you'll need to sort the collection on the client or stored procedures (although I'd I'd recommend doing so on the client since stored procedures have bounded execution).

要回答您的问题:

1)我不相信有保证的默认订购

1) I don't believe there is a guaranteed default ordering

2)存储过程将导致收集扫描，然后对其进行排序.

2) The Stored Procedure would result in a collection scan and then sort it.

您可以在以下位置通过在Azure反馈论坛上发表意见来帮助推动排序依据": http://feedback.azure.com/forums/263030-documentdb/suggestions/6334829--按查询顺序

You can help push for "Order By" by voicing your opinion on the Azure Feedback Forums at: http://feedback.azure.com/forums/263030-documentdb/suggestions/6334829--order-by-in-queries

这篇关于DocumentDB-查询结果顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！