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 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. 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.
For more examples of querying against data contained in arrays, see Working With Arrays.