DocumentDB 'all/any' functionality

0
votes

Imagine I have the data in Cosmos

[
    {
        "id": "FCEC01CD-A6E9-4DEA-8DD5-89711B5B05A1",
        "sub": [
            {
                "id": 1,
                "v": false
            },
            {
                "id": 2,
                "v": false
            }
        ]
]

and I want to query for all id's that have all (sibbeling) 'sub' items having v=false what query syntax would work? (ARRAY_CONTAINS would not work, since that gives an 'any' result)

Thanks!

1
azure-cosmosdb

1 Answers

1
votes

You need a user-defined function for this. 

function arrayAllMatch(arr) {
  for(i=0; i < arr.length; i++) {
    if (arr[i].v === true) {
      return false;
    }
  }

  return true;
}

Then call within query (also include ARRAY_CONTAINS because it can use the index to reduce the number of calls to the UDF):

SELECT * 
FROM c 
WHERE ARRAY_CONTAINS(c.sub, {"v" : false }, true) 
AND udf.arrayAllMatch(c.sub)