Limit Nested Arrays

Limit nested arrays when an application needs a bounded set of nested rows in each generated document.

Use the limit argument to restrict the number of elements in a nested array.

For example, race pages can show a summary of the top results without loading every race entry. The race document includes the race details and only the first two result rows from the related DRIVER_RACE_MAP rows.

The following GraphQL definition limits array results to two elements:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_top_results_dv AS
race @insert @update @delete {
  _id : race_id,
  name,
  laps,
  race_date,
  results : driver_race_map(limit: 2) [{
    driver_race_map_id,
    position
  }]
};

The following SQL definition uses LIMIT 2:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_top_results_dv AS
SELECT JSON {
  '_id'       : r.race_id,
  'name'      : r.name,
  'laps'      : r.laps,
  'race_date' : r.race_date,
  'results'   :
    [ SELECT JSON {
              'driver_race_map_id' : drm.driver_race_map_id,
              'position'           : drm.position
            }
      FROM driver_race_map drm
      WHERE drm.race_id = r.race_id
      LIMIT 2 ]
}
FROM race r WITH INSERT UPDATE DELETE;

Note: The limit value must be an integer greater than or equal to 1. The table with the limit must have an identifying key, but the identifying key does not need to appear in the view output.

Oracle AI Database sorts matching rows by identifying key before applying the limit. If a limit value is specified for a table, then the table must be read-only.

If the limit value is 1, then the sub-object can be unnested or converted to a singleton sub-object. If an inserted or updated document contains more array elements than the limit allows, then an error is raised.