1. SQL Reference Guide
  2. SQL Query Management
  3. Path Expressions
  4. Field Step Expressions

Field Step Expressions

Syntax

map_field_step ::= 
   id | string | variable_reference | 
   parenthesized_expression | function_call

Semantics

The main use of a field step is to select the value of a field from a record or map. The field to select is specified by its field name, which is either given explicitly as an identifier, or is computed by a name expression. The name expression, must have type STRING?.

A field step processes each context item as follows:
  • If the context item is an atomic item, it is skipped (the result is empty).
  • The name expression is computed. The name expression may reference the context item via the $ variable. If the name expression returns the empty sequence or NULL, the context item is skipped. Otherwise, let K be the result of of the name expression (if an identifier is used instead of a name expression, K is the string with the same characters as the identifier).
  • If the context item is a record, then if that record contains a field whose name is equal to K, the value of that field is returned, otherwise, an error is raised.
  • If the context item is a map, then if that map contains a field whose name is equal to K, the value of that field is returned, otherwise, an empty result is returned.
  • If the content item is an array, the field step is applied recursively to each element of the array (with the context item being set to the current array element).

Example 6-38 Field Step Expression

Select the id and the city of all users.

SELECT id, u.address.city
FROM users u;

Notice that if the input to a path expressions is a table column, a table alias must be used together with the column name. Otherwise, as explained in the Variable References section, an expression like address.city would be interpreted as a reference to the city column of a table called address, which is of course not correct.

Recall that address is a column of type JSON. For most (if not all) users, its value will be a json document, i.e.. a map containing other json values. If it is a document and it has a field called city, its value will be returned. For address documents with no city field, the path expression u.address.city returns the empty sequence, which gets converted to NULL by the SELECT clause. The same is true for addresses that are atomic values (e.g. flat strings). Finally, a user may have many addresses stored as an array in the address column. For such a user, all of his/her cities will be returned inside an array.

The record items constructed and returned by the above query will all have type RECORD(id INTEGER, city JSON). The city field of this record type has type JSON, because the address column has type JSON and as a result, any nested field in an address can have any valid JSON value. However, each actual record value in the result will have a city field whose field value has a more specific type (most likely STRING).

Note:

The query processor could be constructing on-the-fly a precise RECORD type for each individual record constructed by the query, but it does not do so for performance reasons. Instead it constructs a common type for all returned record items.

Example 6-39 Field Step Expression

Select the id and amount spent on books for all users who live in California.

SELECT id, u.expenses.books
FROM users u
WHERE u.address.state = "CA";

In this case, "expenses" is a "typed" map: all of its values have INTEGER as their type. As a result, the record items constructed and returned by the above query will all have type RECORD(id INTEGER, books INTEGER).

Example 6-40 Field Step Expression

For each user, select their id and a field from his/her address. The field to select is specified via an external variable.

DECLARE $fieldName STRING;

SELECT u.id, u.address.$fieldName
FROM users u;

Example 6-41 Field Step Expression

For each user select all their last names. In this query the otherName column is an array, and the .last step is applied to each element of the array.

SELECT lastName, u.otherNames.last
FROM users u;

Example 6-42 Field Step Expression

For each user select their id and all of their phone numbers (without the area code). This query will work as expected independently of whether phones is an array of phone objects or a single such phone object. However, if phones is, for example, a single integer or a json object without a number field, the path expression will return the empty sequence, which will be converted to NULL by the SELECT clause.

SELECT id, u.address.phones.number
FROM users u;

Example 6-43 Field Step Expression

For each state, find how much people in that state spent on books.

SELECT u.address.state, sum(u.expenses.books)
FROM users u
GROUP BY u.address.state;

For the above query to work, an index must exist whose first field is u.address.state.