Array Functions and Operators

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 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 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 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 select unnest(b); output 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 select unnest(b) with ordinality; output 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 select unnest(d); output 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 select unnest(e); output 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 select unnest(f); output 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 select unnest(b), unnest(c); output 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 select unnest(c), unnest(b); output 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 select unnest(b, c); output b val c val \ 1 6 2 9 7 4 5 null expand m ultiple array columns this query unnests two array columns select unnest(b, d); output 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 select unnest(f val) from (unnest(f)); output f val \ 1 2 5 6 9 4 7 3 8 expand an empty array this example unnests an empty integer array select unnest(int\[]\()); output 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 select a, unnest(int\[]\()); output 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 select a, unnest(e); output 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 select a, unnest(e) with null input; output 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 select unnest(cast(null as int\[]); select unnest(int\[]\()); output 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] {{postgresql}} 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 specified array 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 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 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)) 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 docid\ dwjpaeks9otz2u sav2lj docid\ xunz45zvbfsvnbhzy99v5 docid\ e2c2vckzh8dfsbp1wngpr docid 6ysvgmcg45ereu6od sxn docid\ ogtviwl gtbgv0chhrh 3 docid 4ycq1d8tkfmlsacorynf6