Using the size() Function
The size function can be used to return the size (number of fields/entries) of a complex item (record, array, or map). For example:
To return the id and the number of phones that each person has:
sql-> SELECT id, size(p.address.phones)
AS registeredphones FROM Persons p;
+----+------------------+
| id | registeredphones |
+----+------------------+
| 5 | 3 |
| 3 | 2 |
| 4 | 4 |
| 2 | 1 |
| 1 | 1 |
+----+------------------+
5 rows returned
To return the id and the number of expenses categories for each person: has:
sql-> SELECT id, size(p.expenses) AS
categories FROM Persons p;
+----+------------+
| id | categories |
+----+------------+
| 4 | 4 |
| 3 | 3 |
| 2 | 3 |
| 1 | 2 |
| 5 | 3 |
+----+------------+
5 rows returned
To return for each person their id and the number of expenses categories for which the expenses were more than 2000:
sql-> SELECT id, size([p.expenses.values($value > 2000)]) AS
expensiveCategories FROM Persons p;
+----+---------------------+
| id | expensiveCategories |
+----+---------------------+
| 3 | 0 |
| 2 | 1 |
| 5 | 0 |
| 1 | 0 |
| 4 | 1 |
+----+---------------------+
5 rows returned