1. SQL Reference Guide
  2. SQL Query Management
  3. Path Expressions
  4. Array-Filter Step Expressions

Array-Filter Step Expressions

Syntax

array_filter_step ::= "[" [expression] "]"

Semantics

An array filter is similar to a map filter, but it is meant to be used primarily for arrays. An array filter step selects elements of arrays by computing a predicate expression for each element and selecting or rejecting the element depending on the predicate result. The result of the filter step is a sequence containing all selected items. If the predicate expression is missing, it is assumed to be the constant true (in which case all the array elements will be returned).

An array filter step processes each context item as follows:
  • If the context item is not an array, an array is created and the context item is added to that array. Then the array filter is applied to this single-item array as described below.
  • If the context item is an array, the step iterates over the array elements and computes the predicate expression on each element. In addition to the context-item variable ($), the predicate expression may reference the following two implicitly-declared variables: $element is bound to the context element, i.e., the current element in $, and $pos is bound to the position of the context element within the array (positions are counted starting with 0). The predicate expression must return a boolean item, or a numeric item, or the empty sequence, or NULL. A NULL or an empty result from the predicate expression is treated as a false value. If the predicate result is true/false, the context element is selected/skipped, respectively. If the predicate result is a number P, the context element is selected only if the condition $pos = P is true. Notice that this implies that if P is negative or greater or equal to the array size, the context element is skipped.

Example 6-48 Array-Filter Step Expression

For each user, select their last name and his/her phone numbers with area code 650.

SELECT lastName,
[ u.address.phones[$element.area = 650].number ] 
AS phoneNumbers
FROM users u;

Notice the the path expression in the select clause is enclosed in square brackets, which is the syntax used for arrayconstructor expressions as described in the Array and Map Constructors section. The use of the explicit array constructor guarantees that the records in the result set will always have an array as their second field. Otherwise, the result records would contain an array for users with more than one phones, but a single integer for users with just one phone. Notice also that for users with just one phone, the phones field in address may not be an array (containing a single phone object), but just a single phone object. If such a single phone object has area code 650, its number will be selected, as expected.

Example 6-49 Array-Filter Step Expression

For each user, select their last name and phone numbers having the same area code as the first phone number of that user. 
SELECT lastName,
[ u.address.phones[$element.area = $[0].area].number ]
FROM users u;

Example 6-50 Array-Filter Step Expression

Among the 10 strongest connections of each user, select the ones with id > 100. (Recall that the connections array is assumed to be sorted by the strength of the connections, with the stronger connections appearing first). 
SELECT [ connections[$element > 100 AND $pos < 10] ] 
AS interestingConnections 
FROM users;

Example 6-51 Array-Filter Step Expression

Count the total number of phones numbers with areacode 650. 
SELECT count(u.address.phones[$element.area = 650])
FROM users u;

Example 6-52 Array-Filter Step Expression

To count the total number of people with at least one phone in the 650 areacode, a case expression (see Case Expressions) and the exists operator (see Exists Operator) must be used. 
SELECT count(CASE
WHEN EXISTS u.address.phones[$element.area = 650] THEN 1
ELSE 0
END)
FROM users u;