Previous Next JavaScript must be enabled to correctly display this content

## Primary Expressions

Primary expressions form the building blocks of more complex expressions used in SQL queries.

Column Reference:

A column-reference expression returns the item stored in the specified column within the context row (the row that a SELECT expression is currently working on). A column reference expression consists of one identifier, or two identifiers separated by a dot.

If there are two identifiers, the first is considered as the table alias and the second as the name of a column in that table. This form is called a qualified column name.

Example 6-1 Fetch the first name of all persons using qualified column name

    select p.firstname FROM Persons p

Explanation:

`p` is the table alias and `firstname` is the name of a column in the table.

Output:

    {"firstname":"Dana"}
    {"firstname":"David"}
    {"firstname":"John"}
    {"firstname":"Peter"}
    {"firstname":"John"}

If there is a single identifier, it is resolved to the name of a column in one of the tables referenced in the FROM clause. However, in this case, there must not be more than one participating table having a column with the same name. This form is called an unqualified column name.

Example 6-2 Fetch the first name of all persons using unqualified column name

    select firstname FROM Persons p

Explanation:

`firstname` is the name of a column in the `Persons` table.

Output:

    {"firstname":"Dana"}
    {"firstname":"David"}
    {"firstname":"John"}
    {"firstname":"Peter"}
    {"firstname":"John"}

Variable Reference:

A variable-reference expression returns the item that the specified variable is currently bound to. Oracle NoSQL Database allows the declaration of internal and external variables. For more details on declaring the variables and their scope, see Variable Declarations.

Internal variables are bound to their values during the execution of the expressions that declare them.

Example 6-3 Fetch the number of phones using variable reference

    select p.firstname AS NAME, $numphones AS NUM_OF_PHONES FROM Persons p, size(p.address.phones) $numphones

Explanation:

`numphones` is an internal variable that is assigned to the size of the `phones` array when the query is executed.

Output:

    {"NAME":"Dana","NUM_OF_PHONES":3}
    {"NAME":"David","NUM_OF_PHONES":1}
    {"NAME":"John","NUM_OF_PHONES":1}
    {"NAME":"Peter","NUM_OF_PHONES":4}
    {"NAME":"John","NUM_OF_PHONES":2}

    5 rows returned

Constant Expression:

Constant expressions are string, integer, number, floating point or boolean literals.

Example 6-4 Fetch names of persons who have a phone number of type 'work'

    select p.firstname, p.lastname FROM Persons p WHERE p.address.phones.type =any
          "work"

Explanation:

The string literal `work` is the constant expression in the WHERE clause. `phones` is an array and `phones.type` is a sequence. You want to check if there is any element in the sequence whose type is `work`.

As the Value Comparison Operators cannot operate on sequences of more than one item, you use the Sequence Comparison Operator `any` in addition to the value comparison operator '=' to compare the `type` field. The first name and last name of the persons having any phone number of type `work` are returned.

Output:

    {"firstname":"Dana","lastname":"Scully"}
    {"firstname":"John","lastname":"Morgan"}
    {"firstname":"Peter","lastname":"Smith"}

    3 rows returned

Parenthesized Expression:

Parenthesized expressions are used primarily to alter the default precedence among operators, and to avoid syntactic ambiguities.

Example 6-5 Fetch name of persons whose age, income satisfy the conditions in the expression

    select p.firstname FROM Persons p WHERE p.age <= 30 AND (p.age > 20 OR p.income > 400000)

Explanation:

In this query, we are returning the first name of persons whose age is less than or equal to 30, and either their age is greater than 20 or their income is greater than 400K. If the parenthesis is not present, then the order of evaluation would change as AND has a higher precedence than OR.

Output:

    {"firstname":"David"}
    1 row returned

Function Call:

Function call expressions are used to invoke built-in (system) functions. The function call starts with an id which identifies the function to call by name, followed by a parenthesized list of zero or more arguments separated by comma.

Example 6-6 Fetch names of persons who have 'books' as one of their expense category

    select p.firstname FROM Persons p WHERE EXISTS p.expenses[contains($element,"books")]

Explanation:

In the `persons` table, the `expenses` field contains the various categories across which the `persons` have spent their income. In the query above, you use a function call to the contains function. The contains function is one of the built-in functions, which indicates whether or not a search string is present inside the source string. The square brackets in the query iterates over the elements of the `expenses` map. During the iteration, the $`element` variable is bound to the current map element. Each iteration computes the expression inside the `contains` function on the map element. If the element includes the string "`books`", it returns true otherwise it is skipped. As a result, only the firstname of the persons who have an expense category of books are displayed in the output.

Output:

    {"firstname":"John"}
    {"firstname":"Peter"}

    2 rows returned

Sequence Transform:

A sequence transform expression transforms a sequence to another sequence. Syntactically it looks like a function whose name is `seq_transform`. The first argument is an expression that generates the sequence to be transformed (the input sequence) and the second argument is a "mapper" expression that is computed for each item of the input sequence. The result of the `seq_transform` expression is the concatenation of sequences produced by each evaluation of the mapper expression. The mapper expression can access the current input item using the $ variable.

Example 6-7 Fetch the contact information as a flat list of area code concatenated with phone number

    select p.firstname,seq_transform(p.address.phones[],{concat($.type," phone"):concat($.areacode,"-",$.number)}) AS CONTACT_INFO  FROM Persons p

Explanation:

In this query, you concatenate the `areacode` and `number` fields for each phone and get a flat array of these as the contact information of each person.

Output:

    {"firstname":"Dana","CONTACT_INFO":[{"work phone":"201-3213267"},{"work phone":"201-8765421"},{"home phone":"339-3414578"}]}
    {"firstname":"David","CONTACT_INFO":{"home phone":"423-8634379"}}
    {"firstname":"Peter","CONTACT_INFO":[{"work phone":"339-4120211"},{"work phone":"339-8694021"},{"home phone":"339-1205678"},{"home phone":"305-8064321"}]}
    {"firstname":"John","CONTACT_INFO":[{"work phone":"305-1234079"},{"home phone":"305-2066401"}]}
    {"firstname":"John","CONTACT_INFO":{"home phone":"339-1684972"}}
    5 rows returned

Array and Map Constructors:

An array constructor constructs a new array out of the items returned by the expressions provided inside the square brackets in a SELECT expression. These expressions are computed left to right, and the produced items are appended to the array.

Similarly, a map constructor constructs a new map or JSON object out of the items returned by the expressions provided inside the curly brackets in a SELECT expression. These expressions come in pairs; each pair computes one field. The first expression in a pair must return at most one string which serves as the field's name and the second returns the associated field value. If a value expression returns more than one item, an array is implicitly constructed to store the items, and that array becomes the field value. If either a field name or a field value expression returns an empty sequence, no field is constructed.

Example 6-8 Construct an 'expense sheet' map with a 'high expenses' array in it

    SELECT {"first_name" : p.firstName,"income" : p.income,"high_expenses" : [p.expenses.keys($value > 2000)]} AS Expense_Sheet FROM Persons p

Explanation:

In this query, we are constructing a map named `Expense_Sheet` with elements `first_name`, `high_expenses` and `income`. We use an array constructor for `high_expenses` and this contains all the categories that have expense value > 2000. Notice that the use of an explicit array for the `high_expenses` field guarantees that the field will exist in all the constructed maps, even if the evaluation inside the array constructor returns empty.

Output:

    {"Expense_Sheet":{"first_name":"Dana","high_expenses":[],"income":400000}}
    {"Expense_Sheet":{"first_name":"David","high_expenses":[],"income":100000}}
    {"Expense_Sheet":{"first_name":"John","high_expenses":["travel"],"income":100000}}
    {"Expense_Sheet":{"first_name":"Peter","high_expenses":["food"],"income":80000}}
    {"Expense_Sheet":{"first_name":"John","high_expenses":[],"income":100000000}}
    5 rows returned