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

Question

I want to pass array as a param to SqlQuerySpec to be able to use it in the IN expression when building query for azure cosmos db. What i'm trying to do is something like we do with regular (string, int etc) params:

private SqlQuerySpec BuildQuery(IEnumerable<string> exclTypes)
{
    var queryText = "SELECT * FROM root r WHERE r.Type NOT IN (@types)";
    var parameters = new SqlParameterCollection{new SqlParameter("@types", exclTypes.ToArray())};
    return new SqlQuerySpec()
    {QueryText = queryText, Parameters = parameters};
}

But that doesn't work in such way. Any other ways I can pass array as a param? Thanks.

Answer 1

Your query should look something like this:

SELECT * FROM root r WHERE ARRAY_CONTAINS(@types, r.Type) <> true

then you can pass @types as array and check if that array contains value you have in property r.Type in your document.

refs:

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query-reference#bk_array_contains https://github.com/Azure/azure-documentdb-node/issues/156