map_filter_step ::= (KEYS | VALUES) "(" [
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).
- 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 6-28 Map-Filter Step Expressions
SELECT id, u.expenses.keys($value > 1000) FROM users u;
Example 6-29 Map-Filter Step Expressions
SELECT id, u.expenses.keys($value > $.clothes) FROM users u;
Example 6-30 Map-Filter Step Expressions
SELECT id, seq_sum(u.expenses.values($key != housing)) AS sum, seq_max(u.expenses.values($key != housing)) AS max FROM users u;
Example 6-31 Map-Filter Step Expressions
is equivalent to
SELECT id, u.address.city FROM users u;
However, the field step version is the preferred one, for performance reasons.
SELECT id, u.address.values($key = "city") FROM users u;