SQL Reference

Array Functions and Operators

UNNEST
Expands each element in an input array into an individual row. 
For example, using 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 [ identifier ]
   | VALUE [ identifier ] 
   | NULL_INPUT }
UNNEST( array_column [, ... ]​ ) 
   [ WITH <unnest_options> ]
   
<unnest_options> ::=
   { ORDINALITY [ identifier ]
   | VALUE [ identifier ] 
   | NULL_INPUT }
﻿
Argument
Data Type
Description
array_column
ARRAY
The geospatial object used to calculate the dimensions. 
<unnest_options>
CHAR
UNNEST supports these optional arguments: 
ORDINALITY [identifier] — Generates a new integer column that corresponds to the index position of each output element in the original array. The identifier value specifies the name of the output ordinality column. If unspecified, this column name defaults to the original column name with the suffix _ord.
VALUE [identifier] — Rename the unnested column to the identifier argument. If unspecified, this column name defaults to the original column name with the suffix _val.
NULL_INPUT — When the input column array is empty or NULL, replace each output row with an array with one NULL element. This replacement is useful for preserving 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]]
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]]
﻿
Example
This example performs a simple UNNEST on column b. 
SQL
SELECT UNNEST(b);
SELECT UNNEST(b);
﻿
Output
Text
b_val  
-----------
1
2
7
5
b_val  
-----------
1
2
7
5
﻿
Example
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;
SELECT UNNEST(b) WITH ORDINALITY;
﻿
Output
Text
b_val   b_ord
--------------------
1       1
2       2
7       1
5       2
b_val   b_ord
--------------------
1       1
2       2
7       1
5       2
﻿
Example
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);
SELECT UNNEST(d);
﻿
Output
Text
d_val 
------------
3
d_val 
------------
3
﻿
Example
UNNEST also does not return array values that are NULL  unless you specify the NULL_INPUT option. 
SQL
SELECT UNNEST(e);
SELECT UNNEST(e);
﻿
Output
Text
e_val 
------------
8
e_val 
------------
8
﻿
Example
The UNNEST function expands only one array layer. 
The empty array remains in the return values.
SQL
SELECT UNNEST(f);
SELECT UNNEST(f);
﻿
Output
Text
f_val 
------------
[1,2]
[5]
[]
[6]
[9,4,7]
[3,8]
f_val 
------------
[1,2]
[5]
[]
[6]
[9,4,7]
[3,8]
﻿
Example
Multiple UNNEST commands in a query produce a per-row Cartesian product of all the unnested values. 
SQL
SELECT UNNEST(b), UNNEST(c);
SELECT UNNEST(b), UNNEST(c);
﻿
Output
Text
b_val   c_val
------------
1       6
1       9
2       6
2       9
7       4
5       4
b_val   c_val
------------
1       6
1       9
2       6
2       9
7       4
5       4
﻿
Example
This example uses UNNEST on the same columns, but in reverse order. 
SQL
SELECT UNNEST(c), UNNEST(b);
SELECT UNNEST(c), UNNEST(b);
﻿
Output
Text
c_val   b_val
------------
6       1
6       2
9       1
9       2
4       7
4       5
c_val   b_val
------------
6       1
6       2
9       1
9       2
4       7
4       5
﻿
Example
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);
SELECT UNNEST(b, c);
﻿
Output
Text
b_val   c_val
------------
1       6
2       9
7       4
5       NULL
b_val   c_val
------------
1       6
2       9
7       4
5       NULL
﻿
Example
This query unnests two array columns. 
SQL
SELECT UNNEST(b, d);
SELECT UNNEST(b, d);
﻿
Output
Text
b_val   d_val
------------
1       NULL
2       NULL
7       3
5       NULL
b_val   d_val
------------
1       NULL
2       NULL
7       3
5       NULL
﻿
﻿Example﻿
This example has two layers of UNNEST to capture values of arrays within arrays. 
SQL
SELECT UNNEST(f_val) FROM (UNNEST(f));
SELECT UNNEST(f_val) FROM (UNNEST(f));
﻿
Output
Text
f_val 
------------
1
2
5
6
9
4
7
3
8

f_val 
------------
1
2
5
6
9
4
7
3
8
﻿
Example
This example unnests an empty integer array. 
SQL
SELECT UNNEST(INT[]());
SELECT UNNEST(INT[]());
﻿
Output
The query returns a column of type INT, but no rows.  
Text
col_val   
------------
col_val   
------------
﻿
Example
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[]()); 
SELECT a, UNNEST(INT[]()); 
﻿
Output
Text
a   col_val   
------------
a   col_val   
------------
﻿
Example
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); 
SELECT a, UNNEST(e); 
﻿
Output
Text
a   e_val   
------------
1   8
2   8
a   e_val   
------------
1   8
2   8
﻿
Example
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; 
SELECT a, UNNEST(e) WITH NULL_INPUT; 
﻿
Output
Text
a   e_val   
------------
1   8
1   NULL
2   8
2   NULL
a   e_val   
------------
1   8
1   NULL
2   8
2   NULL
﻿
Example
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[]());
SELECT UNNEST(CAST(NULL AS INT[]);

SELECT UNNEST(INT[]());
﻿
Output
Text
col_val   
------------
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 length
array_length(array)
Return the number of elements of a given array
array softmax
softmax(array)
Returns the softmax of the array. The array must be 1-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 1-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 1-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 1-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 1-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 1-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 argmin/argmax
array_argmin(array), array_argmax(array)
Returns the corresponding argmin or argmax of the array as a BIGINT index. 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 given by the format string. The format is: 'ARRAY(INT)' for a int array, 'ARRAY(CHAR)' for 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 return 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 seperated 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))
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
Operator
Syntax
Purpose
contains scalar
array @> scalar
Tell whether the array contains the scalar.
contains elements
array @> array
Tell whether the left array contains all elements from right array.
scalar is contained by
scalar <@ array
Tell whether the array contains the scalar.
array is contained by
array <@ array
Tell whether the right array contains all elements from left.
overlap
array && array
Tell whether any of the elements between the arrays are common.
access operator
array[element_index]
Returns the element stored in the given index position. Index starts at 1. Access out of bounds returns a NULL.
slice operator
array[left_index:right_index]
Returns the subarray ranging from left_index to right_index inclusive. Index starts at 1.
About the Array Slice Operator
Each index starts at 1.
If the array or either index value is NULL, the result of slicing is NULL.
If you exclude the left index, it is equivalent to using 1. If you exclude the right index, it is equivalent to len(array). If you exclude both, it returns the full array.
Values below zero for the left index convert to 1 and values beyond the array length for the right index convert to len(array). arrayVal[-1:4] is equivalent to arrayVal[1:4], and int[](1, 2, 3)[1:6] is equivalent to int[](1, 2, 3)[1:3].
Ranges completely out of array bounds return an empty array. array[1, 2][4:5] = array[]
Sequential slices slice each dimension of multidimensional arrays. 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. array[1, 2, 3][:][:] = []
When slicing multidimensional arrays, you cannot combine slicing with the access operator. Any access operator [n] converts to [:n]. arrayVal[4][1:6] = arrayVal[:4][1:6]
Slicing an array of tuples such as tuple<<int,int>>[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)].
Operator Examples
Operator
SQL Predicate
Result
Contains
array[1, 4, 3] @> array[3, 1]
True
﻿
array[1, 4, 3] @> array[3, 1, 3]
True
﻿
array[3, 1, 3] @> 1
True
Is contained by
array[2, 5] <@ array[2, 5, 3, 7, 9]
True
﻿
array[2, 2, 5] <@ array[2, 5, 3, 7, 9]
True
﻿
1 <@ array[2, 5, 1]
True
Overlap
array[1 ,2, 3] && array[1 ,6, 4]
True
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]
Related Links
﻿Matrix Functions and Operators﻿﻿
﻿Tuple Functions and Operators﻿﻿
﻿Math Functions and Operators﻿﻿
﻿Sorted Aggregate Functions﻿﻿
﻿Data Types﻿﻿
﻿Query Ocient﻿﻿

Argument	Data Type	Description
array_column	ARRAY	The geospatial object used to calculate the dimensions.
<unnest_options>	CHAR	UNNEST supports these optional arguments: ORDINALITY [identifier] — Generates a new integer column that corresponds to the index position of each output element in the original array. The identifier value specifies the name of the output ordinality column. If unspecified, this column name defaults to the original column name with the suffix _ord. VALUE [identifier] — Rename the unnested column to the identifier argument. If unspecified, this column name defaults to the original column name with the suffix _val. NULL_INPUT — When the input column array is empty or NULL, replace each output row with an array with one NULL element. This replacement is useful for preserving the contents of other input rows. By default, this option is not enabled.

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 length	array_length(array)	Return the number of elements of a given array
array softmax	softmax(array)	Returns the softmax of the array. The array must be 1-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 1-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 1-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 1-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 1-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 1-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 argmin/argmax	array_argmin(array), array_argmax(array)	Returns the corresponding argmin or argmax of the array as a BIGINT index. 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 given by the format string. The format is: 'ARRAY(INT)' for a int array, 'ARRAY(CHAR)' for 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 return 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 seperated 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	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))	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)

Operator	Syntax	Purpose
contains scalar	array @> scalar	Tell whether the array contains the scalar.
contains elements	array @> array	Tell whether the left array contains all elements from right array.
scalar is contained by	scalar <@ array	Tell whether the array contains the scalar.
array is contained by	array <@ array	Tell whether the right array contains all elements from left.
overlap	array && array	Tell whether any of the elements between the arrays are common.
access operator	array[element_index]	Returns the element stored in the given index position. Index starts at 1. Access out of bounds returns a NULL.
slice operator	array[left_index:right_index]	Returns the subarray ranging from left_index to right_index inclusive. Index starts at 1.

Operator	SQL Predicate	Result
Contains	array[1, 4, 3] @> array[3, 1]	True
	array[1, 4, 3] @> array[3, 1, 3]	True
	array[3, 1, 3] @> 1	True
Is contained by	array[2, 5] <@ array[2, 5, 3, 7, 9]	True
	array[2, 2, 5] <@ array[2, 5, 3, 7, 9]	True
	1 <@ array[2, 5, 1]	True
Overlap	array[1 ,2, 3] && array[1 ,6, 4]	True

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]

Updated 16 May 2024

Did this page help you?

Aggregate Functions

Character / Binary Functions