BSON_ARRAY_SLICE

Syntax

BSON_ARRAY_SLICE(<input>, <position>, <n>)

<input>: A valid BSON array or an expression that evaluates to a valid BSON array.
<position>: An integer or an expression that evaluates to an integer. It specifies the index at which the array slicing starts. A positive value indicates the start of an array, and a negative value indicates that the starting position is calculated from the end of an array (in reverse order).
<n>: An integer or an expression that evaluates to an integer. It specifies the number of array items to return.

A BSON array.

If the specified <position> is a positive integer that exceeds the bounds of an array, returns an empty array.
If the specified <position> is a negative integer that exceeds the bounds of an array, slicing starts at the 0 index, the starting position in the array.
BSON_ARRAY_SLICE() returns NULL if:
- <input> is SQL NULL, BSON NULL, or BSON undefined.
- <n> is SQL NULL, BSON NULL, or BSON undefined.

Note: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

The following example returns 3 elements from the array starting at 0 index.

SQL

SELECT BSON_ARRAY_SLICE('[0,1,2,3,4,5]':>BSON, 0,3):>JSON AS Result;

+---------+
| Result  |
+---------+
| [0,1,2] |
+---------+

The following example returns 3 elements from the array starting from the third element at the end.

SQL

SELECT BSON_ARRAY_SLICE('[0,1,2,3,4,5]':>BSON, -3,3):>JSON AS Result;

+---------+
| Result  |
+---------+
| [3,4,5] |
+---------+

The following example returns an empty array because the specified index is positive and out of array bounds.

SQL

SELECT BSON_ARRAY_SLICE('[0,1,2,3,4,5]':>BSON, 10,3):>JSON AS Result;

+--------+
| Result |
+--------+
| []     |
+--------+

The following example returns the first three elements of the array because the specified index is negative and out of array bounds.

SQL

SELECT BSON_ARRAY_SLICE('[0,1,2,3,4,5]':>BSON, -6,3):>JSON AS Result;

+---------+
| Result  |
+---------+
| [0,1,2] |
+---------+