Expressions

An expression represents a set of operations to be performed in order to produce a result. The various kinds of expressions supported by Oracle NoSQL Database are described later in this chapter.

Expressions are built by combining other expressions and sub-expressions through operators, function calls, or other gramatical constructs. The simplest kind of expressions are constants and references to variables or identifiers.

If you want to follow along with the examples, create the airline application table and insert data as described in Tables used in the Examples.

Example 6-1 Retrieve the full name, ticket number, and bag tag number for all customer baggage shipped after the 1st of Jan 2019 in an airline application

SELECT fullName, 
ticketNo,
bag.bagInfo.tagNum 
FROM BaggageInfo bag 
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

Explanation: This query demonstrates the usage of several expressions. In the BaggageInfo table, you store the baggage arrival date for every passenger in the bagArrivalDate field of the bagInfo array. Since the bagInfo is a JSON field, the bagArrivalDate attribute contains the date in ISO-8601 format as a string value. You use the SELECT, FROM, and WHERE clauses of the SELECT Expression to choose columns from the table.

To fetch the list of the customer baggage that was shipped after a specified date, you use the value comparison operator ">=" to check if the bagArrivalDate for each bag is greater than or equal to the given date, that is, the 1st of Jan 2019. Here, the input date is also a string in ISO-8601 format. Using string-formatted dates in ISO-8601 format works with logical comparison operators due to the natural sort order of strings. The chronological ordering of dates happens to follow the natural sort order of strings, so in this specific case, you get the desired behavior. For more complex date operations such as date arithmetic, you must cast the ISO-8601 date string into a Timestamp data type. For more information on using the CAST operator, see Cast Expression.

The EXISTS operator, which is a comparison expression returns either a true or false value based on the result of the value comparison operation. In the above query, if the bag arrival date is greater than or equal to the given date, the EXISTS operator returns a true value and the corresponding row is fetched. Else, the row is filtered out.

In this example, the bag is a table alias for the BaggageInfo table. The $element is an implicitly-declared variable and is bound to the context row (every baggage of the customer). The variable references are a part of primary expressions.

Output:

{"fullName":"Fallon Clements","ticketNo":1762350390409,"tagNum":"17657806255507"}
{"fullName":"Lucinda Beckman","ticketNo":1762320569757,"tagNum":"17657806240001"}
{"fullName":"Elane Lemons","ticketNo":1762324912391,"tagNum":"1765780623244"}
{"fullName":"Gerard Greene","ticketNo":1762341772625,"tagNum":"1765780626568"}
{"fullName":"Kendal Biddle","ticketNo":1762377974281,"tagNum":"17657806296887"}
{"fullName":"Zulema Martindale","ticketNo":1762340579411,"tagNum":"17657806288937"}
{"fullName":"Mary Watson","ticketNo":1762340683564,"tagNum":"17657806299833"}
{"fullName":"Teena Colley","ticketNo":1762357254392,"tagNum":"17657806255823"}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957,"tagNum":["17657806240001","17657806340001"]}
{"fullName":"Adam Phillips","ticketNo":1762344493810,"tagNum":"17657806255240"}
{"fullName":"Adelaide Willard","ticketNo":1762392135540,"tagNum":"17657806224224"}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917,"tagNum":"17657806215913"}
{"fullName":"Michelle Payne","ticketNo":1762330498104,"tagNum":"17657806247861"}
{"fullName":"Zina Christenson","ticketNo":1762390789239,"tagNum":"17657806228676"}
{"fullName":"Raymond Griffin","ticketNo":1762399766476,"tagNum":"17657806243578"}
{"fullName":"Henry Jenkins","ticketNo":176234463813,"tagNum":"17657806216554"}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464,"tagNum":"17657806292229"}
{"fullName":"Dierdre Amador","ticketNo":1762376407826,"tagNum":"17657806240229"}
{"fullName":"Joanne Diaz","ticketNo":1762383911861,"tagNum":"17657806292518"}
{"fullName":"Omar Harvey","ticketNo":1762348904343,"tagNum":"17657806234185"}
{"fullName":"Doris Martin","ticketNo":1762355527825,"tagNum":"17657806232501"}