Array Functions

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.
SELECT ARRAY_APPEND(t.public_likes, "Valerie Smith") 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow",
      "Valerie Smith"
    ]
  }
]

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.
SELECT ARRAY_CONCAT(t.public_likes, ["John McHill", "Dave Smith"]) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow",
      "John McHill",
      "Dave Smith"
    ]
  }
]

ARRAY_CONTAINS(expression, value)

Returns true if the array contains value.
SELECT ARRAY_CONTAINS(t.public_likes, "Vallie Ryan") 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": true
  }
]

ARRAY_COUNT(expression)

Returns count of all the non-NULL values in the array, or zero if there are no such values.
SELECT ARRAY_COUNT(t.reviews) AS total_reviews
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "total_reviews": 2
  }
]

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.
SELECT ARRAY_IFNULL(t.public_likes)
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 2;
[
  {
    "$1": "Julius Tromp I"
  },
  {
    "$1": null
  }
]

ARRAY_INSERT(value, expression, expression)

The array_insert function inserts a value into a specific position in an array and returns the new array.
Syntax
array array_insert( array-name, position, value)
Example
SELECT ARRAY_INSERT(schedule, 2, "jsmith")[2]  
FROM `travel-sample` 
WHERE type = "route"  
LIMIT 1;
       {
         "requestID": "c0e073ed-5eca-451d-bcc2-57e2e01ab93d",
         "signature": {
           "$1": "json"
         },
         "results" : [
         {
           "$1": "jsmith"
         }
         ],
         "status": "success",
         "metrics": {
           "elapsedTime": "4.883422ms",
           "executionTime": "4.849777ms",
           "resultCount": 1,
           "resultSize": 38
         }
       }

ARRAY_INTERSECT(expression1, expression2, ...)

Since Couchbase Server 4.5.1

Takes two or more arrays as parameters and returns the intersection of the input arrays as the result, that is, the array containing values that are present in all the input arrays. It returns an empty array if there are no common array elements.
Examples
SELECT ARRAY_INTERSECT( ["apples","bananas","grapes","orange"], ["apples","orange"], ["apples","grapes"]);
[
  {
    "$1": [
      "apples"
    ]
  }
]

SELECT ARRAY_INTERSECT( ["apples","grapes","oranges"], ["apples"],["oranges"],["bananas", "grapes"]);
[
  {
    "$1": []
  }
]

ARRAY_LENGTH(expression)

Returns the number of elements in the array.
Example
SELECT ARRAY_LENGTH(t.public_likes) AS total_likes 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "total_likes": 8
  }
]

ARRAY_MAX(expression)

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.
SELECT ARRAY_MAX(t.public_likes) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": "Vallie Ryan"
  }
]

ARRAY_MIN(expression)

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.
SELECT ARRAY_MIN(t.public_likes) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": "Brian Kilback"
  }
]

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.
SELECT ARRAY_POSITION(t.public_likes, "Brian Kilback") FROM `travel-sample` t WHERE type="hotel" LIMIT 1;
[
  {
    "$1": 4
  }
]

ARRAY_PREPEND(value, expression)

Returns new array with value pre-pended.
SELECT ARRAY_PREPEND("Dave Smith",t.public_likes) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Dave Smith",
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

ARRAY_PUT(expression, value)

Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.
SELECT ARRAY_PUT(t.public_likes, "Dave Smith") 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow",
      "Dave Smith"
    ]
  }
]

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.
SELECT ARRAY_REMOVE(t.public_likes, "Vallie Ryan") 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

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.
SELECT ARRAY_REPLACE(t.public_likes, "Vallie Ryan", "Valerie Ryan") 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Valerie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

ARRAY_REVERSE(expression)

Returns new array with all elements in reverse order.
SELECT ARRAY_REVERSE(t.public_likes) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Elnora Trantow",
      "Ms. Moses Feeney",
      "Lilian McLaughlin",
      "Brian Kilback",
      "Vallie Ryan",
      "Jaeden McKenzie",
      "Corrine Hilll",
      "Julius Tromp I"
    ]
  }
]

ARRAY_SORT(expression)

Returns new array with elements sorted in N1QL collation order.
SELECT ARRAY_SORT(t.public_likes) 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "$1": [
      "Brian Kilback",
      "Corrine Hilll",
      "Elnora Trantow",
      "Jaeden McKenzie",
      "Julius Tromp I",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Vallie Ryan"
    ]
  }
]

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"