Azure Cosmos DB SQL API stored procedure example

Question

I am new to Azure Cosmos DB and I'm trying to create a stored procedure using SQLAPI to return data.

Using the following as an example:

{ 
  "id":"123",
  "fname" : "John",
  "lname" : "Doe",
  "receivedTime" : "08/08/2019 09:54:57",
  "subjects" : [
{
   "subjectid" : "01",
   "subjectname" : "English"
   "subjectmark" : "80"
},
{
   "subjectid" : "02",
   "subjectname" : "Math"
   "subjectmark" : "70"
}
]
}

How would I build a stored procedure to return the root data as well as the average of the subjectmarks?

Is there a reason you want a stored procedure in particular? This could probably be done with a query directly. — Noah Stahl
I would like to call the procedure as and when needed and may have the proc registered and exposed to some external interfaces. I'm also trying to get familiar with the syntax. — Rishab Goonoa
Please edit your question to show what you've done so far, and where you're stuck (output issues, errors, etc). Also, please include an example of what your expected output would look like. I'm not sure what you mean by "the root data" - this would be a good thing to explain. — David Makogon

Steve Zhao Steve Zhao · Accepted Answer · 2020-08-31T06:25:01

Please try this：

function sample() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
    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();
            for(var x in feed){
                var totalmark = 0;
                var avargemark = 0;
                for(var y in feed[x].subjects){
                    totalmark += parseInt(feed[x].subjects[y].subjectmark);
                }
                avargemark = parseFloat(totalmark)/feed[x].subjects.length;
                feed[x].avargemark = avargemark;
            }

            var body = feed;
            response.setBody(JSON.stringify(body));
        }
    });

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

By the way,stored procedures are always scoped to a partition key.You need to provide a partition value to execute this.

Azure Cosmos DB SQL API stored procedure example

1 Answers