0
votes

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.

1
' + categoryID + ' is a string literal and won't even reference the variable. I suspect that's not all that is wrong but would need to see an example document to help more. - Larry Maccherone
Right - in your example, categoryID isn't ever used. The only way this query would work is if your _sort.category property were an array, and it contained an element " + categoryID + ". - David Makogon

1 Answers

1
votes

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