Return Arrays of Scalar Values

Return arrays of scalar values when an application needs a list of values instead of full nested objects.

Use square brackets around a single scalar field to return an array of scalar values from a nested table.

For example, the application might need only an ordered list of driver names, not the full driver details.

The following GraphQL definition returns the driver names as scalar values in array driverNames:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_driver_names_dv AS
team @insert @update @delete {
  _id : team_id,
  name,
  points,
  driverNames : driver @insert @update @delete [
    name
  ]
};

The following SQL definition returns the same scalar array:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_driver_names_dv AS
SELECT JSON {
  '_id'         : t.team_id,
  'name'        : t.name,
  'points'      : t.points,
  'driverNames' :
    [ SELECT d.name
      FROM driver d WITH INSERT UPDATE DELETE
      WHERE d.team_id = t.team_id ]
}
FROM team t WITH INSERT UPDATE DELETE;

Note: An array of scalars can select only one column. If the array is not a generated read-only expression, then the selected column must have a unique non-nullable or primary-key constraint.

Composite unique keys and composite primary keys are supported when one selected column is the array value and the remaining key columns link to the parent table.

For update operations, Oracle AI Database compares the inbound scalar array with the database values by key. Matching values remain unchanged. New values are inserted or linked to the parent row. Removed values are deleted or unlinked according to the @delete and @update settings.

For select operations, rows are sorted in ascending order by the identifying key column of the table. You can change the sort order by using ORDER BY.