Collection Operators

Collection Operators

Collection operators enable you to evaluate expressions over collections or objects. The operators include ANY, EVERY, ARRAY, FIRST, EXISTS, IN and WITHIN.

exists-expression | in-expression | within-expression | ANY | EVERY | ARRAY | FIRST

ANY

 ANY variable ( IN  | WITHIN ) expression 
     [  ,  variable ( IN | WITHIN ) expression  ]* 
     SATISFIES condition  END 

ANY is a range predicate that tests a Boolean condition over the elements or attributes of a collection, object, or objects. It uses the IN and WITHIN operators to range through the collection.

If at least one item in the array satisfies the ANY expression, then it returns TRUE. Otherwise, it returns FALSE.

The following query retrieves all contacts who have one or more children over the age of 14. In this example, the query returns one item for 'dave' because one of his children is 17.

Query:
SELECT name
  FROM contacts
  WHERE ANY child IN children
  SATISFIES child.age > 14 END    
Result:
"results": [ { "name": "dave" }, .... ]

Sometimes the conditions you want to filter need to be applied to the arrays nested inside the document. The SATISFIES keyword is used to specify the filter condition. The expression after the ANY clause allows us to assign an identifier to an element in the array that we are searching through.

Query:
 SELECT fname, children
    FROM tutorial 
        WHERE ANY child IN tutorial.children SATISFIES child.age > 10  END
Result:
{"results":[
  {"children":[{"age":17,"fname":"Aiden","gender":"m"},{"age":2,"fname":"Bill","gender":"f"}],"fname":"Dave"},
  {"children":[{"age":17,"fname":"Xena","gender":"f"},{"age":2,"fname":"Yuri","gender":"m"}],"fname":"Earl"},
  {"children":[{"age":17,"fname":"Abama","gender":"m"},{"age":21,"fname":"Bebama","gender":"m"}],"fname":"Ian"}
]}
Note: The SOME and ANY operators perform the same function and can be used interchangeably.

EVERY

EVERY variable ( IN  | WITHIN ) expression 
   [  ,  variable ( IN | WITHIN ) expression  ]* 
   SATISFIES condition  END   

EVERY is a range predicate that tests a Boolean condition over the elements or attributes of a collection, object, or objects. It uses the IN and WITHIN operators to range through the collection.

If every array element satisfies the EVERY expression, it returns TRUE. Otherwise, it returns FALSE. If the array is empty, it returns TRUE.

The EVERY operator returns TRUE if all items meet the condition.

For example, the following query is almost identical to the preceding one except EVERY is used instead of ANY. This query scans all contacts and returns the name of any contact that has children over the age of 10. The result tells us that out of all of the contacts only 'ian' has children who are all over the age 10.

Query:
 SELECT name
    FROM contacts
    WHERE EVERY child IN children
    SATISFIES child.age > 10 END
Result:
  { "name": "ian" }

Sometimes the conditions you want to filter need to be applied to the arrays nested inside the document. The SATISFIES keyword is used to specify the filter condition. The expression after the EVERY clause allows us to assign a name to an element in the array that we are searching through.

Query:
 SELECT fname, children
    FROM tutorial 
        WHERE EVERY child 
        IN tutorial.children 
        SATISFIES child.age > 10  END    
Result:
{"results":[
   {"children":[{"age":17,"fname":"Aiden","gender":"m"},{"age":2,"fname":"Bill","gender":"f"}],"fname":"Dave"},
   {"children":[{"age":17,"fname":"Xena","gender":"f"},{"age":2,"fname":"Yuri","gender":"m"}],"fname":"Earl"},
   {"children":[{"age":17,"fname":"Abama","gender":"m"},{"age":21,"fname":"Bebama","gender":"m"}],"fname":"Ian"}]}

ARRAY

array-expression:

ARRAY expression FOR variable ( IN |  WITHIN ) expression
   [ ,  variable ( IN | WITHIN ) expression ]* [ ( WHEN  condition) ] END

The ARRAY operator lets you map and filter the elements or attributes of a collection, object, or objects. It evaluates to an array of the operand expression, that satisfies the WHEN clause, if provided.

FIRST

first-expression:

FIRST expression FOR variable ( IN |  WITHIN ) 
  expression [ ,  variable ( IN | WITHIN ) expression]* 
  [ ( WHEN  condition) ] END

The FIRST operator enables you to map and filter the elements or attributes of a collection, object, or objects. It evaluates to a single element based on the operand expression that satisfies the WHEN clause, if provided.

EXISTS

exists-expression:

EXISTS expression

The EXISTS operator evaluates to TRUE if the value is an array and contains at least one element.

IN

in-expression:

expression [ NOT ] IN expression

The IN operator evaluates to TRUE if the right-side value is an array and directly contains the left-side value. The NOT IN operator evaluates to TRUE if the right-side value is an array and does not directly contain the left-side value.

WITHIN

within-expression:

expression [NOT] WITHIN expression

The WITHIN operator evaluates to TRUE if the right-side value contains the left-side value as a child or descendant. The NOT WITHIN operator evaluates to TRUE if the right-side value does not contain the left-side value as a child or descendant.