Stored Procedure Example

Here is an example of an aggregate query and the corresponding stored procedure that was created for it.

The aggregate query:

var query = container.CreateAggregateDocumentQuery<Sale>(queryOptions)
    .Where(x => x.Time > new DateTime(2012, 02, 01))
    .GroupBy(x => x.StoreId)
    .Aggregate(
        first => new SaleStoreAggregate
        {
            Count = 1,
            StoreId = first.StoreId,
            TotalAmount = first.Amount,
            AverageAmount = first.Amount,
        },
        (aggregate, current) => new SaleStoreAggregate
        {
            Count = aggregate.Count + 1,
            StoreId = aggregate.StoreId,
            TotalAmount = aggregate.TotalAmount + current.Amount,
            AverageAmount = (aggregate.TotalAmount + current.Amount) / (aggregate.Count + 1),
        })
    .Select(aggregate => new SaleStoreSummary
    {
        StoreId = aggregate.StoreId,
        TotalAmount = aggregate.TotalAmount,
        AverageAmount = aggregate.AverageAmount,
    })
    .AsDocumentQuery();

The stored procedure:

// ***
// *** DO NOT MODIFY THIS STORED PROCEDURE
// ***
// This stored procedure was automatically created by the CodeTiger.Azure.Cosmos library to facilitate queries
// that aggregate data across multiple documents. It can safely be deleted, and will automatically be re-created if
// it is needed in the future, but any changes made to this stored procedure could cause incorrect behavior that
// will NOT automatically be repaired.

function query(queryState) {
    var collection = getContext().getCollection();
    var query = {
        query: 'SELECT * FROM root r WHERE r.time > @p1 ORDER BY r.storeId',
        parameters: queryState.parameters
    };

    runQuery(queryState);

    function runQuery(queryState) {
        var requestOptions = { pageSize: -1, continuation: queryState.continuationToken };

        var isAccepted = collection.queryDocuments(
            collection.getSelfLink(), query, requestOptions,
            function (err, feed, options) {
                if (err) {
                    throw err;
                }

                if (feed) {
                    for (var i = 0; i < feed.length; i++) {
                        var current = feed[i];

                        if (queryState.partialAggregate
                            && shouldStartNewAggregate(queryState.previousSourceDocument, current)) {
                            if (!queryState.results) {
                                queryState.results = [];
                            }
                            queryState.results.push(createResult(queryState.partialAggregate));
                            queryState.partialAggregate = null;
                            queryState.returnedResultCount += 1;
                        }

                        if (!queryState.partialAggregate) {
                            queryState.partialAggregate = createAggregateSeed(current);
                        } else {
                            queryState.partialAggregate = createAggregate(queryState.partialAggregate, current);
                        }

                        queryState.previousSourceDocument = current;
                    }
                }

                if (options.continuation
                    && (!queryState.maxResultCount
                        || queryState.returnedResultCount < queryState.maxResultCount)) {
                    queryState.continuationToken = options.continuation;
                    runQuery(queryState);
                } else {
                    queryState.continuationToken = null;
                    if (queryState.partialAggregate) {
                        if (!queryState.results) {
                            queryState.results = [];
                        }
                        queryState.results.push(createResult(queryState.partialAggregate));
                        queryState.partialAggregate = null;
                        queryState.returnedResultCount += 1;
                    }
                    getContext().getResponse().setBody(queryState);
                }
            });

        if (!isAccepted) {
            if (queryState.continuationToken) {
                // The query was likely not accepted because of time or RU limitations. Return the continuation
                // token so the caller can attempt to continue the query.
                getContext().getResponse().setBody(queryState);
            } else {
                throw new Error('The query was not accepted (queryState = ' + JSON.stringify(queryState) + ').');
            }
        }
    }
}

function createAggregateSeed(first) {
    return { "count": 1, "storeId": first.storeId, "averageAmount": first.amount, "totalAmount": first.amount };
}

function createAggregate(aggregate, current) {
    return { "count": (aggregate.count + 1), "storeId": aggregate.storeId, "averageAmount": ((aggregate.totalAmount + current.amount) / (aggregate.count + 1)), "totalAmount": (aggregate.totalAmount + current.amount) };
}

function shouldStartNewAggregate(previous, current) {
    if (!previous) {
        return false;
    }
    return current.storeId != previous.storeId;
}

function createResult(aggregate) {
    return { "storeId": aggregate.storeId, "averageAmount": aggregate.averageAmount, "totalAmount": aggregate.totalAmount };
}