Working with Records

You can use a field step to select the value of a field from a record. For example, to return the id, last name, and city of persons who reside in Florida:

sql-> SELECT id, lastname, p.address.city
FROM Persons p WHERE p.address.state = "FL";
 +----+----------+------------+
 | id | lastname |    city    |
 +----+----------+------------+
 |  3 | Morgan   | Middleburg |
 +----+----------+------------+

1 row returned 

In the above query, the path expression (see Path Expressions) p.address.state consists of 2 field steps: .address selects the address field of the current row (rows can be viewed as records, whose fields are the row columns), and .state selects the state field of the current address.

The example record contains an array of phone numbers. You can form queries against that array using a combination of path steps and sequence comparison operators (see Sequence Comparison Operators). For example, to return the last name of persons who have a phone number with area code 423:

sql-> SELECT lastname FROM Persons
p WHERE p.address.phones.areacode =any 423;
 +----------+
 | lastname |
 +----------+
 | Morrison |
 +----------+

1 row returned 

In the above query, the path expression p.address.phones.areacode returns all the area codes of a person. Then, the =any operator returns true if this sequence of area codes contains the number 423. Notice also that the field step .areacode is applied to an array field (phones). This is allowed if the array contains records or maps. In this case, the field step is applied to each element of the array in turn.

The following example returns all the persons who had three connections. Notice the use of [] after connections: it is an array filter step, which returns all the elements of the connections array as a sequence (it is unnesting the array).

sql-> SELECT id, firstName, lastName, connections from Persons where 
connections[] =any 3 ORDER BY id;
 +----+-----------+----------+-------------+
 | id | firstName | lastName | connections |
 +----+-----------+----------+-------------+
 |  1 | David     | Morrison | 2           |
 |    |           |          | 3           |
 +----+-----------+----------+-------------+
 |  2 | John      | Anderson | 1           |
 |    |           |          | 3           |
 +----+-----------+----------+-------------+
 |  4 | Peter     | Smith    | 3           |
 |    |           |          | 5           |
 |    |           |          | 1           |
 |    |           |          | 2           |
 +----+-----------+----------+-------------+
 |  5 | Dana      | Scully   | 2           |
 |    |           |          | 4           |
 |    |           |          | 1           |
 |    |           |          | 3           |
 +----+-----------+----------+-------------+

4 rows returned 

This query can use ORDER BY to sort the results because the sort is being performed on the table's primary key. The next section shows sorting on non-primary key fields through the use of indexes.

See Working With Arrays for more examples of querying against data contained in arrays.