Thomas Mutzl Thomas Mutzl -5 years ago 274
SQL Question

DocumentDB queries with arrays

I have documents with a simple (string-)array property.

"id": "one",
"tags": ["A", "B"]

"id": "two",
"tags": ["A", "C"]

To check, if a value is part of an array, I could use ARRAY_CONTAINS


will return document "one".

How could I query for documents with a list of possible values in the array?

Return all Documents where at least one value of the tags array are IN("B", "C").

-> documents "one" and "two"

Answer Source

You can combine the JOIN operator , which is used to form cross products with nested array elements, with the IN operator.

FROM docs
JOIN tags IN docs.tags
WHERE tags IN ("B", "C")

Note that because you are creating a cross product, that you will get a result for each matching child element, rather than for each document.

Alternatively you could combine several ARRAY_CONTAINS with OR operators, or write a UDF.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download