Chapter 9. Expressions

Table of Contents

Path Expressions
Field Step Expressions
Map Filter Step Expressions
Array Filter Step Expressions
Array Slice Step Expressions
Constant Expressions
Column Reference Expression
Variable Reference Expression
Searched Case Expressions
Cast Expressions

In general, an expression represents a set of operations to be executed in order to produce a result. Expressions are built by combining other subexpressions using operators (arithmetic, logical, value and sequence comparisons), function calls, or other grammatical constructs. The simplest kinds of expressions are constants and references to variables or identifiers.

In SQL for Oracle NoSQL Database, the result of any expression is always a sequence of zero or more items. Notice that a single item is considered equivalent to a sequence containing that single item.

Note

The examples shown in this chapter rely on the sample data shown in Example Data.

Path Expressions

To navigate inside complex values and select their nested values, SQL for Oracle NoSQL Database supports path expressions. A path expression has an input expression followed by one or more steps.

<primary_expressions>.<step>*

Note

A path expression over a table row must always start with the table's name or the table's alias (if one was included in the FROM clause).

There are three kinds of path expression steps: field, filter, and slice steps. Field steps are used to select field/entry values from records or maps. Filter steps are used to select array or map entries that satisfy some condition. Slice steps are used to select array entries based on their position inside the containing array. A path expression can mix different kinds of steps.

All steps iterate over their input sequence, producing zero or more items for each input item. If the input sequence is empty, the result of the step is also empty. Otherwise, the overall result of the step is the concatenation of the results produced for each input item. The input item that a step is currently operating on is called the context item, and it is available within the step expression using the dollar sign ($) variable. This context-item variable exists in the scope created by the step expression.

For all steps, if the context item is NULL, it is just added into the output sequence with no further processing.

In general, path expressions may return more than one item as their result. Such multi-item results can be used as input in two other kinds of expressions: sequence-comparison operators and array constructors.

Field Step Expressions

A field step selects 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 be of type string.

<primary_expression>.<id> | <string> | <var_ref> | 
<parenthesized_expr> | <func_call>* 

As a simple example, the field step expression u.address.city:

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

Retrieves the field "city" from the "address" column in the Users ("u") table.

A field step processes each context item as follows:

  1. If the context item is an atomic item, it is skipped (the result is empty).

  2. The name expression is evaluated. If the name expression returns the empty sequence or NULL, the context item is skipped. Otherwise, the evaluated name expression is passed to the next step.

  3. If the context item:

    • Is a record

      and if that record contains a field identical to the evaluated name expression, then that field is returned. Otherwise, an error is raised.

    • Is a map

      and if that map contains a field identical to the evaluated name expression, then that field is returned. Otherwise, an empty result is returned.

If the context item ($) is an array, then the field step is applied to each element of the array with the context item being set to the current array element. If the context item is an atomic item, it is skipped (the result is empty).

Map Filter Step Expressions

A map filter step is used with records and maps to select either the field name (keys) or the field values of the fields that satisfy a given condition. This condition is specified as a predicate expression inside parentheses. If the predicate expression is missing, it is assumed to be true — all the field names or values are returned.

<primary_expression>.keys | values (<predicate>) 

where keys references the record's or map's field name, and values references the record's or map's field values.

In addition to the context-item variable ($), the predicate expression may reference the following two variables: $key is bound to the name of the context field — that is, the current field in $, and $value is bound to the value of the context field. The predicate expression must be boolean.

A simple example is u.expenses.keys($value > 1000), which selects all the expenses greater than $1000. Combined with this query:

SELECT id, u.expenses.keys($value > 1000) FROM Users u; 

all the user IDs and expense fields are returned where more than 1000 was spent.

A map filter step processes each context item as follows:

  1. If the context item is an atomic item, it is skipped (the result is empty).

  2. If the context item is a record or map, the step iterates over its fields. For each field, the predicate expression is evaluated. A NULL or an empty result from the predicate expression is treated as a false value. If the predicate result is true, the context field is selected and either its name or its value is returned; otherwise the context field is skipped.

Note

If the context item ($) is an array, then the map filter step is applied to each element of the array with the context item being set to the current array element.

Array Filter Step Expressions

An array filter step is used with arrays to select elements of arrays by evaluating a predicate expression for each element. Elements are selected or rejected depending on the results of the predicate expression. If the predicate expression is missing, it is assumed to be true — all the array elements are returned.

[<primary_expression>[<predicate_expression>]] 

Notice in the syntax that the entire expression is enclosed in square brackets ([]). This is the array constructor. Use of the array constructor is frequently required in order to obtain the desired result, and so we show it here. The use of the explicit array constructor guarantees that the records in the result set will always have an array as their second field. For example:

SELECT lastName,
[ u.address.phones[$element.area = 650].number ] AS phoneNumbers
FROM Users u; 

Assume that u.address.phones references one or more phone numbers. Without the array constructor, the result records would contain an array for users with more than one phone (because the information would be held in an array in the store anyway), but just a single integer for users with just one phone. For users with just one phone, the phones field might not be an array (containing a single phone object), but just a single phone object. If such a single phone object has area code 650, its number will be selected, as expected.

In addition to the context-item variable ($), the predicate expression may reference the following two variables: $element is bound to the current element in $, and $pos is bound to the position of the context element within $. Positions are counted starting with 0.

An array filter step processes each context item as follows:

  1. If the context item is not an array, an array is created and the context item is added to that array. Then the array filter is applied to this single-item array.

  2. If the context item is an array, the step iterates over the array elements and computes the predicate expression on each element.

    The predicate expression must return a boolean item, or a numeric item, or the empty sequence, or NULL. A NULL or an empty result from the predicate expression is treated as a false value. If the predicate result is true/false, the context element is selected/skipped, respectively. If the predicate result is a number, the context element is selected only if $pos equals that number. This means that if the predicate result is a negative number, or greater or equal to the array size, the context element is skipped.

Array Slice Step Expressions

An array slice step is used with arrays to select elements of arrays based on element position. The elements to select are identified by specifying boundary positions which identify "low" position and "high" positions. Each boundary expression must return at most one item of type LONG or INTEGER, or NULL. The low and/or the high expression may be missing. The context-item variable ($) is available during the computation of the boundary expressions.

<primary_expression>[<low>:<high>]

For example, assume an array of connects ordered from the strongest connect (position 0) to the weakest, select the strongest connection for the user with id 10:

select connections[0] as strongestConnection from Users
where id = 10;

Select user 10's five strongest connections, and return the array (notice the use of the array constructor):

select [ connections[0:4] ] as strongConnections from Users
where id = 10; 

Select user 10's five weakest connections:

select [ connections[size($) - 5 : ] ] as weakConnections from Users
where id = 10; 

An array slice step processes each context item as follows:

  1. If the context item is not an array, an array is created and the context item is added to that array. Then the array filter is applied to this single-item array.

  2. If the context item is an array, the boundary expressions (if any) are evaluated.

    If any boundary expression returns NULL or an empty result, the context item is skipped.

    Otherwise, if the low expression is absent, or if it evaluates to less than 0, the lower boundary is set to 0. If the high expression is absent, or if it evaluates to higher than the array_size -1, it is set to array_size - 1.

  3. After the low and high positions are determined, the step selects all the elements positions, inclusively, between those two boundaries. If the low position is greater than the high position, then no elements are selected.