1. SQL Reference Guide
  2. SQL Query Management
  3. Path Expressions
  4. Array-Slice Step Expressions

Array-Slice Step Expressions

Syntax

array_slice_step ::= "[" [expression] ":" [expression] "]"

Semantics

Array slice steps are meant to be used primarily with arrays. In general, an array slice step selects elements of arrays based only on the element positions. The elements to select are the ones whose positions are within a range between a "low" position and a "high" position. The low and high positions are computed by two boundary expressions: a "low" expression for the low position and a "high" expression for the high position. Each boundary expression must return at most one item of type LONG or INTEGER, or NULL. The low and/or the high expression may be missing. The context-item variable ($) is available during the computation of the boundary expressions.

An array filter step processes each context item as follows:
  • If the context item is not an array, an array is created and the context item is added to that array. Then the array filter is applied to this single-item array as described below.
  • If the context item is an array, the boundary expressions are computed, if present. If any boundary expression returns NULL or an empty result, the context item is skipped. Otherwise, let L and H be the values returned by the low and high expressions, respectively. If the low expression is absent, L is set to 0. If the high expression is absent, H is set to the size of the array - 1. If L is < 0, L is set to 0. If H > array_size - 1, H is set to array_size - 1. After L and H are computed, the step selects all the elements between positions L and H (L and H included). If L > H no elements are selected.

Notice that based on the above rules, slice steps are actually a special case of filter steps. For example, a slice step with both boundary expressions present, is equivalent to <input expr>[<low expr> <= $pos and $pos <= <high expr>]. Slice steps are provided for convenience (and better performance).

Example 6-37 Array-Slice Step Expression

Select the strongest connection of the user with id 10. 
SELECT connections[0] AS strongestConnection
FROM users
WHERE id = 10;

Example 6-38 Array-Slice Step Expression

For user 10, select his/her 5 strongest connections (i.e. the first 5 ids in the "connections" array).

SELECT [ connections[0:4] ] AS strongConnections
FROM users
WHERE id = 10;

Notice that the slice expression will return at most 5 ids; if user 10 has fewer that 5 connections, all of his/her connections will be returned.

Example 6-39 Array-Slice Step Expression

For user 10, select his/her 5 weakest connections (i.e. the last 5 ids in the "connections" array).

SELECT [ connections[size($) - 5 : ] ] AS weakConnections
FROM users
WHERE id = 10;

In this example, size() is a function that returns the size of a given array, and $ is the context array, i.e., the array from which the 5 weakest connections are to be selected.