Array Functions and Operators - Ocient Documentation

The System enables you to work with data as arrays. The system contains functions that work with arrays and operators that enable you to parse data within arrays.

ARRAY_CAT_DISTINCT

Concatenates two or more arrays in the order of the input arguments. After concatenation, the function removes duplicates from the result array while preserving the first occurrence order. The function skips any NULL input arguments. If all input arrays are NULL, the function returns a NULL. If at least one NULL element is present in the array, the function returns a NULL in the result array with the position of the first NULL the function finds in the order of the input arguments. Syntax

SQL

ARRAY_CAT_DISTINCT(input_array1, input_array2 [, ...])

Argument	Data Type	Description
`input_array1, input_array2 [, ...]`	ARRAY	Two or more arrays for concatenation and deduplication. The elements within all input arrays must have the same data type.

Examples Concatenate Two Arrays and Remove Duplicates Concatenate two arrays and remove duplicate elements within the arrays.

SQL

SELECT ARRAY_CAT_DISTINCT(ARRAY[1,2,2,3,1], ARRAY[2,3,4,4,5]);

Output: [1,2,3,4,5] Concatenate Two Arrays with NULLs and Remove Duplicates Deduplicate the contents of the array. In this case, the array contains NULLs.

SQL

SELECT ARRAY_CAT_DISTINCT(ARRAY[1,2,2,NULL,1], ARRAY[2,4,4,NULL]);

Output: [1,2,NULL,4]

ARRAY_DISTINCT

Removes duplicates from an array while preserving the first occurrence order. If at least one NULL is present in the array, the function returns a NULL with the position of the first NULL of the input array in the output array. Syntax

SQL

ARRAY_DISTINCT(input_array)

Argument	Data Type	Description
`input_array`	ARRAY	The array for deduplication.

Examples Remove Duplicates from an Array Deduplicate the contents of the array.

SQL

SELECT ARRAY_DISTINCT(ARRAY[1,2,2,3,1]);

Output: [1,2,3] Remove Duplicates from an Array with NULLs Deduplicate the contents of the array. In this case, the array contains NULLs.

SQL

SELECT ARRAY_DISTINCT(ARRAY[1,2,NULL,2,3,NULL,1]);

Output: [1,2,NULL,3]

ARRAY_LENGTH

Returns the length of the array for the specified dimension. If the array is a nested array, the function returns the length of the outermost array. Syntax

SQL

ARRAY_LENGTH(input_array)

Argument	Data Type	Description
`input_array`	ARRAY	The array that contains any type of elements. The array can be single or multi-dimensional.

Example Return the length of the array.

SQL

SELECT ARRAY_LENGTH(ARRAY[1,2]);

Output: 2

ARRAY_SORT

Sorts and returns the input array based on the natural ordering of its elements. The behavior of this function varies depending on the syntax you use. Syntaxes Basic Array Sort Sorts and returns the input array based on the natural ordering of its elements. If one of the array elements is NULL, then this function sorts the NULL values to the end of the array.

SQL

ARRAY_SORT(input_array)

Argument	Data Type	Description
`input_array`	ARRAY	An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`.

Sort an Array With a Lambda Function Sorts and returns the input array based on the results of the specified Lambda function. The function should have two arguments representing two elements of the array. This function should return a negative integer, 0, or a positive integer if the first element is less than, equal to, or greater than the second element, respectively.

SQL

ARRAY_SORT(input_array,function)

Argument	Data Type	Description
`input_array`	ARRAY	An array that contains any type of elements. The array can be single or multi-dimensional.
`function`	FUNCTION	This is an optional argument. A lambda function with the format `(x T, y T) -> INT` or another SQL reference function.

The Ocient System supports this syntax with the data pipeline functionality only. For details, see Transform Data in Data Pipelines.

Sort an Array in the Specified Order Determines how the array handles the order of elements in the array.

SQL

ARRAY_SORT(input_array,sort_order)

Argument	Data Type	Description
`input_array`	ARRAY	An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`.
`sort_order`	BOOLEAN	This is an optional argument. Determines the sort order of the array. The value `true` means ascending order. The value `false` means descending order. The default value is `true`.

Sort an Array in the Specified Order with NULL Elements Determines how the array handles the order of elements in the array and where NULL elements appear.

SQL

ARRAY_SORT(input_array,sort_order,nulls_first)

Argument	Data Type	Description
`input_array`	ARRAY	An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`.
`sort_order`	BOOLEAN	This is an optional argument. Determines the sort order of the array. The value `true` means ascending order. The value `false` means descending order. The default value is `true`.
`nulls_first`	BOOLEAN	This is an optional argument. Determines whether NULL elements appear first or last in the array. The `true` value means that NULLs appear first in the array. The `false` value means that they appear last. If the array is in ascending order, the default value is `false`. Otherwise, the default value is `true`.

Examples Sort an Array Sort an array of three elements [2,3,1].

SQL

SELECT ARRAY_SORT(ARRAY[2,3,1]);

Output: [1,2,3] Sort an Array by Specifying the Sort Order Sort an array of four elements [2,3,1,NULL] and specify the order of the elements in descending order.

SQL

SELECT ARRAY_SORT(ARRAY[2,3,1,NULL],false);

Output: [NULL,3,2,1] Sort an Array by Specifying the Sort Order and NULL Placement Sort an array of four elements [2,NULL,3,1] and specify the order of the elements in ascending order, and have the NULL element appear last.

SQL

SELECT ARRAY_SORT(ARRAY[2,NULL,3,1],true,false);

Output: [1,2,3,NULL]

UNNEST

Expands each element in an input array into an individual row. For example, the UNNEST function on an array column of type ARRAY(INT) with values [2, 6] yields two result rows with integers 2 and 6. The values of the other columns in each input row are unchanged in each corresponding output row. You can specify multiple array columns to unnest the specified arrays from each row in parallel. Syntax

SQL

UNNEST( array_column [, ... ]​ )
   [ WITH <unnest_options> ]

<unnest_options> ::=
   { ORDINALITY [ ord_identifier ]
   | VALUE [ val_identifier ]
   | NULL_INPUT }

Argument	Data Type	Description
`array_column`	`ARRAY`	The array column to expand.
`ord_identifier`	`CHAR`	When you use this argument in an `ORDINALITY` clause, the argument is the name for the output column.
`val_identifier`	`CHAR`	When you use this argument in a `VALUE` clause, the argument is the name for the unnested column.

The UNNEST function supports these options. When you use only one of these options, enclosing the option in parentheses is optional. However, if you use more than one of these options, you must enclose them in parentheses.

Options	Description
`ORDINALITY [ord_identifier]`	Optional. Generates a new integer column that corresponds to the index position of each output element in the original array. The `ord_identifier` value specifies the name of the output ordinality column. If you do not specify this clause, the column name defaults to the original column name with the suffix `_ord`.
`VALUE [val_identifier]`	Optional. Rename the unnested column to the new name specified in the `val_identifier` argument. If you do not specify this clause, the column name defaults to the original column name with the suffix `_val`.
`NULL_INPUT`	Optional. When the input column array is empty or NULL, this clause specifies to replace each output row with an array with one NULL element. This replacement preserves the contents of other input rows. By default, this option is not enabled.

These examples demonstrate how the UNNEST function operates on arrays and other objects. Most of the examples use the data rows from this table.

Text

a   b        c        d     e      f
--------------------------------------------
1   [1, 2]   [6, 9]   []    NULL   [[1, 2], [5], []]
2   [7, 5]   [4]      [3]   [8]    [[6], [9, 4, 7], [3, 8]]

Examples Expand a Column This example performs a simple UNNEST on column b.

SQL

SELECT UNNEST(b);

Output

Text

b_val
-----------
1
2
7
5

Expand a Column Using the ORDINALITY Option In this example, the ORDINALITY option adds a second return column that represents the index position of the value in the input array.

SQL

SELECT UNNEST(b) WITH ORDINALITY;

Output

Text

b_val   b_ord
--------------------
1       1
2       2
7       1
5       2

Expand a Column with Empty Data In this example, the UNNEST query returns only one row because one of the two rows in column d is an empty array. If you use the NULL_INPUT option, the query returns a second row with the NULL value.

SQL

SELECT UNNEST(d);

Output

Text

d_val
------------
3

Expand a Column with NULL Data UNNEST also does not return array values that are NULL unless you specify the NULL_INPUT option.

SQL

SELECT UNNEST(e);

Output

Text

e_val
------------
8

Expand a Column with Multiple Array Layers The UNNEST function expands only one array layer.

The empty array remains in the returned values.

SQL

SELECT UNNEST(f);

Output

Text

f_val
------------
[1,2]
[5]
[]
[6]
[9,4,7]
[3,8]

Expand Columns Using Multiple UNNEST Statements Multiple UNNEST statements in a query produce a per-row Cartesian product of all the unnested values.

SQL

SELECT UNNEST(b), UNNEST(c);

Output

Text

b_val   c_val
------------
1       6
1       9
2       6
2       9
7       4
5       4

Expand Columns Using Multiple UNNEST Statements in Reverse Order This example uses UNNEST on the same columns but in reverse order.

SQL

SELECT UNNEST(c), UNNEST(b);

Output

Text

c_val   b_val
------------
6       1
6       2
9       1
9       2
4       7
4       5

Expand Multiple Columns In this example, the query unnests two array columns in parallel. Even though it does not include the NULL_INPUT option, the query still returns NULL for column c to correspond with column b.

SQL

SELECT UNNEST(b, c);

Output

Text

b_val   c_val
------------
1       6
2       9
7       4
5       NULL

Expand M****ultiple Array Columns This query unnests two array columns.

SQL

SELECT UNNEST(b, d);

Output

Text

b_val   d_val
------------
1       NULL
2       NULL
7       3
5       NULL

Expand Values of Arrays Within Arrays This example has two layers of UNNEST to capture values of arrays within arrays.

SQL

SELECT UNNEST(f_val) FROM (UNNEST(f));

Output

Text

f_val
------------
1
2
5
6
9
4
7
3
8

Expand an Empty Array This example unnests an empty integer array.

SQL

SELECT UNNEST(INT[]());

Output

Text

col_val
------------

The query returns a column of type INT but no rows. Expand an Empty Array Column Without the NULL_INPUT Option This example selects the non-array column a and an empty array column. The query returns no rows because empty or NULL array values are not returned unless you specify the NULL_INPUT option.

SQL

SELECT a, UNNEST(INT[]());

Output

Text

a   col_val
------------

Expand a Column Without the NULL_INPUT Option This query does not include the NULL_INPUT option, which causes the result to omit the NULL array values in column e.

SQL

SELECT a, UNNEST(e);

Output

Text

a   e_val
------------
1   8
2   8

Expand a Column with the NULL_INPUT Option This query includes the NULL_INPUT option, which means that the query returns the NULL values in column e.

SQL

SELECT a, UNNEST(e) WITH NULL_INPUT;

Output

Text

a   e_val
------------
1   8
1   NULL
2   8
2   NULL

Expand Arrays with NULL Rows This example attempts to unnest two arrays, one of which is cast as NULL, while the other is empty. In both cases, the query returns a column of type INTEGER with no rows because there are no values to unnest.

SQL

SELECT UNNEST(CAST(NULL AS INT[]);

SELECT UNNEST(INT[]());

Output

Text

col_val
------------

Other Array Functions

Function	Syntax	Purpose
Constructor	TYPE[](e1, e2, …, en)	Construct an array of SQL type TYPE giving the elements. NULL is also supported as an element.
Constructor (2)	array[e1, e2, …, en]	-compliant constructor. The type of the array is deduced from the elements.
array concatenation	array_cat(array, array)	Concatenate 2 arrays into a new one.
array softmax	softmax(array)	Returns the softmax of the array. The array must be one-dimensional and contain float or double values. The system transfers NULL-valued indices as 0.0.
array cross entropy loss	cross_entropy_loss(array, array)	Returns the cross entropy loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum.
array log loss	log_loss(array, array)	Returns the log loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum.
array logits loss	logits_loss(array, array)	Returns the logits loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum.
array hinge loss	hinge_loss(array, array)	Returns the hinge loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum.
array sum	array_sum(array)	Returns the sum of the array. The array must be one-dimensional and contain numeric values. NULL values do not contribute to the sum.
array min/max	array_min(array), array_max(array)	Returns the corresponding minimum or maximum of the array. This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL.
array argmax	array_argmax(array)	Returns the 1-based position (index) of the maximum element in an array. The first element has index 1. This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL.
array argmin	array_argmin(array)	Returns the 1-based position (index) of the minimum element in an array. The first element has index 1. ** This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL.
array casting	cast_to_array(array, format)	Casts the elements of the array to another type, as specified by the format string. The format is: `'ARRAY(INT)'` for an `int` array, `'ARRAY(CHAR)'` for a `char` array, and so on. Currently, only numeric casts are supported.
scalar position	array_position(array, scalar, pos = 1 )	Returns the position of the first matching scalar in the array. pos indicates the position to start searching, if not specified it defaults to 1.
array elements positions	array_positions(array, array )	Same as array_position(). But this looks for all elements stored in the right array and returns their respective positions in the left array.
array append	array_append(array, value)	Add value to the back of an array
array prepend	array_prepend(value, array)	Add value to the front of an array
array remove	array_remove(array, value)	remove value from the array
array replace	array_replace(array, old_value, new_value)	replace value by another in an array
convert array to string	char(array)	Converts array to its string representation.
convert array to string	array_to_string(array, delimiter [, nullstr])	Converts array to a list of elements separated by ‘delimiter’. Nested arrays are flattened. NULL values are either ignored or replaced by nullstr if provided.
convert string to array	string_to_array(str, format)	Converts the string representation of an array (e.g., `'int[1,2,NULL]'`) into an array. The format string is the same as in cast_to_array.

Function Examples

Function	SQL statement	Result
type constructor	int[](1,2)	int array with values 1 and 2
constructor with no type	array[1,2]	bigint array with values 1 and 2
array_cat	array_cat(array[2, 3], array[4, 5])	array[2, 3, 4, 5]
array_length	array_length(array[2, 3])	2
cast_to_array	cast_to_array(array[2.5, 3.4], ‘ARRAY(INT)‘)	int[](2,3)
array_position	array_position(array[1, 2, 1], 1)	1
	array_position(array[1, 2, 3, 4, 1], 1, 3)	5
array_positions	array_positions(array[1, 2, 3, 4, 1], 1)	int[](1,5)
array_append	array_append(array[1,2], 3)	array[1,2,3]
array_prepend	array_prepend(0, array[1,2])	array[0,1,2]
array_remove	array_remove(array[1,2], 1)	array[2]
array_replace	array_replace(array[1,2,4],4,3)	array[1,2,3]
softmax	softmax(float[](2.0, 2.0, 2.0, NULL, 2.0))	float[0.25, 0.25, 0.25, 0.0, 0.25]
array_sum	array_sum(int[](1, 2, 3))	6
array_min	array_min(int[](1, -2, 3))	-2
array_argmax	array_argmax(int[](1, 3, 2))	2
array_argmin	array_argmin(int[](1, 3, 2))	1
char(<array value>)	char(array[1,2])	‘bigint[1,2]‘
array_to_string	array_to_string(int[1,2,3,NULL,5], ’,’, ’*’)	‘1,2,3,*,5’
string_to_array	string_to_array(‘int[1,2]’, ‘ARRAY(INT)‘)	int[](1,2)

Array Operators

Ocient array operators allow you to concatenate and check for containment or overlap of data. Also, you can retrieve specific elements or slices within arrays.

Do not use array operators, including @>, <@, and &&, to evaluate NULL values in arrays. For information on how to check for NULL values in arrays, see Array NULL Handling.

Contains Operator (`@>`)

The @> operator determines whether a left-side array contains a scalar value or array elements on the right side. @> Syntax

SQL

array @> scalar_or_array

Argument	Data Type	Description
`array`	`ARRAY`	An array column or expression.
`scalar_or_array`	`ARRAY` or any numeric, character, time, or geospatial data type.	A value or an array of values to check whether they are contained in the array. For data types, see Understanding Data Types.

Examples These examples use the @> operator to test whether the left-side array contains all the elements from the right-side array. Array Containment (True Case) This example returns true because all right-side elements are in the left-side array.

SQL

SELECT ARRAY[1, 4, 3] @> ARRAY[3, 1];

Output: true Array Containment (False Case) If the right-side array contains at least one value not present in the left-side array, the query returns false. In this example, the right-side array has one value, 5, not present on the left side.

SQL

SELECT ARRAY[1, 4, 3] @> ARRAY[3, 1, 5];

Output: false Scalar Containment in an Array This example checks whether a single scalar value is in the left-side array.

SQL

SELECT ARRAY[3, 1, 3] @> 1;

Output: true

Contained In Operator (`<@`)

The <@ operator checks whether a right-side array contains all the elements on the left side. Syntax

SQL

scalar_or_array <@ array

Argument	Data Type	Description
`array`	`ARRAY`	An array column or expression.
`scalar_or_array`	`ARRAY` or any numeric, character, time, or geospatial data type.	A value or an array of values to check whether they are contained in the array. For data types, see Understanding Data Types.

Examples Array Contained Within Another Array (True Case) This example returns true because all left-side elements are in the right-side array.

SQL

SELECT VARCHAR[]('apples', 'oranges') <@ VARCHAR[]('apples', 'oranges', 'bananas')

Output: true String Membership Check <@ and other array operators can check whether individual strings are present in an array. The 'oranges' string is present in the right-side array.

SQL

SELECT 'oranges' <@ VARCHAR[]('apples', 'oranges', 'bananas')

Output: true

Overlap Operator (`&&`)

The overlap operator && determines whether any elements between two arrays are common. Syntax

SQL

array1 && array2

Argument	Data Type	Description
`array1`	`ARRAY`	An array column or expression to check whether any of its values are shared with `array2`.
`array2`	`ARRAY`	An array column or expression to check whether any of its values are shared with `array1`.

Examples Check Overlap Between Arrays (True Case) As long as at least one value is present in both arrays, the && operator returns true. Both of these arrays contain the value 3.

SQL

SELECT ARRAY[1 ,2, 3] && ARRAY[3 ,4, 5];

Output: true Check Overlap Between Arrays (False Case) Both of these arrays have no values in common, so the && operator returns false.

SQL

SELECT ARRAY[1 ,2, 3] && ARRAY[4 ,5, 6];

Output: false

Slice Operator (`:`)

The slice operator : returns a subarray ranging from a left index to a right index, both of which are optional to specify. If you exclude both indexes, the slice operator returns the full array. Syntax

SQL

array[left_index:right_index]

Argument	Data Type	Description
`array`	`ARRAY`	An array column or expression.
`left_index`	`INT`	Optional. A starting index for the slice operator to begin the subarray. Index values start at `1`. If you do not specify a left index, or if this value is less than zero, then the slice starts at index `1`.
`right_index`	`INT`	Optional. An ending index for the slice operator to end the subarray. Index values start at `1`. If you do not specify a right index or if the value exceeds the array length, the right index defaults to the array length.

The slice operator also follows these rules:

Each index starts at 1.
If the array or either index value is NULL, the result of slicing is NULL.
Ranges completely out of array bounds return an empty array. For example, array[1, 2][4:5] = [].
Sequential slices slice each dimension of multidimensional arrays. For example, ARRAY[ARRAY[1, 2, 3], ARRAY[4, 5, 6]][1:1][2:3] = ARRAY[ARRAY[2,3]].
More than N sequential slices of an N-dimensional array, for example, three sequential slices on a two-dimensional array return an empty array. For example, ARRAY[1, 2, 3][:][:] = [].
You cannot combine slicing with the access operator when slicing multidimensional arrays. Any access operator [n] converts to [:n]. For example, Array_Val[4][1:6] would be equivalent to Array_Val[:4][1:6].
Slicing an array of tuples such as ARRAY[TUPLE<<INT,INT>>(1,2), TUPLE<<INT,INT>>(3,4)][1:1][2:3] slices [1:1] on the array and [2:3] on the tuple elements within the sliced array, and returns the value TUPLE<<INT>>[TUPLE<<INT>>(2)].

Examples Basic Array Slicing Slice an array with four numbers starting at index 2 and ending at index 3.

SQL

SELECT ARRAY[1 ,2, 3, 4][2:3];

*Output: *['2','3'] Array Slicing With No Right Index The query returns all values after the second value because it does not specify an ending index.

SQL

SELECT ARRAY[1 ,2, 3, 4][2:];

*Output: *['2','3','4'] Array Slicing With No Left Index This query captures a subarray starting at the first index because it does not specify a starting value.

SQL

SELECT ARRAY[1 ,2, 3, 4][:3];

*Output: *['1','2','3']

Array NULL Handling

Filtering with array functions can have different outcomes if they operate on an array containing NULL values or a NULL value of array type. Array comparison operators, such as @>, <@, and &&, do not follow normal Boolean logic when evaluating NULL values. To evaluate arrays for NULL values, use the filter functions FOR_ALL() or FOR_SOME(). For details about these functions, see Array Filters. Examples Evaluate a Single Array With No NULL Values This example evaluates to false because none of the array values are NULL.

SQL

SELECT FOR_SOME(ARRAY[1, 2, 3]) IS NULL;

*Output: *false Evaluate a Single Array With NULL Values This example evaluates to true because the array contains a NULL value.

SQL

SELECT FOR_SOME(ARRAY[1, 2, NULL]) IS NULL;

*Output: *true Select Only Arrays Containing NULL Values To further demonstrate array NULL behavior, these examples use this table loaded with a few array values, some of which are NULL or contain NULL values.

SQL

CREATE TABLE demo_array_table (
    id INT,
    tags VARCHAR[]
);

INSERT INTO demo_array_table (id, tags) VALUES
    (1, VARCHAR[]('alpha', 'beta', 'gamma')),               -- Regular array
    (2, VARCHAR[]('delta', NULL, 'epsilon')),               -- Array containing a NULL element
    (3, NULL),                                              -- Entirely NULL array
    (4, VARCHAR[]()),                                       -- Empty array
    (5, VARCHAR[](NULL, NULL));                             -- Array containing all NULL elements

In this example, the query uses a FOR_SOME function to filter the rows to return only arrays with NULL values. The output does not include the third row of the table because the row itself is NULL, and is not an array containing NULL values.

SQL

SELECT id, tags FROM demo_array_table
    WHERE FOR_SOME(tags) IS NULL;

Output

SQL

| id | tags                             |
|----|----------------------------------|
|  2 | ['delta', NULL, 'epsilon']       |
|  5 | [NULL, NULL]                     |

Select NULL Values or Arrays Containing NULL Values This example includes an additional filter to include any NULL rows and arrays with NULL values. The example uses the demo_array_table table, which has values that are NULL or contain NULL values.

SQL

SELECT id, tags FROM demo_array_table
    WHERE FOR_SOME(tags) IS NULL
    OR tags IS NULL;

Output

SQL

| id | tags                         |
|----|------------------------------|
|  3 | NULL                         |
|  5 | [NULL, NULL]                 |
|  2 | ['delta', NULL, 'epsilon']   |

Select Arrays Without NULL Values This example filters the rows using the FOR_ALL filter function to return only arrays with no NULL values. The output also contains rows with empty arrays. The example uses the demo_array_table table, which has values that are NULL or contain NULL values.

SQL

SELECT id, tags FROM demo_array_table
    WHERE FOR_ALL(tags) IS NOT NULL;

Output

SQL

| id | tags                          |
|----|-------------------------------|
|  4 | []                            |
|  1 | ['alpha', 'beta', 'gamma']    |

Select Only Non-Empty Arrays Without NULL Values To omit empty arrays, you can add a filter to check the array length. The ARRAY_LENGTH function removes any empty arrays. This example uses the demo_array_table table, which has values that are NULL or contain NULL values.

SQL

SELECT id, tags FROM demo_array_table
    WHERE FOR_ALL(tags) IS NOT NULL
    AND ARRAY_LENGTH(tags) > 0;

Output

SQL

| id | tags                          |
|----|-------------------------------|
|  1 | ['alpha', 'beta', 'gamma']    |

Multidimensional arrays

Multidimensional arrays are supported. As seen in these examples, each dimension, or length, at each level does not have to be the same. NULL values are allowed at each dimension of an array. Multidimensional Examples

Function	SQL statement	Result
type constructor two-dimensional	`int[][](int[](1, NULL), array[1])`	two-dimensional `INT` array with values `[1, NULL]` and `[1]`
constructor with no type two-dimensional	`array[int[]\(1), array[int(2)]]`	two-dimensional `INT` array with values `[1]` and `[2]`
two-dimensional array with NULL values	`array[array[1, NULL], NULL]`	two-dimensional `BIGINT` array with values `[1, NULL]` and `NULL`
two-dimensional array with different lengths	`array[array[1], array[1,2], array[1,2,3]]`	two-dimensional `BIGINT` array with values `[1]`, `[1,2]`, and `[1,2,3]`

Matrix Functions and Operators Tuple Functions and Operators Math Functions and Operators Array Data Transformation Functions Data Types Data Types for Data Pipelines Query Ocient

Documentation Index

​ARRAY_CAT_DISTINCT

​ARRAY_DISTINCT

​ARRAY_LENGTH

​ARRAY_SORT

​UNNEST

​Other Array Functions

​Function Examples

​Array Operators

​Contains Operator (@>)

​Contained In Operator (<@)

​Overlap Operator (&&)

​Slice Operator (:)

​Array NULL Handling

​Multidimensional arrays

​Related Links

ARRAY_CAT_DISTINCT

ARRAY_DISTINCT

ARRAY_LENGTH

ARRAY_SORT

UNNEST

Other Array Functions

Function Examples

Array Operators

Contains Operator (`@>`)

Contained In Operator (`<@`)

Overlap Operator (`&&`)

Slice Operator (`:`)

Array NULL Handling

Multidimensional arrays

Related Links