Sequences
A sequence is a set of zero or more items. All expressions operate on zero or more input sequences and produce an output sequence as their result.
A sequence is just a collection set of zero or more items (including NULLs). A sequence is not an item itself (so no nested sequences) nor is it a container: there is neither a persistent data structure nor a java class at the public API level (or internally) that represents a sequence. Expressions usually operate on sequences by iterating over their items.
Note:
An array is not a sequence of items. Instead, it is a single item, albeit one that contains other items in it. So, arrays are containers of items.Although, in general, Oracle NoSQL Database expressions work on sequences and produce sequences, many of them place restrictions on the cardinality of the sequences they accept and/or produce. For example, several expressions are scalar: they require that their input sequence(s) contain no more than one item and they never produce a sequence of more than one item. Notice that a single item is considered equivalent to a sequence containing only that single item.
Boxing and Unboxing Sequence
A sequence produced by an expression E can be converted to an array by wrapping E with an array constructor : [ E ]. See Array and Map Constructors section. This is called boxing the sequence. Conversely, there are expressions that unbox an array: they select all or a subset of the items contained in the array and return these items as a sequence. There is no implicit unboxing of arrays; an expression must always be applied to do the unboxing. In most cases, sequence boxing must also be done explicitly, that is, the query writer must use an array constructor. There are, however, a couple of cases where boxing is done implicitly, that is, an expression (which is not an array constructor) will convert an input sequence to an array.
Note:
In standard SQL the term "expression" means "scalar expression", i.e., an expression that returns exactly one (atomic) item. The only operations that can produce more than one items (or zero items) are query blocks (either as top-level queries or subqueries) and the set operators like union, intersection, etc (in these cases, the items are tuples). In Oracle NoSQL Database too, most expressions are scalar. Like the query blocks of standard SQL, the select-form-where expression of Oracle NoSQL Database returns a sequence of items. However, to navigate and extract information from complex, hierarchical data, Oracle NoSQL Database includes path expressions as well. See Path Expressions section. Path expressions are the other main source of multi-item sequences in Oracle NoSQL Database. However, if path expressions are viewed as subqueries, the Oracle NoSQL Database model is not that different from standard SQL.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-2 Fetch the passenger name and tag number for all bags whose bag arrival date is greater than 2019-03-01T13:00:00Z
SELECT fullname,
bag.bagInfo[].tagNum
FROM BaggageInfo bag
WHERE bag.bagInfo[].bagArrivalDate >any "2019-03-01T13:00:00Z"Explanation: In an airline application, each piece of baggage that is checked in by the passenger is associated with a unique tag number. In the BaggageInfo table, the tag numbers are stored in the tagnum field of the bagInfo array. If the passenger has more than one piece of luggage, the baginfo array has more than one element, and each baggage has a unique tag number.
In this query, you fetch the full name and tag numbers of all such luggage whose bag arrival date is greater than the specified value. The bagArrivalDate field is a string that holds the arrival date for each baggage in ISO-8601 format. You compare the bag arrival date of each baggage with the input date value, which is also a string in the ISO-8601 format. For passengers with additional luggage, all the associated tag numbers are listed. You use the SELECT, FROM, and WHERE clauses of the select expression to choose columns from a table.
Here, the bag is a table alias for the BaggageInfo table and can be used anywhere in the SELECT statement. The bag.bagInfo[].bagArrivalDate is a sequence of more than one item. An error is returned if you use comparison operators to compare a sequence with more than one item. Hence, you use the sequence comparison operator >any to compare the sequence with the required arrival date. For more details on sequence comparison, see Sequence Comparison Operators. In this example, you can compare the string-formatted dates in ISO-8601 format due to the natural sorting order of strings without having to cast into Timestamp data types.
{"fullname":"Elane Lemons","tagNum":"1765780623244"}
{"fullname":"Omar Harvey","tagNum":"17657806234185"}
{"fullname":"Henry Jenkins","tagNum":"17657806216554"}
{"fullname":"Kendal Biddle","tagNum":"17657806296887"}
{"fullname":"Mary Watson","tagNum":"17657806299833"}
{"fullname":"Gerard Greene","tagNum":"1765780626568"}
{"fullname":"Dierdre Amador","tagNum":"17657806240229"}
{"fullname":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"]}
{"fullname":"Lucinda Beckman","tagNum":"17657806240001"}
{"fullname":"Doris Martin","tagNum":"17657806232501"}