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