Chapter 11. Constructors

Table of Contents

Array Constructors
Map Constructors

SQL for Oracle NoSQL Database offers two constructors that you can use: Array and Map constructors.


The examples shown in this chapter rely on the sample data shown in Example Data.

Array Constructors

[ <expression>, <expression>, ... ]

An array constructor constructs a new array out of the items returned by the expressions inside the square brackets. These expressions are computed left to right and the produced items are appended to the array. Any NULLs produced by the input expressions are skipped (arrays cannot contain NULLs).

The type of the constructed array is determined during query compilation, based on the types of the input expressions and the usage of the constructor expression. Specifically, if a constructed array can be inserted in another constructed array and this “parent” array has type ARRAY(JSON), then the “child” array will also have type ARRAY(JSON). This is because "typed" data is not allowed inside JSON data.

For example, the use of the explicit array constructor here means that the field will exist in all returned rows, even if the path inside the array constructor returns empty. Without this constructor, a NULL can be returned for the field. With it, an empty result returns an empty array.

select firstname, lastname, 
[u.expenses.keys($value > 1000)] AS Expenses
from Users u;