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.

tlt · Accepted Answer · 2017-12-28 08:07:48Z

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

answered Dec 28, 2017 at 8:07

tlt

15.5k11 gold badges54 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3841460 Over a year ago

First link is dead. Try this : learn.microsoft.com/en-us/azure/cosmos-db/sql/…

SoronelHaetir · Accepted Answer · 2017-12-28 07:53:51Z

-4

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.

answered Dec 28, 2017 at 7:53

SoronelHaetir

15.6k1 gold badge18 silver badges26 bronze badges

2 Comments

Olha Shumeliuk Over a year ago

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 ?

Imre Pühvel Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related