javascript - 如何在 Azure Cosmos DB 中编写 JavaScript 存储过程以从 SQL API 返回数据

Question

我正在尝试创建一个 Cosmo DB 存储过程来返回相对简单的 SQL 语句的结果。如果它是纯粹的 SQL，我会很好，但由于我对 JavaScript 一无所知，所以我正在挣扎。任何帮助将非常感激。这是 SQL 查询:

SELECT distinct cx.ID, cxa.FieldValue as PartNo, cx.TransactionDate, cx.TransactionStatus
FROM c
JOIN cx in c.File.Transactions
JOIN cxa in cx.AppDetails
JOIN
(
 SELECT cx2.ID, cxa2.FieldValue as PartNo, max(cx2.TransactionDate) as TransactionDate
 FROM c
 JOIN cx2 in c.File.Transactions
 JOIN cxa2 in cx2.AppDetails
 WHERE c.File.Category= 'BatchParts' and cxa2.FieldName ='PartNo'
 GROUP BY cx2.ID,cxa2.FieldValue
) B 
WHERE c.File.Category= 'BatchParts' and cxa.FieldName ='PartNo'

Answer 1

你可以尝试这样的事情:

function getItems(category,fieldName) {
    var collection = getContext().getCollection();

    var query = 'SELECT distinct cx.ID, cxa.FieldValue as PartNo, cx.TransactionDate, cx.TransactionStatus ' +
                'FROM c ' +
                'JOIN cx in c.File.Transactions ' +
                'JOIN cxa in cx.AppDetails ' +
                'JOIN ' +
                '( ' +
                    'SELECT cx2.ID, cxa2.FieldValue as PartNo, max(cx2.TransactionDate) as TransactionDate ' +
                    'FROM c ' +
                    'JOIN cx2 in c.File.Transactions ' +
                    'JOIN cxa2 in cx2.AppDetails ' +
                    'WHERE c.File.Category= @Category and cxa2.FieldName = @FieldName ' +
                    'GROUP BY cx2.ID,cxa2.FieldValue ' +
                ') B ' + 
                'WHERE c.File.Category= @Category  and cxa.FieldName = @FieldName';

    var filterQuery =
    {
        'query' : query,
        'parameters' : [{'name':'@Category', 'value':category},{'name':'@FieldName', 'value':fieldName}] 
    };

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

        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var body = feed;
            response.setBody(JSON.stringify(body));
        }
    });

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

顺便说一句，当您调用存储过程时，您需要传递分区键值。并且只能获取该分区的数据。可以引用这个doc和 this .