Array Functions

reference

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_AGG(`expr`)

Description

This function returns an array of the non-MISSING group values in the input expr, including NULL values.

Arguments

expr: [Required] The group of elements you wish to output in an array.

Return Values

An array of non-MISSING values.

If the input expression is MISSING or if one of the elements in the array is MISSING, then it returns MISSING.

Example

Group a list of three items into an array.

Query

SELECT ARRAY_AGG( [ "abc", 1, NULL ] ) AS array_aggregate;

Results

[
  {
    "array_aggregate": [
      [
        "abc",
        1,
        null
      ]
    ]
  }
]

ARRAY_APPEND(`expr`, `val1`, `val2`, …)

Description

This function takes an array expr and one or more val arguments to return a new array with the specified val argument(s) appended.

It requires a minimum of two arguments and returns an error if there are fewer.

Arguments

expr: [Required] The array to be appended to.
val1, val2, …: [At least 1 is required] The values to be appended.

Return Values

A new array with the specified val argument(s) appended.

If either of the input argument types are MISSING, then it returns MISSING.

If either of the input argument types are NULL, then it returns NULL.

If the expr argument is not an array, then it returns NULL.

If the expr is in the WHERE clause of a partial index, this function lists the expressions that are implicitly covered.

Example

Append two numbers to the end of a given array.

Query

SELECT ARRAY_APPEND( [ 1 , 2 ] , 3 , 4) AS numbers;

Results

[
  {
    "numbers": [
      1,
      2,
      3,
      4
    ]
  }
]

ARRAY_AVG(`expr`)

Description

This function takes an array expr 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.

Arguments

expr: [Required] The array of numbers to be evaluated.

Return Values

A number representing the arithmetic mean (average) of all the non-NULL number values in the array expression.

If there are no number values in array expr, then it returns NULL.

If the input expr is MISSING, then it returns MISSING.

If the array size of expr is 0 (no elements), then it returns NULL.

Any non-number elements in the array expr are ignored.

Example

Find the average from an array of numbers.

Query

SELECT ARRAY_AVG( [ 0 , 1 , 1 , 2 , 3 , 5 ] ) AS array_average;

Results

[
  {
    "array_average": 2
  }
]

ARRAY_BINARY_SEARCH(`expr`, `val`, …)

Description

This function returns the first position of the specified value val within the sorted array expr.

The array position is zero-based, that is, the first position is 0.

This function uses a binary search algorithm. If the array is unsorted, the function may not be able to find the value.

Arguments

expr: [Required] The array you want to search, sorted in SQL++ collation order.
val: [Required] The value whose position you want to find.

Return Values

An integer representing the first position of the input val, where the first position is 0. If the value val occurs more than once within the array expr, only the first position is returned.

It returns -1 if the input val is not found in the array.

If one of the arguments is MISSING, it returns MISSING.

If the input expr is not an array, it returns NULL.

Example

Find the position of a number in a sorted array.

Query

SELECT ARRAY_BINARY_SEARCH( [ 1 , 3 , 5 , 7 , 9 ] , 5)
AS position;

Results

[
  {
    "position": 2
  }
]

ARRAY_CONCAT(`expr1`, `expr2`, …)

Description

This function takes two or more expr arrays and returns a new array after concatenating the input arrays.

If there are fewer than two arguments, then it returns an error.

Arguments

expression1, expression2, …: [At least 2 are required] The arrays to be concatenated together.

Return Values

A new array, concatenated from the input arrays.

If any of the input expr arguments or one of the array elements are MISSING, then it returns MISSING.

If any of the input expr arguments is NULL, then it returns NULL.

If any of the input expr arguments is not an array, then it returns NULL.

Example

Combine two arrays into a single array.

Query

SELECT ARRAY_CONCAT( [ 1 , 2 , 3 ], [ 4 , 5 , 6 ] )
AS combined_array;

Results

[
  {
    "combined_array": [
      1,
      2,
      3,
      4,
      5,
      6
    ]
  }
]

ARRAY_CONTAINS(`expr`, `val`)

Description

This functions checks if the array expression contains the specified value.

Arguments

expr: [Required] The array to be searched.
val: [Required] The value that is being searched for.

Return Values

If either of the input argument types are MISSING, then it returns MISSING.

If either of the input argument types are NULL, then it returns NULL.

If the expr argument is not an array, then it returns NULL.

If the array expr contains val, then it returns TRUE; otherwise, it returns FALSE.

Example

Check if the value 3 exists in a given array of numbers.

Query

SELECT ARRAY_CONTAINS( [ 1 , 2 , 3 , 4 , 5 ] , 3 )
AS contains_value;

Results

[
  {
    "contains_value": true
  }
]

ARRAY_COUNT(`expr`)

Description

This function counts all the non-NULL values in the input expr array.

Arguments

expr: [Required] The array to be searched and evaluate its values.

Return Values

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

If the expr argument is MISSING, then it returns MISSING.

If the expr argument is NULL, then it returns NULL.

If the expr argument is not an array, then it returns NULL.

Example

Count the number of elements in a given array.

Query

SELECT ARRAY_COUNT( [ 1 , 2 , 3 , 4 , 5 ] )
AS total_count;

Results

[
  {
    "total_count": 5
  }
]

ARRAY_DISTINCT(`expr`)

Description

This function returns a new array with distinct elements of the input array expr.

Arguments

expr: [Required] The array of items to be evaluated.

Return Values

An array with distinct elements of the input array expr.

If the input expr is MISSING, it returns MISSING.

If the input expr is a non-array value, it returns NULL.

Example

Remove duplicate elements from a given array.

Query

SELECT ARRAY_DISTINCT( [ 1 , 2 , 2 , 3 , 4 , 4 , 5 ] )
AS distinct_numbers;

Results

[
  {
    "distinct_numbers": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]

ARRAY_EXCEPT(`expr1`, `expr2`)

Description

This function returns all the elements of the first array, except for those which are also included in the second array.

Arguments

expr1: [Required] The input array, from which specified elements may be excluded.
expr2: [Required] The array of elements to be excluded.

Return Value

An array of all the elements in expr1, except for those which also occur in expr2.

If any of the arguments is MISSING, it returns MISSING.

If any of the arguments is a non-array, it returns NULL.

Examples

Exclude elements of one array from another.

Query

SELECT ARRAY_EXCEPT( [ 1 , 2 , 3 , 4 , 5 ] , [ 2 , 4 ] )
AS result_array;

Results

[
  {
    "result_array": [
      1,
      3,
      5
    ]
  }
]

ARRAY_FLATTEN(`expr`, `depth`)

Description

This function flattens nested array elements into the top-level array, up to the specified depth.

Arguments

expr: [Required] The multilevel array to be flattened.
depth: [Required] The Integer representing the number of depths to flatten.

Return Value

An array with depth fewer levels than the input array expr.

If one of the arguments is MISSING, it returns MISSING.

If the input expr is a non-array, or if the input depth argument is not an integer, it returns NULL.

Examples

Flatten a nested array by 1 and 2 levels.

Query

SELECT
  ARRAY_FLATTEN(
    [ [ 1, 2 ], [ 3, [ 4, 5 ] ] ], 1
  ) AS flatten_by_1,
  ARRAY_FLATTEN(
    [ [ 1, 2 ], [ 3, [ 4, 5 ] ] ], 2
  ) AS flatten_by_2;

Results

[
  {
    "flatten_by_1": [
      1,
      2,
      3,
      [
        4,
        5
      ]
    ],
    "flatten_by_2": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]

ARRAY_IFNULL(`expr`)

Description

This function parses the input array expr and returns the first non-NULL value in the array.

Arguments

expr: [Required] The array of values to be evaluated.

Return Values

The first non-NULL value in the input array.

If the input expr is MISSING, then it returns MISSING.

If the input expr is a non-array, then it returns NULL.

Examples

Find the first non-NULL value in a given array.

Query

SELECT ARRAY_IFNULL( [ NULL , NULL , 1 , 2 , 3 ] )
AS first_non_null;

Results

[
  {
    "first_non_null": 1
  }
]

ARRAY_INSERT(`expr`, `pos`, `val1`, `val2`, …)

Description

This function inserts the specified value or multiple value items into the specified position in the input array expression, and returns the new array.

Arguments

expr: [Required] The array to insert items into.
pos: [Required] The integer specifying the array position from the left of the input array expr, where the 1st position is 0 (zero).
val1, val2, …: [At least one is required] The value or multiple value items to insert into the input array expression.

Return Values

An array with the input value or multiple value items inserted into the input array expression at position pos.

If any of the three arguments are MISSING, then it returns MISSING.

If the expr argument is a non-array or if the position argument is not an integer, then it returns NULL.

Example

Insert 55 into the 2nd position of an array.

Query

SELECT ARRAY_INSERT( [ 1 , 2 , 3 , 4 ] , 2 , 55 )
AS updated_array;

Results

[
  {
    "updated_array": [
      1,
      2,
      55,
      3,
      4
    ]
  }
]

ARRAY_INTERSECT(`expr1`, `expr2`, ...)

Description

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 of the input arrays.

Arguments

expr1, expr2, …: [At least 2 are required] The two or more arrays to compare the values of.

Return Values

An array containing the values that are present in all of the input arrays.

If there are no common elements, then it returns an empty array.

If any of the input arguments are MISSING, then it returns MISSING.

If any of the input arguments are non-array values, then it returns NULL.

Examples

Compare three arrays to find common elements.

Query

SELECT ARRAY_INTERSECT(
  [ 1 , 2 , 3 , 4 ] ,
  [ 2 , 4 , 6 ] ,
  [ 4 , 8 ] )
AS array_intersection;

Results

[
  {
    "array_intersection": [
      4
    ]
  }
]

Compare three arrays with no common elements.

Query

SELECT ARRAY_INTERSECT(
  [ 1 , 2 , 3 ] ,
  [ 3 , 4 , 5 , 6 ] ,
  [ 6 , 7 , 8 , 9 ] )
AS array_intersection;

Results

[
  {
    "array_intersection": []
  }
]

ARRAY_LENGTH(`expr`)

Equivalent: LEN()

Description

This function returns the number of elements in the input array.

Arguments

expr: [Required] The array whose elements you want to know the number of.

Return Values

An integer representing the number of elements in the input array.

If the input argument is MISSING, then it returns MISSING.

If the input argument is a non-array value, then it returns NULL.

Example

Find the number of elements in a given array.

Query

SELECT ARRAY_LENGTH( [ 1 , 2 , 3 , 4 , 5 ] )
AS total_elements;

Results

[
  {
    "total_elements": 5
  }
]

ARRAY_MAX(`expr`)

Description

This function returns the largest non-NULL, non-MISSING array element, in SQL++ collation order.

Arguments

expr: [Required] The array whose elements you want to know the highest value of.

Return Values

The largest non-NULL, non-MISSING array element, in SQL++ collation order.

If the input expr is MISSING, then it returns MISSING.

If the input expr is a non-array value, then it returns NULL.

Example

Find the largest value in a given array.

Query

SELECT
  ARRAY_MAX( [ 1 , 3 , 9 , 5 ] ) AS max_num,
  ARRAY_MAX( [ "delta" , "united" , "southwest" ] ) max_string;

Results

[
  {
    "max_num": 9,
    "max_string": "united"
  }
]

ARRAY_MIN(`expr`)

Description

This function returns the smallest non-NULL, non-MISSING array element, in SQL++ collation order.

Arguments

expr: [Required] The array whose elements you want to know the lowest value of.

Return Values

The smallest non-NULL, non-MISSING array element, in SQL++ collation order.

If the input expr is MISSING, then it returns MISSING.

If the input expr is a non-array value, then it returns NULL.

Example

Find the smallest value in a given array.

Query

SELECT
  ARRAY_MIN( [ 1 , 3 , 9 , 5 ] ) AS min_num,
  ARRAY_MIN( [ "delta" , "united" , "southwest" ] ) min_string;

Results

[
  {
    "min_num": 1,
    "min_string": "delta"
  }
]

ARRAY_MOVE(`expr`, `val1`, `val2`)

Description

This function returns a new array containing all the elements of expr, with one element moved to a new position.

Arguments

expr: [Required] The input array containing an element that you want to move.
val1: [Required] An integer specifying the old location of the element to move.
val2: [Required] An integer specifying the new location of the element to move.

If val1 or val2 are 0 or greater, the position is counted from the left of the input array, where the leftmost position in the array is 0 (zero). If val1 or val2 are less than 0, the position is counted from the right of the input array, where the rightmost position in the array is -1.

Return Values

An array with the element at the position specified by val1 moved to a new position specified by val2.

If either of the val arguments is outside the array, the function returns NULL.

If any of the arguments is MISSING, then it returns MISSING.

If the expr argument is a non-array, or if either of the val arguments is not an integer, then it returns NULL.

Examples

Move the 1st element in a given array to the 3rd position in the array.

Query

SELECT ARRAY_MOVE(["a", "b", "c", "d", "e"], 0, 3)
AS updated_array;

Results

[
  {
    "updated_array": [
      "b",
      "c",
      "d",
      "a",
      "e"
    ]
  }
]

Move the 1st element in a given array to the penultimate position in the array.

Query

SELECT ARRAY_MOVE( [ "a" , "b" , "c" , "d" , "e" ] , 0 , -2)
AS penultimate;

Results

[
  {
    "penultimate": [
      "b",
      "c",
      "d",
      "a",
      "e"
    ]
  }
]

ARRAY_POSITION(`expr`, `val`)

Description

This function returns the first position of the specified value within the array expression.

The array position is zero-based, that is, the first position is 0.

Arguments

expr: [Required] The array you want to search.
val: [Required] The value whose position you want to know.

Return Values

An integer representing the first position of the input val, where the first position is 0. If the value val occurs more than once within the array expr, only the first position is returned.

It returns -1 if the input val does not exist in the array.

If one of the arguments is MISSING, it returns MISSING.

If either of the arguments are non-array values, it returns NULL.

Example

Find the position of the value 30 in a given array.

Query

SELECT ARRAY_POSITION( [ 10 , 20 , 30 , 40 , 50 ] , 30 )
AS position;

Results

[
  {
    "position": 2
  }
]

ARRAY_PREPEND(`val1`, `val2`, … , `expr`)

Description

This function returns the new array after prepending the array expr with the specified val or multiple val arguments.

It requires a minimum of two arguments.

Arguments

val1, val2, …: [At least 1 is required] The value or multiple value arguments to prepend to the input expr.
expression: [Required] The array you want to have the input value argument(s) prepended to.

Return Values

A new array with the input val argument(s) prepended to the input array expr.

If one of the arguments is MISSING, it returns MISSING.

If the last argument is a non-array, it returns NULL.

Example

Add the value 0 to the beginning of a given array.

Query

SELECT ARRAY_PREPEND( 0 , [ 1 , 2 , 3 , 4 , 5 ] )
AS updated_array;

Results

[
  {
    "updated_array": [
      0,
      1,
      2,
      3,
      4,
      5
    ]
  }
]

ARRAY_PUT(`expr`, `val1`, `val2`, …)

Description

This function returns a new array with val or multiple val arguments appended if the val is not already present. Otherwise, it returns the unmodified input array expr.

It requires a minimum of two arguments.

Arguments

expr: [Required] The array you want to append the input value or value arguments.
val1, val2, …: [At least 1 is required] The value or multiple value arguments that you want appended to the end of the input array expression.

Return Values

A new array with val or multiple val arguments appended if the val is not already present. Otherwise, it returns the unmodified input array expr.

If one of the arguments is MISSING, then it returns MISSING.

If the first argument is a non-array, then it returns NULL.

Example

Append the value 5 to a given array of numbers.

Query

SELECT ARRAY_PUT([1, 2, 3, 4], 5) AS updated_array;

Results

[
  {
    "updated_array": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]

ARRAY_RANGE(`start_num`, `end_num` `step_num` ])

Description

This function returns a new array of numbers, from start_num until the largest number less than end_num. Successive numbers are incremented by step_int.

If step_int is not specified, then the default value is 1. If step_num is negative, then he function decrements until the smallest number greater than end_num.

Arguments

start_num: [Required] The integer to start a new array with.
end_num: [Required] The integer that is one number larger than the final integer in the output array.
step_num: [Optional; default is 1] The number between each array element.

If step_num is negative, then the function decrements until the smallest number greater than end_num.

Output Values: A new array of numbers, from start_num until the largest number less than end_num.

If any of the arguments are MISSING, then it returns MISSING.

If any of the arguments do not start with a digit, then it returns an error.

Examples

Generate an array of numbers from 0 to 20 with a step of 5.

Query

SELECT ARRAY_RANGE( 0 , 25 , 5 ) AS generated_array;

Results

[
  {
    "generated_array": [
      0,
      5,
      10,
      15,
      20
    ]
  }
]

Generate an array of numbers from 0.1 to 1.1 with the default step 1.

Query

SELECT ARRAY_RANGE( 0.1 , 2 ) AS generated_array;

Results

[
  {
    "generated_array": [
      0.1,
      1.1
    ]
  }
]

Generate an array from 10 to 3 with a step of -3.

Query

SELECT ARRAY_RANGE( 10 , 3 , -3 ) AS generated_array;

Results

[
  {
    "generated_array": [
      10,
      7,
      4
    ]
  }
]

ARRAY_REMOVE(`expr`, `val1`, `val2`, …)

Description

This function returns a new array with all occurrences of the specified value or multiple value fields removed from the array expression. It requires a minimum of two arguments.

Arguments

expr

[Required] The input array to have the specified val or multiple val fields removed.

val1, val2, …

[At least 1 is required] The input value or multiple values to remove from the input array expr.

Output Values

A new array with all occurrences of the specified val or multiple val fields removed from the array expr.

If any of the arguments are MISSING, then it returns MISSING.

If the first argument is not an array, then it returns NULL.

This function cannot be used to remove NULL values from an array as it uses an equality predicate when evaluating the array elements to remove. Since NULL does not equal NULL, such values are not matched and remain in the array.

To remove NULL values, use the Array Collection Operator instead. For example:

SELECT ARRAY name FOR name IN ["Ryan", NULL, "Corrine"]
WHEN name IS NOT NULL
END AS filtered_names;

Example

Remove 3 and 5 from an array of numbers.

Query

SELECT ARRAY_REMOVE( [ 1 , 2 , 3 , 4 , 5 ] , 3, 5 )
AS updated_array;

Results

[
  {
    "updated_array": [
      1,
      2,
      4
    ]
  }
]

ARRAY_REPEAT(`val`, `rep_int`)

Description

This function returns a new array with the specified val repeated rep_int times.

Arguments

val: [Required] The input value you want repeated.
rep_int: [Required] The integer number of times you want the input val repeated.
Output Values: A new array with the specified val repeated rep_int times.

If any of the arguments are MISSING, then it returns MISSING.

If the rep_int argument is not an integer, then it returns NULL.

Example

Create an array with the value "hello" repeated 3 times.

Query

SELECT ARRAY_REPEAT("hello", 3) AS repeated_array;

Results

[
  {
    "repeated_array": [
      "hello",
      "hello",
      "hello"
    ]
  }
]

ARRAY_REPLACE(`expr`, `val1`, `val2` [, `max_int`])

Description

This function returns a new array with all occurrences of value1 replaced with value2.

If max_int is specified, the function performs no more than max_int replacements.

Arguments

expr: [Required] The input array you want to replace val1 with val2.
val1: [Required] The existing value in the input expr you want to replace.
val2: [Required] The new value you want to take the place of val1 in the input expr.
max_int: [Optional. Default is no maximum] The number of maximum replacements to perform.

Return Values

A new array with all or max_int occurrences of val1 replaced with val2.

If any of the arguments are MISSING, then it returns MISSING.

If the first argument is not an array or if the second argument is NULL, then it returns NULL.

Example

Replace all occurrences of 2 with 99 in a given array.

Query

SELECT ARRAY_REPLACE( [ 1 , 2 , 3 , 2 , 4 ] , 2 , 99) AS updated_array;

Results

[
  {
    "updated_array": [
      1,
      99,
      3,
      99,
      4
    ]
  }
]

ARRAY_REPLACE_EQUIVALENT(`expr`, `val1`, `val2` [, `max_int`])

Description

This function is similar to the ARRAY_REPLACE() function and returns a new array with all occurrences of val1 replaced with val2. However, it determines matches using the equivalence operator instead of the equality operator. This enables you to replace NULL values and composite object values with NULL attributes, which is not possible with ARRAY_REPLACE().

If max_int is specified, the function performs no more than max_int replacements.

Arguments

expr: [Required] The input array you want to replace val1 with val2.
val1: [Required] The existing value in the input expr you want to replace.
val2: [Required] The new value you want to take the place of val1 in the input expr.
max_int: [Optional] The maximum number of replacements to perform. By default, you can perform any number of replacements.

Return Values

A new array with all or max_int occurrences of val1 replaced with val2.

If any of the arguments are MISSING, then it returns MISSING.

If the first argument is not an array, then it returns NULL.

Example

The following example shows how the ARRAY_REPLACE_EQUIVALENT function can replace NULL values, while the ARRAY_REPLACE function cannot.

Query

SELECT
ARRAY_REPLACE( [ 1 , 2 , null , 4 ] , null , 99 )
  AS replace_result,
ARRAY_REPLACE_EQUIVALENT( [ 1 , 2 , null , 4 ] , null , 99 )
  AS replace_equiv_result;

Results

[
  {
    "replace_result": null,
    "replace_equiv_result": [
      1,
      2,
      99,
      4
    ]
  }
]

ARRAY_REVERSE(`expr`)

Description

This function returns a new array with all the elements of expr in reverse order.

Arguments

expr: [Required] The input array whose elements you want to reverse.

Return Values

A new array with all the elements of expr in reverse order.

If the argument is MISSING, then it returns MISSING.

If the argument is a non-array value, then it returns NULL.

Example

Reverse the order of elements in a given array.

Query

SELECT ARRAY_REVERSE( [ 1 , 2 , 3 , 4 , 5 ] )
AS reversed_array;

Results

[
  {
    "reversed_array": [
      5,
      4,
      3,
      2,
      1
    ]
  }
]

ARRAY_SORT(`expr`)

Description

This function returns a new array with the elements of expr sorted in SQL++ collation order.

Arguments

expr: [Required] The input array you want sorted.

Return Values

A new array with the elements of expr sorted in SQL++ collation order.

If the argument is MISSING, then it returns MISSING.

If the argument is a non-array value, then it returns NULL.

Example

Sort the elements of a given array in ascending order.

Query

SELECT ARRAY_SORT( [ 5 , 3 , 1 , 4 , 2 ] )
AS sorted_array;

Results

[
  {
    "sorted_array": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]

ARRAY_STAR(`expr`)

Description

This function converts an array of expr objects into an object of arrays.

Arguments

expr: [Required] The input array you want to convert into an object of arrays.

Output Values

An object of arrays.

If the argument is MISSING, then it returns MISSING.

If the argument is a non-array value, then it returns NULL.

Example

Convert a given array of two objects, each with three fields, into an object of three arrays.

SELECT ARRAY_STAR([
  { "id": 10226, "airline": "Atifly", "code": "A1F" },
  { "id": 10123, "airline": "Texas Wings", "code": "TXW" }
]) AS object_of_arrays;

Results

[
  {
    "object_of_arrays": {
      "airline": [ "Atifly", "Texas Wings"],
      "code": [ "A1F", "TXW" ],
      "id": [ 10226, 10123]
    }
  }
]

Array References

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 airline names from an array of flight objects:

Query

WITH sample AS (
  SELECT [
    { "airline": "Atifly", "code": "AF" },
    { "airline": "Texas Wings", "code": "TXW" }
  ] AS flights
)
SELECT flights[*].airline
FROM sample;

Results

[
  {
    "airline": [
      "Atifly",
      "Texas Wings"
    ]
  }
]

You can write an equivalent query using the array_star() function:

Query

WITH sample AS (
  SELECT [
    { "airline": "Atifly", "code": "AF" },
    { "airline": "Texas Wings", "code": "TXW" }
  ] AS flights
)
SELECT array_star(flights).airline
FROM sample;

Results

[
  {
    "airline": [
      "Atifly",
      "Texas Wings"
    ]
  }
]

Empty Array Subscripts

You can use an empty array subscript ([ ]) to return an array that includes only defined elements.

The rules governing its use are as follows:

Expression Description

Expression	Description
`field[]`	If `field` is not an array, it returns `NULL`. If `field` is an array, it returns the array as is.
`field[][]`	If `field` is not an array, it returns `NULL`. If `field` contains only unnamed arrays, it returns `field` as is. Otherwise, it returns `NULL`.
`field[].field2`	If `field` is not an array, it returns `MISSING`. If `field` is an array, it extracts `field2` from each element in `field` and returns a new array with those values. If `field` contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts `field2` from each object where it is present.
`field[][].field2`	If `field` is not an array, it returns `MISSING`. If every element in `field` is not an unnamed array, it returns `MISSING`. If `field` contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts `field2` from each object where it is present.
`field[].field2[].field3`	Returns an array of `field3` values by traversing two levels of arrays. First it extracts `field2` from each element of `field`, then extracts `field3` from each element of the resulting `field2` array. If `field` and `field2` contain unnamed arrays, they are flattened by one level. Then from the resulting `field2` array, it extracts `field3` from each object where it is present.

field[]

If field is not an array, it returns NULL.
If field is an array, it returns the array as is.

field[][]

If field is not an array, it returns NULL.
If field contains only unnamed arrays, it returns field as is. Otherwise, it returns NULL.

field[].field2

If field is not an array, it returns MISSING.
If field is an array, it extracts field2 from each element in field and returns a new array with those values.
If field contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts field2 from each object where it is present.

field[][].field2

If field is not an array, it returns MISSING.
If every element in field is not an unnamed array, it returns MISSING.
If field contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts field2 from each object where it is present.

field[].field2[].field3

Returns an array of field3 values by traversing two levels of arrays. First it extracts field2 from each element of field, then extracts field3 from each element of the resulting field2 array.
If field and field2 contain unnamed arrays, they are flattened by one level. Then from the resulting field2 array, it extracts field3 from each object where it is present.

If you use more that two empty array subscripts (for example, field[][][]), the function considers only the first two subscripts and ignores the rest.

Example

Given the following sample document:

{
  "a": {
    "airline": "AF",
    "airlineid": "airline_003",
    "destinationairport": "SFO",
    "distance": 2481.617376098415,
    "equipment": "320",
    "id": 3,
    "schedule": [
      {
        "day": 0,
        "flight": "AF198",
        "utc": "10:13:00"
      },
      {
        "flight": "AF250",
        "utc": "12:59:00"
      },
      {
        "day": 2,
        "flight": "AF223",
        "special_flights": [
          {
            "a": "SA"
          },
          {
            "b": [
              [
                {
                  "c": "SC1"
                },
                {
                  "c": "SC2"
                }
              ],
              [
                {
                  "c": "SC3"
                }
              ]
            ]
          }
        ],
        "utc": "19:41:00"
      }
    ],
    "sourceairport": "DFW"
  }
}

Here’s how different array subscripts evaluate:

a.airline[]: Returns NULL (not an array).
a.schedule[]: Equivalent to a.schedule, returns the array.
a.schedule[].day: Returns [0, 2]. This is in contrast to a.schedule[*].day, which returns [0, null, 2].
a.schedule[].special_flights[].a: Returns ["SA"].
a.schedule[][].utc: Returns MISSING.
a.schedule[].special_flights[].b[][].c: Returns ["SC1", "SC2", "SC3"].
a.schedule[].special_flights[].b[].c: Also returns ["SC1", "SC2", "SC3"], as the unnamed arrays are flattened.

ARRAY_SUM(`expr`)

Description

This function returns the sum of all the non-NULL number values in the expr array.

Arguments

expr: [Required] The input array of numbers you want to know the total value of.

Return Values

The sum of all the non-NULL number values in the expr array.

If there are no number values, then it returns 0 (zero).

If the argument is MISSING, then it returns MISSING.

If the argument is a non-array value, then it returns NULL.

Example

Find the total of a given array of numbers.

Query

SELECT ARRAY_SUM( [ 0 , 1 , 1 , 2 , 3 , 5 ] ) as sum;

Results

[
  {
    "sum": 12
  }
]

ARRAY_SYMDIFF(`expr1`, `expr2`, …)

This function has a synonym ARRAY_SYMDIFF1().

Description

This function returns a new array based on the set symmetric difference, or disjunctive union, of the input expression arrays. The new array contains only those elements that appear in exactly one of the input arrays, and it requires a minimum of two arguments.

Arguments

expr1, expr2, …: [At least 2 are required] The input arrays to compare.

Return Values

A new array containing only those elements that appear in exactly one of the input arrays.

If any of the arguments is MISSING, then it returns MISSING.

If any of the arguments is a non-array value, then it returns NULL.

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 an odd number of times in the input arrays.

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.

Example

Find the elements that appear in exactly one of these three input arrays.

Query

SELECT ARRAY_SYMDIFF( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS symm_diff1;

Results

[
  {
    "symm_diff1": [
      3,
      4
    ]
  }
]

ARRAY_SYMDIFF1(`expr1`, `expr2`, …)

Synonym of ARRAY_SYMDIFF().

ARRAY_SYMDIFFN(`expr1`, `expr2`, …)

Description

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, and it requires a minimum of two arguments.

Arguments

expr1, expr2, …: [At least 2 are required] The input arrays to compare.

Return Values

A new array containing only those elements that appear in an odd number of the input arrays.

If any of the arguments is MISSING, then it returns MISSING.

If any of the arguments is a non-array value, then it returns NULL.

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.

Example

Find the elements that appear in an odd number of these three input arrays.

Query

SELECT ARRAY_SYMDIFFN( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS symm_diffn;

Results

[
  {
    "symm_diffn": [
      1,
      3,
      4
    ]
  }
]

ARRAY_UNION(`expr1`, `expr2`, …)

Description

This function returns a new array with the set union of the input arrays, and it requires a minimum of two arguments.

Arguments

expr1, expr2, …: [At least 2 are required] The input arrays to compare.

Return Values

A new array with the set union of the input arrays.

If any of the arguments is MISSING, then it returns MISSING.

If any of the arguments is a non-array value, then it returns NULL.

Examples

List the union of three given arrays.

Query

SELECT ARRAY_UNION( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS array_union;

Results

[
  {
    "array_union": [
      3,
      2,
      1,
      4
    ]
  }
]

List the union of two given arrays with a string.

Query

SELECT ARRAY_UNION( [ 1 , 2 ] , [ 1 , 2 , 4 ] , "abc")
AS array_union;

Results

[
  {
    "array_union": null
  }
]

Array Functions

ARRAY_AGG(expr)

Description

Arguments

Return Values

Example

ARRAY_APPEND(expr, val1, val2, …)

Description

Arguments

Return Values

Example

ARRAY_AVG(expr)

Description

Arguments

Return Values

Example

ARRAY_BINARY_SEARCH(expr, val, …)

Description

Arguments

Return Values

Example

ARRAY_CONCAT(expr1, expr2, …)

Description

Arguments

Return Values

Example

ARRAY_CONTAINS(expr, val)

Description

Arguments

Return Values

Example

ARRAY_COUNT(expr)

Description

Arguments

Return Values

Example

ARRAY_DISTINCT(expr)

Description

Arguments

Return Values

Example

ARRAY_EXCEPT(expr1, expr2)

Description

Arguments

Return Value

Examples

ARRAY_FLATTEN(expr, depth)

Description

Arguments

Return Value

Examples

ARRAY_IFNULL(expr)

Description

Arguments

Return Values

Examples

ARRAY_INSERT(expr, pos, val1, val2, …)

Description

Arguments

Return Values

Example

ARRAY_INTERSECT(expr1, expr2, ...)

Description

Arguments

Return Values

Examples

ARRAY_LENGTH(expr)

Description

Arguments

Return Values

Example

ARRAY_MAX(expr)

Description

Arguments

Return Values

Example

ARRAY_MIN(expr)

Description

Arguments

Return Values

ARRAY_AGG(`expr`)

ARRAY_APPEND(`expr`, `val1`, `val2`, …)

ARRAY_AVG(`expr`)

ARRAY_BINARY_SEARCH(`expr`, `val`, …)

ARRAY_CONCAT(`expr1`, `expr2`, …)

ARRAY_CONTAINS(`expr`, `val`)

ARRAY_COUNT(`expr`)

ARRAY_DISTINCT(`expr`)

ARRAY_EXCEPT(`expr1`, `expr2`)

ARRAY_FLATTEN(`expr`, `depth`)

ARRAY_IFNULL(`expr`)

ARRAY_INSERT(`expr`, `pos`, `val1`, `val2`, …)

ARRAY_INTERSECT(`expr1`, `expr2`, ...)

ARRAY_LENGTH(`expr`)

ARRAY_MAX(`expr`)

ARRAY_MIN(`expr`)

ARRAY_MOVE(`expr`, `val1`, `val2`)

ARRAY_POSITION(`expr`, `val`)

ARRAY_PREPEND(`val1`, `val2`, … , `expr`)

ARRAY_PUT(`expr`, `val1`, `val2`, …)

ARRAY_RANGE(`start_num`, `end_num` `step_num` ])

ARRAY_REMOVE(`expr`, `val1`, `val2`, …)

ARRAY_REPEAT(`val`, `rep_int`)

ARRAY_REPLACE(`expr`, `val1`, `val2` [, `max_int`])

ARRAY_REPLACE_EQUIVALENT(`expr`, `val1`, `val2` [, `max_int`])

ARRAY_REVERSE(`expr`)

ARRAY_SORT(`expr`)

ARRAY_STAR(`expr`)

ARRAY_SUM(`expr`)

ARRAY_SYMDIFF(`expr1`, `expr2`, …)