Passing parameter to CosmosDB stored procedure

Question

I have a Cosmos DB stored procedure in which I am passing list of comma saperated Ids. I need to pass those IDs to in query. when I'm passing one value to the parameter then its working fine but not with more that one value.

It would be great if any one could help here. below is the code of the stored procedure:

function getData(ids) {
var context = getContext();
var coll = context.getCollection();
var link = coll.getSelfLink();
var response = context.getResponse();

var query =  {query: "SELECT * FROM c where c.vin IN (@ids)", parameters: 
[{name: "@ids", value: ids}]};


var requestOptions = {
                    pageSize: 500
    };

var run = coll.queryDocuments(link, query, requestOptions, callback);

function callback(err, docs) {
    if (err) throw err;
    if (!docs || !docs.length) response.setBody(null);
    else {
          response.setBody(JSON.stringify(docs));
        }
}

if (!run) throw new Error('Unable to retrieve the requested information.');
}

Answer 1

For arrays, you should use ARRAY_CONTAINS function:

var query =  {
    query: "SELECT * FROM c where ARRAY_CONTAINS(@ids, c.vin)", 
    parameters: [{name: "@ids", value: ids}]
};

Also, it is possible that, as stated in this doc, your @ids array is being sent as string

When defining a stored procedure in Azure portal, input parameters are always sent as a string to the stored procedure. Even if you pass an array of strings as an input, the array is converted to string and sent to the stored procedure. To work around this, you can define a function within your stored procedure to parse the string as an array

So you might need to parse it before querying:

function getData(ids) {
    arr = JSON.parse(ids);
}

Related:

How can I pass array as a sql query param for cosmos DB query

https://github.com/Azure/azure-cosmosdb-node/issues/156

Answer 2

This is how you can do it:

Inside the stored procedure

1. parse your one parameter into an array using the split function
2. loop through the array and
   a) build the parameter name / value pair and push it into the parameter array used by the query later
   b) use the parameter name to build a string for use inside the parenthesis of the IN statement
3. Build the query definition and pass it to the collection.

Example

This is how the value of the parameter looks: "abc,def,ghi,jkl" If you are going to use this, replace "stringProperty" with the name of the property you are querying against.

// SAMPLE STORED PROCEDURE
function spArrayTest(arrayParameter) {
    var collection = getContext().getCollection();
    
    var stringArray = arrayParameter.split(",");
    var qParams = [];
    var qIn = "";
    for(var i=0; i<stringArray.length; i++){
        var nm = '@p'+ i; // parameter name
        qParams.push({name: nm, value: stringArray[i]});
        qIn += ( nm +','); // parameter name for query
    }
    qIn = qIn.substring(0,qIn.length-1); // remove last comma

    // qIn only contains a list of the names in qParams
    var qDef = 'SELECT * from documents d where d.stringProperty in ( ' + qIn + ' )';

    console.log(qParams[0].name);
    
    // Query Definition to be passed into "queryDocuments" function
    var q = {
        query: qDef,
        parameters: qParams
    };

    // Query documents
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        q,
    function (err, feed, options) {
        if (err) throw err;

        // Check the feed and if empty, set the body to 'no docs found', 
        // else return all documents from feed
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found with an stringProperty in ' + arrayParameter);
        }
        else {
            var response = getContext().getResponse();            
            response.setBody(feed);
        }
    });

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

Answer 3

Please refer to my sample js code, it works for me.

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

    var query = 'SELECT * FROM c where c.id IN ('+ ids   +')'
    console.log(query);

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        query,
        function (err, feed, options) {
            if (err) throw err;
            if (!feed || !feed.length) getContext().getResponse().setBody('no docs found');
            else {
                for(var i = 0;i<feed.length;i++){
                    var doc = feed[i];
                    doc.name = 'a';
                    collection.replaceDocument(doc._self,doc,function(err) {
                             if (err) throw err;
                 });
                }
                getContext().getResponse().setBody(JSON.stringify("success"));
            }
        });

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

Parameter : '1','2','3'

Hope it helps you.