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 7-1 Fetch the first name of all persons using qualified column name
select p.firstname FROM Persons pp is the table alias and firstname is the name of a column in the table.
{"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 7-2 Fetch the first name of all persons using unqualified column name
select firstname FROM Persons pfirstname is the name of a column in the Persons table.
{"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 7-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) $numphonesnumphones is an internal variable that is assigned to the size of the phones array when the query is executed.
{"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 returnedConstant Expression:
Constant expressions are string, integer, number, floating point or boolean literals.
Example 7-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"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.
{"firstname":"Dana","lastname":"Scully"}
{"firstname":"John","lastname":"Morgan"}
{"firstname":"Peter","lastname":"Smith"}
3 rows returnedParenthesized Expression:
Parenthesized expressions are used primarily to alter the default precedence among operators, and to avoid syntactic ambiguities.
Example 7-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)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 returnedFunction 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 7-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")]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.
{"firstname":"John"}
{"firstname":"Peter"}
2 rows returnedSequence 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 7-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 pIn 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.
{"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 returnedArray 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 7-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 pIn 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.
{"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