Thomas Mutzl Thomas Mutzl - 5 months ago 63
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

SELECT * FROM c WHERE ARRAY_CONTAINS(c.tags, "B")


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

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

SELECT docs
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.