Working With Maps
The path steps applicable to maps are field and filter steps. Slice steps do not make sense for maps, because maps are unordered, and as a result, their entries do not have any fixed positions.
You can use a field step to select the value of a field from a map. For example, to return the lastname and the food expenses of all persons:
sql-> SELECT lastname, p.expenses.food
FROM Persons p;
+----------+------+
| lastname | food |
+----------+------+
| Morgan | 2000 |
| Morrison | 1000 |
| Scully | 900 |
| Smith | 6000 |
| Anderson | 1700 |
+----------+------+
5 rows returned In the above query, the path expression p.expenses.food consists of 2 field steps: .expenses selects the expenses field of the current row and .food selects the value of the food field/entry from the current expenses map.
To return the lastname and amount spent on travel for each person who spent less than $3000 on food:
sql-> SELECT lastname, p.expenses.travel
FROM Persons p WHERE p.expenses.food < 3000;
+----------+--------+
| lastname | travel |
+----------+--------+
| Scully | NULL |
| Morgan | 700 |
| Anderson | 2100 |
| Morrison | NULL |
+----------+--------+
4 rows returned Notice that NULL is returned for persons who did not have any travel expenses.
Filter steps are performed using either the .values() or .keys() path steps. To select values of map entries, use .values(<cond>). To select keys of map entries, use .keys(<cond>). If no condition is used in these steps, all the values or keys of the input map are selected. If the steps do contain a condition expression, the condition is evaluated for each entry, and the value or key of the entry is selected/skipped if the result is true/false.
The implicitly-declared variables $key and $value can be used inside a map filter condition. $key references the key of the current entry and $value references the associated value. Notice that, contrary to arrays, the $pos variable can not be be used inside map filters (because map entries do not have fixed positions).
To show, for each user, their id and the expense categories where they spent more than $1000:
sql-> SELECT id, p.expenses.keys($value > 1000) as Expenses
from Persons p;
+----+---------------------+
| id | Expenses |
+----+---------------------+
| 4 | clothes |
| | food |
| | shoes |
+----+---------------------+
| 3 | food |
+----+---------------------+
| 2 | food |
| | travel |
+----+---------------------+
| 5 | clothes |
+----+---------------------+
| 1 | NULL |
+----+---------------------+ To return the id and the expense categories in which the user spent more than they spent on clothes, use the following filter step expression. In this query, the context-item variable ($) appearing in the filter step expression [$value > $.clothes] refers to the expenses map as a whole.
sql-> SELECT id, p.expenses.keys($value > $.clothes) FROM Persons p;
+----+---------------------+
| id | Column_2 |
+----+---------------------+
| 3 | NULL |
+----+---------------------+
| 2 | NULL |
+----+---------------------+
| 5 | NULL |
+----+---------------------+
| 1 | NULL |
+----+---------------------+
| 4 | food |
+----+---------------------+ To return the id and expenses data of any person who spent more on any category than what they spent on food:
sql-> SELECT id, p.expenses
FROM Persons p
WHERE p.expenses.values() >any p.expenses.food;
+----+---------------------+
| id | expenses |
+----+---------------------+
| 5 | clothes | 1500 |
| | food | 900 |
| | shoes | 1000 |
+----+---------------------+
| 2 | books | 100 |
| | food | 1700 |
| | travel | 2100 |
+----+---------------------+
2 rows returned To return the id of all persons who consumed more than $2000 in any category other than food:
sql-> SELECT id FROM Persons p
WHERE p.expenses.values($key != "food") >any 2000;
+----+
| id |
+----+
| 2 |
+----+
1 row returned