将参数传递给CosmosDB存储过程 [英] Passing parameter to CosmosDB stored procedure

问题描述

我有一个Cosmos DB存储过程，其中传递了逗号分隔的ID列表.我需要将这些ID传递给查询.当我将一个值传递给参数时，它可以正常工作，但不能超过一个值.

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

推荐答案

对于数组，应使用ARRAY_CONTAINS函数:

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

在Azure门户中定义存储过程时，输入参数始终作为字符串发送到存储过程.即使您将字符串数组作为输入传递，该数组也将转换为字符串并发送到存储过程.要解决此问题，您可以在存储过程中定义一个函数，以将字符串解析为数组

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

相关:

如何我可以将数组作为cosmos DB查询的sql查询参数传递

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

这篇关于将参数传递给CosmosDB存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！