Previous Next JavaScript must be enabled to correctly display this content

## 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 grammatical 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"}

JSON collection tables:

You can use SQL expressions to query data from JSON collection tables. The queries work in the same way as fixed schema tables. You can access the document name/value pairs in a JSON collection table by specifying JSON path expressions. A top-level attribute in the document can be accessed using its field name as the path expression, while a nested attribute must be accessed using a path expression to the attribute. A few examples are added in the following sections to demonstrate the queries in JSON collection tables.

To follow along with the examples, create a JSON collection table for the shopping application and insert the sample data records as described in the JSON Collection Table Example section.