How to write a JavaScript stored procedure in Azure Cosmo DB to return data from SQL API

I’m trying to create a Cosmo DB stored procedure to return the results of a relatively simple SQL statement. If it were purely SQL I would be fine but since I know nothing about JavaScript I’m struggling mightily. Any help would be very much appreciated. Here is the SQL 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= 'BatchParts' and cxa2.FieldName ='PartNo'
 GROUP BY cx2.ID,cxa2.FieldValue
) B 
WHERE c.File.Category= 'BatchParts' and cxa.FieldName ='PartNo'

Answer

You can try something like this:

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.');
}

By the way, when you invoke stored procedure, you need to pass partition key value. And you can only get the data from this partition. You can refer to this doc and this.