3

Is possible to use IN statement in DocumentDB query? Like:

SELECT * FROM  c  where c.FormId in(1,2,3,4,5,6,7,8,9,10)

I don't want use OR because there are limits (only 6)

where (c.FormId =1 or c.FormId= 2 or c.FormId =3
Or c.FormId = 4  Or c.FormId=5 Or c.FormId=6)
David Makogon
  • 69,407
  • 21
  • 141
  • 189

1 Answers1

2

EDIT We just announced support for the IN (and other) keyword in queries http://azure.microsoft.com/blog/2015/05/06/new-documentdb-sql-keywords-operators-and-functions/

Today; there are two ways you could approach this

1) Write a UDF to do this for you and use it in your select

2) Or, you could make separate queries per id E.g. where FormId = 1 and another query where FormId = 2 etc.

I know this involves multiple server roundtrips but depending on where your app resides in relation to the DocumentDB service it probably won't be that bad to execute multiple queries in parallel.

When issuing multiple queries with single predicates like this allows the query to be super efficient because it uses the hash index on your properties which are there by default.

Using a UDF in the WHERE portion of a SELECT will result in a scan being performed which could result in sub optimal performance and higher query charges.

PS. if you would like to see support added natively for the IN operator, please go vote for it on http://feedback.azure.com/forums/263030-documentdb/suggestions/6395357-in-operator

Ryan CrawCour
  • 2,704
  • 1
  • 19
  • 25