A newer version of this documentation is available.

View Latest

Array functions

You can use array functions to evaluate arrays, perform computations on elements in an array, and to return a new array based on a transformation.

ARRAY_APPEND(expression, value)

Returns new array with value appended.

ARRAY_AVG(expression)

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

ARRAY_CONCAT(expression1, expression2)

Returns new array with the concatenation of the input arrays.

ARRAY_CONTAINS(expression, value)

Returns true if the array contains value.

ARRAY_COUNT(expression)

Returns count of all the non-NULL values in the array, or zero if there are no such values.

ARRAY_DISTINCT(expression)

Returns new array with distinct elements of input array.

ARRAY_IFNULL(expression)

Returns the first non-NULL value in the array, or NULL.

ARRAY_LENGTH(expression)

Returns the number of elements in the array.

ARRAY_MAX(expression)

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

ARRAY_MIN(expression)

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

ARRAY_POSITION(expression, value)

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

ARRAY_PREPEND(value, expression)

Returns new array with value pre-pended.

ARRAY_PUT(expression, value)

Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.

ARRAY_RANGE(start, end [, step ])

Returns new array of numbers, from start until the largest number less than end. Successive numbers are incremented by step. If step is omitted, the default is 1. If step is negative, decrements until the smallest number greater than end.

ARRAY_REMOVE(expression, value)

Returns new array with all occurrences of value removed.

ARRAY_REPEAT(value, n)

Returns new array with value repeated n times.

ARRAY_REPLACE(expression, value1, value2 [, n ])

Returns new array with all occurrences of value1 replaced with value2. If n is given, at most n replacements are performed.

ARRAY_REVERSE(expression)

Returns new array with all elements in reverse order.

ARRAY_SORT(expression)

Returns new array with elements sorted in N1QL collation order.

ARRAY_SUM(expression)

Sum of all the non-NULL number values in the array, or zero if there are no such values.

Array references ( doc.f[*].id )

You can use an asterisk (*) as an array subscript which converts the array to an object of arrays. The following example returns an array of the ages of the given contact’s children:

SELECT children[*].age FROM contacts WHERE fname = "Dave"

An equivalent query can be written using the array_star() function:

SELECT array_star(children).age FROM contacts WHERE fname = "Dave"