14

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.

Souvik Ghosh
  • 4,456
  • 13
  • 56
  • 78
Olha Shumeliuk
  • 720
  • 7
  • 14

2 Answers2

21

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://learn.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query-reference#bk_array_contains https://github.com/Azure/azure-documentdb-node/issues/156

dee zg
  • 13,793
  • 10
  • 42
  • 82
-3

The easiest way to do this tisto set up a table-valued parameter. Your array would be passed as a TVP and since it is then a table it can be used as part of an IN predicate. There's lots of material online about that.

SoronelHaetir
  • 14,104
  • 1
  • 12
  • 23
  • Thanks, but not sure I understand what u talking about. I'm not using cosmos table storage, but documents storage instead. Can you please write a small example or give a link to tutorial where I can find one ? – Olha Shumeliuk Dec 28 '17 at 08:05
  • Correct me if I'm wrong but cosmosDB SQL does not have a TVP concept. So this answer is just not applicable to this question and should be deleted. – Imre Pühvel Jun 08 '18 at 12:39