BSON_ARRAY_SLICE

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

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

Arguments

<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.

Return Type

A BSON array.

Remarks

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.

Examples

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] |
+---------+