How is it possible to make the following query with DocumentDB:
var categoryID = '1,2,3,4,5';
SELECT * FROM products doc WHERE ARRAY_CONTAINS(doc._sort.category, ' + categoryID + ');
This clearly does not work.
You need something like
SELECT * FROM product WHERE ARRAY_CONTAINS([1,2,3,4,5], product.category)
Which can also be parameterized:
SELECT * FROM product WHERE ARRAY_CONTAINS(@categoryIDs, product.category)
Where @categoryIDs = [1,2,3,4,5].
In .NET, here's a snippet:
Uri collectionUri = UriFactory.CreateDocumentCollectionUri("testdb", "testcollsingle");
int[] array = new int[] { 1, 2, 3 };
SqlQuerySpec query = new SqlQuerySpec(
"SELECT VALUE ARRAY_CONTAINS(@categoryIds, 1)",
new SqlParameterCollection(new SqlParameter[] {
new SqlParameter { Name = "@categoryIds", Value = array
}}));
bool result = client.CreateDocumentQuery<bool>(collectionUri, query)
.AsEnumerable()
.FirstOrDefault();
Details here: https://msdn.microsoft.com/library/azure/dn782250.aspx#bk_array_contains
categoryIDisn't ever used. The only way this query would work is if your_sort.categoryproperty were an array, and it contained an element " + categoryID + ". - David Makogon