Map-Filter Step Expressions

Syntax

map_filter_step ::= (KEYS | VALUES) "(" [expression] ")"

For definition of the syntax component referenced in this statement, see:

• expression

Semantics

Like field steps, map-filter steps are meant to be used primarily with records and maps. Map-filter steps select either the field names (keys) or the field values of the map/record fields that satisfy a given condition (specified as a predicate expression inside parentheses). If the predicate expression is missing, it is assumed to be the constant true (in which case all the field names or all of the field values will be returned).

A map filter step processes each context item as follows:

• If the context item is an atomic item, it is skipped (the result is empty).

• If the context item is a record or map, the step iterates over its fields. For each field, the predicate expression is computed. In addition to the context-item variable ($), the predicate expression may reference the following two implicitly-declared variables: $key is bound to the name of the context field, i.e., the current field in $, and $value is bound to the value of the context field. The predicate expression must be BOOLEAN?. A NULL or an empty result from the predicate expression is treated as a false value. If the predicate result is true, the context field is selected and either its name or its value is returned; otherwise the context field is skipped.

• If the context item is an array, the map-filter step is applied recursively to each element of the array (with the context item being set to the current array element).

Example 1 - Map-Filter Step Expressions

For each user select their id and the expense categories in which the user spent more than $1000.

SELECT id, u.expenses.keys($value > 1000)
FROM users u

Example 2 - Map-Filter Step Expressions

For each user select their id and the expense categories in which they spent more than they spent on clothes. In this query, the context-item variable ($) appearing in the filter step expression [$value > $.clothes] refers to the context item of that filter step, i.e., to an expenses map as a whole.

SELECT id, u.expenses.keys($value > $.clothes)
FROM users u

Example 3 - Map-Filter Step Expressions

For each user select their id, the sum of their expenses in all categories except housing, and the maximum of these expenses.

SELECT id,
seq_sum(u.expenses.values($key != housing)) AS sum,
seq_max(u.expenses.values($key != housing)) AS max
FROM users u

Example 4 - Map-Filter Step Expressions

Notice that field steps are actually a special case of map-filter steps. For example the query

SELECT id, u.address.city
FROM users u

is equivalent to

SELECT id, u.address.values($key = "city")
FROM users u

However, the field step version is the preferred one, for performance reasons.