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

This function takes an array and one or more values as arguments and returns a new array with the specified value(s) appended. It requires a minimum of two arguments: expression and value, and returns an error if there are fewer than two arguments. If one of the values is MISSING, then it returns MISSING. If one of the values is NULL, it returns NULL.
SELECT ARRAY_APPEND(t.public_likes, "Valerie Smith") AS add_user_likes
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "add_user_likes": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow",
      "Valerie Smith"
    ]
  }
]

ARRAY_AVG(expression)

This function takes an array as an argument and returns the arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

If the input value is MISSING, it returns MISSING. If the array size is 0, that is, the array does not contain any elements, it returns NULL. Any non-number elements in the array are ignored.

Example
SELECT ARRAY_AVG([0,1,1,2,3,5]) AS array_average;
[
  {
    "array_average": 2
  }
]

ARRAY_CONCAT(expression1, expression2, ...)

This function takes two or more expressions as arguments and returns a new array after concatenating the input arrays. It returns an error if there are fewer than two arguments. If one of the arguments or one of the array elements is MISSING, it returns MISSING. It returns NULL if one of the arguments is NULL.
SELECT ARRAY_CONCAT(t.public_likes, ["John McHill", "Dave Smith"]) AS add_user_likes
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "add_user_likes": [
      "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)

This functions checks if the array contains the specified value and returns true if the array contains value and false otherwise. If either of the input argument types are MISSING, it returns MISSING. If the first argument is not an array, it returns NULL.

If the expression is in the WHERE clause of a partial index, this function lists the expressions that are implicitly covered. For Boolean functions,
SELECT ARRAY_CONTAINS(t.public_likes, "Vallie Ryan") AS array_contains_value
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "array_contains_value": true
  }
]

ARRAY_COUNT(expression)

This function returns a count of all the non-NULL values in the array, or zero if there are no such values. If the input argument is MISSING, it returns MISSING. If the argument is not an array, it returns a NULL value.
SELECT ARRAY_COUNT(t.reviews) AS total_reviews
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "total_reviews": 2
  }
]

ARRAY_DISTINCT(expression)

This function returns a new array with distinct elements of input array. If the input argument is MISSING, it returns MISSING. If the argument is a non-array value, it returns NULL.

Example
SELECT ARRAY_DISTINCT(["apples","bananas","grapes","oranges","apples","mangoes","bananas"]) 
AS distinct_fruits;
[
  {
    "distinct_fruits": [
      "oranges",
      "grapes",
      "bananas",
      "mangoes",
      "apples"
    ]
  }
]

ARRAY_FLATTEN(expression, depth)

This function flattens nested array elements into the top-level array, up to the specified depth. If one of the arguments is MISSING, it returns MISSING. If the first argument is a non-array, or if the second argument is not a number, it returns NULL.

Syntax
array array_flatten( array-name, depth)
Example
INSERT INTO default 
            (KEY, 
             value) 
VALUES     ("na", 
            {"a":2, "b":[1,2,[31,32,33],4,[[511, 512], 52]]});

SELECT ARRAY_FLATTEN(b,1) AS flatten_array_1level
FROM default USE KEYS ["na"];
[
  {
    "flatten_array_1level": [
      1,
      2,
      31,
      32,
      33,
      4,
      [
        511,
        512
      ],
      52
    ]
  }
]

SELECT ARRAY_FLATTEN(b,2) AS flatten_array_2levels
FROM default USE KEYS ["na"];
[
  {
    "flatten_array_2levels": [
      1,
      2,
      31,
      32,
      33,
      4,
      511,
      512,
      52
    ]
  }
]

ARRAY_AGG(expression)

This function returns an array of the non-MISSING values in the group, including NULL values. It returns MISSING if the argument is MISSING or if one of the elements in the array is MISSING.

Example
SELECT ARRAY_AGG(["abc",1,NULL]) AS array_aggregate;
[
  {
    "array_aggregate": [
      [
        "abc",
        1,
        null
      ]
    ]
  }
]

ARRAY_IFNULL(expression)

This function parses the input array and returns the first non-NULL value in the array. It returns MISSING if the argument is MISSING and NULL if the argument is a non-array.

Syntax
 array_ifnull( array-name)
Example
SELECT ARRAY_IFNULL( ["","apples","","bananas","grapes","oranges"]) AS check_null;
[
  {
    "check_null": ""
  }
]

SELECT ARRAY_IFNULL(t.public_likes) AS if_null
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 2;
[
  {
    "if_null": "Julius Tromp I"
  },
  {
    "if_null": null
  }
]

ARRAY_INSERT(expression, position, value, ...)

This function inserts the specified value(s) into the specified position in an array, and returns the new array. It requires a minimum of 3 arguments and returns MISSING if any of the arguments are MISSING. It returns NULL if the first argument is a non-array or if the second argument is not an integer.
Syntax
array array_insert( array-name, position, value)
Example
SELECT ARRAY_INSERT(schedule, 2, "jsmith")[2] AS insert_val 
FROM `travel-sample` 
WHERE type = "route"  
LIMIT 1;
[
  {
    "insert_val": "jsmith"
  }
]

ARRAY_INTERSECT(expression1, expression2, ...)

Since Couchbase Server 4.5.1

This function takes two or more arrays 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. It returns MISSING if any of the arguments are MISSING, and returns NULL if any of the arguments are non-array values.
Examples
SELECT ARRAY_INTERSECT( ["apples","bananas","grapes","orange"], ["apples","orange"], ["apples","grapes"])
AS array_intersection;
[
  {
    "array_intersection": [
      "apples"
    ]
  }
]

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

ARRAY_LENGTH(expression)

This function returns the number of elements in the array. It returns MISSING if the argument is MISSING, and returns NULL if the argument is a non-array value.
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)

This function returns the largest non-NULL, non-MISSING array element, in N1QL collation order. It returns MISSING if the argument is MISSING and returns NULL if the argument is a non-array value.
SELECT ARRAY_MAX(t.public_likes) AS max_val
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "max_val": "Vallie Ryan"
  }
]

ARRAY_MIN(expression)

This function returns the smallest non-NULL, non-MISSING array element, in N1QL collation order. It returns MISSING if the argument is MISSING and returns NULL if the argument is a non-array value.
SELECT ARRAY_MIN(t.public_likes) AS min_val
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "min_val": "Brian Kilback"
  }
]

ARRAY_POSITION(expression, value)

This function returns the first position of the specified value within the array. The array position is zero-based, that is, the first position is 0. It returns -1 if the values does not exist in the array. It returns MISSING if one of the arguments is MISSING, and returns NULL if the arguments are non-array values.
SELECT ARRAY_POSITION(t.public_likes, "Brian Kilback") AS array_position
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "array_position": 4
  }
]

ARRAY_PREPEND(value, ... , expression)

This function returns the new array after prepending the array with the specified values. It requires a minimum of two arguments. It returns MISSING if one of the arguments is MISSING, and returns NULL if the last argument is a non-array.
SELECT ARRAY_PREPEND("Dave Smith",t.public_likes) AS prepend_val
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "prepend_val": [
      "Dave Smith",
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

ARRAY_PUT(expression, value, ...)

This function returns a new array with value(s) appended if the value is not already present. Otherwise, it returns the unmodified input array. It requires a minimum of two arguments. It returns MISSING if one of the arguments is MISSING, and returns NULL if the first argument is a non-array.
SELECT ARRAY_PUT(t.public_likes, "Dave Smith") AS array_put 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "array_put": [
      "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 ])

This function returns a new array of numbers, from start until the largest number less than end. Successive numbers are incremented by step. If step is not specified, the default value of 1 is used. If step is negative, the function decrements until the smallest number greater than end. It returns MISSING if any of the arguments are MISSING, and returns NULL if any of the arguments are non-numbers.

Example
SELECT ARRAY_RANGE(0, 25, 5) AS gen_array_range5;
[
  {
    "gen_array_range5": [
      0,
      5,
      10,
      15,
      20
    ]
  }
]  

SELECT ARRAY_RANGE(0.1, 2) AS gen_array_range2;
[
  {
    "gen_array_range2": [
      -1,
      0,
      1
    ]
  }
] 

SELECT ARRAY_RANGE(10, 3, -3) AS gen_array_range-3;
[
  {
    "gen_array_range-3": [
      10,
      7,
      4
    ]
  }
]

ARRAY_REMOVE(expression, value, ...)

This function returns a new array with all occurrences of the specified value(s) removed. It requires a minimum of two arguments. It returns MISSING if any of the arguments are MISSING, and returns NULL if the first argument is not an array.
SELECT ARRAY_REMOVE(t.public_likes, "Vallie Ryan") AS remove_val
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "remove_val": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

ARRAY_REPEAT(value, n)

This function returns a new array with the specified value repeated n times. It returns MISSING if any of the arguments are MISSING, and returns NULL if the first argument is not an integer.

Example
SELECT ARRAY_REPEAT("Vallie Ryan", 3) AS repeat_val;
[
  {
    "repeat_val": [
      "Vallie Ryan",
      "Vallie Ryan",
      "Vallie Ryan"
    ]
  }
]

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

This function returns a new array with all occurrences of value1 replaced with value2. If n is specified, at most n replacements are performed. It returns MISSING if any of the arguments are missing, and returns NULL if the first argument is not an array, or if the second argument is NULL.
SELECT ARRAY_REPLACE(t.public_likes, "Vallie Ryan", "Valerie Ryan") AS replace_val 
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "replace_val": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Valerie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  }
]

ARRAY_REVERSE(expression)

This function returns a new array with all the elements in reverse order. It returns MISSING if the argument is MISSING, and returns NULL if the argument is a non-array value.
SELECT ARRAY_REVERSE(t.public_likes) AS reverse_val
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "reverse_val": [
      "Elnora Trantow",
      "Ms. Moses Feeney",
      "Lilian McLaughlin",
      "Brian Kilback",
      "Vallie Ryan",
      "Jaeden McKenzie",
      "Corrine Hilll",
      "Julius Tromp I"
    ]
  }
]

ARRAY_SORT(expression)

This function returns a new array with the elements sorted in N1QL collation order. It returns MISSING if the argument is MISSING, and returns NULL if the argument is a non-array value.
Example
SELECT ARRAY_SORT(t.public_likes) AS sorted_array
FROM `travel-sample` t 
WHERE type="hotel" 
LIMIT 1;
[
  {
    "sorted_array": [
      "Brian Kilback",
      "Corrine Hilll",
      "Elnora Trantow",
      "Jaeden McKenzie",
      "Julius Tromp I",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Vallie Ryan"
    ]
  }
]

ARRAY_STAR(expression)

This function converts an array of objects into an object of arrays. It returns MISSING if the argument is MISSING, and returns NULL if the argument is a non-array value.

Example
SELECT ARRAY_STAR( [
   {
    "address": "Capstone Road, ME7 3JE",
    "city": "Medway",
    "country": "United Kingdom",
    "name": "Medway Youth Hostel",
    "url": "http://www.yha.org.uk"
  },
  {
    "address": "6 rue aux Juifs",
    "city": "Giverny",
    "country": "France",
    "name": "The Robins",
    "url": "http://givernyguesthouse.com/robin.htm"
  }]) AS array_star;

[
  {
    "array_star": {
      "address": [
        "Capstone Road, ME7 3JE",
        "6 rue aux Juifs"
      ],
      "city": [
        "Medway",
        "Giverny"
      ],
      "country": [
        "United Kingdom",
        "France"
      ],
      "name": [
        "Medway Youth Hostel",
        "The Robins"
      ],
      "url": [
        "http://www.yha.org.uk",
        "http://givernyguesthouse.com/robin.htm"
      ]
    }
  }
]

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"

ARRAY_SUM(expression)

This function returns the sum of all the non-NULL number values in the array, or zero if there are no number values.

It returns MISSING if the argument is MISSING, and returns NULL if the argument is a non-array value.

Syntax
number ARRAY_SUM(array);
Example
SELECT ARRAY_SUM([0,1,1,2,3,5]) as sum;
[
  {
    "sum": 12
  }
]

ARRAY_SYMDIFF(expression1, expression2, ...)

Synonym: ARRAY_SYMDIFF1(expression1, expression2, ...)

This function returns a new array based on the set symmetric difference, or disjunctive union, of the input arrays. The new array contains only those elements that appear in exactly one of the input arrays. It requires a minimum of two arguments. It returns MISSING if any of the arguments are MISSING, and returns NULL if one of the arguments is not an array.

The difference between ARRAY_SYMDIFF() and ARRAY_SYMDIFFN() is that the former function includes the value when it appears only once while the latter function includes the value when it appears odd number of times in the input arrays.
Note: Refer to the following article for more information on the difference between a normal and n-ary symdiff: https://en.wikipedia.org/wiki/Symmetric_difference.
Examples
SELECT ARRAY_SYMDIFF([1, 2], [1, 2, 4], [1, 3]) AS symm_diff1;
[
  {
    "symm_diff1": [
      3,
      4
    ]
  }
]

ARRAY_SYMDIFFN(expression1, expression2, ...)

This function returns a new array based on the set symmetric difference, or disjunctive union, of the input arrays. The new array contains only those elements that appear in an odd number of input arrays. It requires a minimum of two arguments. It returns MISSING if any of the arguments are MISSING, and returns NULL if one of the arguments is not an array.

The difference between ARRAY_SYMDIFF() and ARRAY_SYMDIFFN() is that the former function includes the value when it appears only once while the latter function includes the value when it appears odd number of times in the input arrays.
Note: Refer to the following article for more information on the difference between a normal and n-ary symdiff: https://en.wikipedia.org/wiki/Symmetric_difference.
Examples
SELECT ARRAY_SYMDIFFN([1, 2], [1, 2, 4], [1, 3]) AS symm_diffn;
[
  {
    "symm_diffn": [
      1,
      3,
      4
    ]
  }
]

ARRAY_UNION(expression1, expression2, ...)

This function returns a new array with the set union of the input arrays. It requires a minimum of two arguments. It returns MISSING if any of the arguments are MISSING, and returns NULL if one of the arguments is not an array.

Examples
SELECT ARRAY_UNION([1, 2], [1, 2, 4], [1, 3]) AS array_union;
[
  {
    "array_union": [
      3,
      2,
      1,
      4
    ]
  }
]

SELECT ARRAY_UNION([1, 2], [1, 2, 4], "abc") AS array_union;
[
  {
    "array_union": null
  }
]