We've reorganized our documentation navigation structure to align directly with your operational workflows. See the release notes and the walkthrough video for more information.

Array functions

GoogleSQL for SecOps supports the following array functions.

Function list

Name	Summary
`ARRAY`	Produces an array with one element for each row in a subquery.
`ARRAY_AGG`	Gets an array of values. For more information, see Aggregate functions.
`ARRAY_CONCAT`	Concatenates one or more arrays with the same element type into a single array.
`ARRAY_CONCAT_AGG`	Concatenates arrays and returns a single array as a result. For more information, see Aggregate functions.
`ARRAY_FIRST`	Gets the first element in an array.
`ARRAY_LAST`	Gets the last element in an array.
`ARRAY_LENGTH`	Gets the number of elements in an array.
`ARRAY_REVERSE`	Reverses the order of elements in an array.
`ARRAY_TO_STRING`	Produces a concatenation of the elements in an array as a `STRING` value.
`GENERATE_ARRAY`	Generates an array of values in a range.
`GENERATE_DATE_ARRAY`	Generates an array of dates in a range.
`JSON_EXTRACT_ARRAY`	(Deprecated) Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value. For more information, see JSON functions.
`JSON_EXTRACT_STRING_ARRAY`	(Deprecated) Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value. For more information, see JSON functions.
`JSON_QUERY_ARRAY`	Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value. For more information, see JSON functions.
`JSON_VALUE_ARRAY`	Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value. For more information, see JSON functions.
`RANGE_BUCKET`	Scans through a sorted array and returns the 0-based position of a point's upper bound. For more information, see Mathematical functions.

`ARRAY`

ARRAY(subquery)

Description

The ARRAY function returns an ARRAY with one element for each row in a subquery.

If subquery produces a SQL table, the table must have exactly one column. Each element in the output ARRAY is the value of the single column of a row in the table.

Constraints

Subqueries are unordered, so the elements of the output ARRAY aren't guaranteed to preserve any order in the source table for the subquery. However, if the subquery includes an ORDER BY clause, the ARRAY function will return an ARRAY that honors that clause.
If the subquery returns more than one column, the ARRAY function returns an error.
If the subquery returns an ARRAY typed column or ARRAY typed rows, the ARRAY function returns an error that GoogleSQL doesn't support ARRAYs with elements of type ARRAY.
If the subquery returns zero rows, the ARRAY function returns an empty ARRAY. It never returns a NULL ARRAY.

Return type

ARRAY

Examples

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

/*-----------+
 | new_array |
 +-----------+
 | [1, 2, 3] |
 +-----------*/

`ARRAY_CONCAT`

ARRAY_CONCAT(array_expression[, ...])

Description

Concatenates one or more arrays with the same element type into a single array.

The function returns NULL if any input argument is NULL.

Return type

ARRAY

Examples

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

/*--------------------------------------------------+
 | count_to_six                                     |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 +--------------------------------------------------*/

`ARRAY_FIRST`

ARRAY_FIRST(array_expression)

Description

Takes an array and returns the first element in the array.

Produces an error if the array is empty.

Returns NULL if array_expression is NULL.

Return type

Matches the data type of elements in array_expression.

Example

SELECT ARRAY_FIRST(['a','b','c','d']) as first_element

/*---------------+
 | first_element |
 +---------------+
 | a             |
 +---------------*/

`ARRAY_LAST`

ARRAY_LAST(array_expression)

Description

Takes an array and returns the last element in the array.

Produces an error if the array is empty.

Returns NULL if array_expression is NULL.

Return type

Matches the data type of elements in array_expression.

Example

SELECT ARRAY_LAST(['a','b','c','d']) as last_element

/*---------------+
 | last_element  |
 +---------------+
 | d             |
 +---------------*/

`ARRAY_LENGTH`

ARRAY_LENGTH(array_expression)

Description

Returns the size of the array. Returns 0 for an empty array. Returns NULL if the array_expression is NULL.

Return type

INT64

Examples

SELECT
  ARRAY_LENGTH(["coffee", "tea", "milk" ]) AS size_a,
  ARRAY_LENGTH(["cake", "pie"]) AS size_b;

/*--------+--------+
 | size_a | size_b |
 +--------+--------+
 | 3      | 2      |
 +--------+--------*/

`ARRAY_REVERSE`

ARRAY_REVERSE(value)

Description

Returns the input ARRAY with elements in reverse order.

Return type

ARRAY

Examples

SELECT ARRAY_REVERSE([1, 2, 3]) AS reverse_arr

/*-------------+
 | reverse_arr |
 +-------------+
 | [3, 2, 1]   |
 +-------------*/

`ARRAY_TO_STRING`

ARRAY_TO_STRING(array_expression, delimiter)

Description

Returns a concatenation of the elements in array_expression as a STRING or BYTES value. The value for array_expression can either be an array of STRING or BYTES data type.

Return type

STRING for a function signature with STRING input.
BYTES for a function signature with BYTES input.

Examples


SELECT ARRAY_TO_STRING(['coffee', 'tea', 'milk' ], '--') AS text

/*--------------------------------+
 | text                           |
 +--------------------------------+
 | coffee--tea--milk              |
 +--------------------------------*/

`GENERATE_ARRAY`

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Description

Returns an array of values. The start_expression and end_expression parameters determine the inclusive start and end of the array.

The GENERATE_ARRAY function accepts the following data types as inputs:

INT64
NUMERIC
FLOAT64

The step_expression parameter determines the increment used to generate array values. The default value for this parameter is 1.

The GENERATE_ARRAY function returns an error if any of the following are true:

step_expression evaluates to 0.
Any input is NaN.
The resulting array is too large.

If any argument is NULL, the function returns a NULL array.

Return Data Type

ARRAY

Examples

The following returns an array of integers, with a default step of 1.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

/*-----------------+
 | example_array   |
 +-----------------+
 | [1, 2, 3, 4, 5] |
 +-----------------*/

The following returns an array using a user-specified step size.

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

/*---------------+
 | example_array |
 +---------------+
 | [0, 3, 6, 9]  |
 +---------------*/

The following returns an array using a negative value, -3 for its step size.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

/*---------------+
 | example_array |
 +---------------+
 | [10, 7, 4, 1] |
 +---------------*/

The following returns an array using the same value for the start_expression and end_expression.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

/*---------------+
 | example_array |
 +---------------+
 | [4]           |
 +---------------*/

The following returns an empty array, because the start_expression is greater than the end_expression, and the step_expression value is positive.

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

/*---------------+
 | example_array |
 +---------------+
 | []            |
 +---------------*/

The following returns a NULL array because end_expression is NULL.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

/*---------------+
 | example_array |
 +---------------+
 | NULL          |
 +---------------*/

The following returns multiple arrays.

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

/*---------------+
 | example_array |
 +---------------+
 | [3, 4, 5]     |
 | [4, 5]        |
 | [5]           |
 +---------------*/

`GENERATE_DATE_ARRAY`

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

Description

Returns an array of dates. The start_date and end_date parameters determine the inclusive start and end of the array.

The GENERATE_DATE_ARRAY function accepts the following data types as inputs:

start_date must be a DATE.
end_date must be a DATE.
INT64_expr must be an INT64.
date_part must be either DAY, WEEK, MONTH, QUARTER, or YEAR.

The INT64_expr parameter determines the increment used to generate dates. The default value for this parameter is 1 day.

The GENERATE_DATE_ARRAY function returns an error if any of the following are true:

INT64_expr is set to 0.
The resulting array is too large.

Return Data Type

ARRAY containing 0 or more DATE values.

Examples

The following returns an array of dates, with a default step of 1.

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

/*--------------------------------------------------+
 | example                                          |
 +--------------------------------------------------+
 | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
 +--------------------------------------------------*/

The following returns an array using a user-specified step size.

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

/*--------------------------------------+
 | example                              |
 +--------------------------------------+
 | [2016-10-05, 2016-10-07, 2016-10-09] |
 +--------------------------------------*/

The following returns an array using a negative value, -3 for its step size.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

/*--------------------------+
 | example                  |
 +--------------------------+
 | [2016-10-05, 2016-10-02] |
 +--------------------------*/

The following returns an array using the same value for the start_dateand end_date.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

/*--------------+
 | example      |
 +--------------+
 | [2016-10-05] |
 +--------------*/

The following returns an empty array, because the start_date is greater than the end_date, and the step value is positive.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

/*---------+
 | example |
 +---------+
 | []      |
 +---------*/

The following returns a NULL array, because one of its inputs is NULL.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

/*---------+
 | example |
 +---------+
 | NULL    |
 +---------*/

The following returns an array of dates, using MONTH as the date_part interval:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

/*--------------------------------------------------------------------------+
 | example                                                                  |
 +--------------------------------------------------------------------------+
 | [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
 +--------------------------------------------------------------------------*/

The following uses non-constant dates to generate an array.

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

/*--------------------------------------------------------------+
 | date_range                                                   |
 +--------------------------------------------------------------+
 | [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
 | [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
 | [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
 | [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
 +--------------------------------------------------------------*/

Supplemental materials

OFFSET and ORDINAL

For information about using OFFSET and ORDINAL with arrays, see Array subscript operator and Accessing array elements.

Array functions Stay organized with collections Save and categorize content based on your preferences.

Function list

ARRAY

ARRAY_CONCAT

ARRAY_FIRST

ARRAY_LAST

ARRAY_LENGTH

ARRAY_REVERSE

ARRAY_TO_STRING

GENERATE_ARRAY

GENERATE_DATE_ARRAY

Supplemental materials

OFFSET and ORDINAL

Array functions

`ARRAY`

`ARRAY_CONCAT`

`ARRAY_FIRST`

`ARRAY_LAST`

`ARRAY_LENGTH`

`ARRAY_REVERSE`

`ARRAY_TO_STRING`

`GENERATE_ARRAY`

`GENERATE_DATE_ARRAY`