Array Functions and Operators

the {{ocient}} 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 array cat distinct(input array1, input array2 \[, ]) true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples concatenate two arrays and remove duplicates concatenate two arrays and remove duplicate elements within the arrays 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 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 array distinct(input array) true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples remove duplicates from an array deduplicate the contents of the array 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 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 array length(input array) true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example return the length of the array select array length(array\[1,2]); output 2 array sort s orts 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 s orts 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 array sort(input array) true 194,194,194left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type sort an array with a lambda function s orts 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 array sort(input array,function) true 194,194,194left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type the ocient system supports this syntax with the data pipeline functionality only for details, see docid\ ti3mdibvgmuudmlqu9xpl sort an array in the specified order determines how the array handles the order of elements in the array array sort(input array,sort order) true 194,194,194left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 array sort(input array,sort order,nulls first) true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples sort an array sort an array of three elements \[2,3,1] 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 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 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 unnest( array column \[, ]​ ) \[ with \<unnest options> ] \<unnest options> = { ordinality \[ ord identifier ] \| value \[ val identifier ] \| null input } 168,101,371 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 330,331 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 147,193,290 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type function examples 163,221,236 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ xuk0z8dmxpgmogszpdw6w contains operator ( @> ) the @> operator determines whether a left side array contains a scalar value or array elements on the right side @> syntax array @> scalar or array true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 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 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 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 scalar or array <@ array true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples array contained within another array (true case) this example returns true because all left side elements are in the right side array 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 select 'oranges' <@ varchar\[]\('apples', 'oranges', 'bananas') output true overlap operator ( && ) the overlap operator && determines whether any elements between two arrays are common syntax array1 && array2 true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 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 select array\[1 ,2, 3] && array\[4 ,5, 6]; output false slice operator ( ) the slice operator r eturns 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 array\[left index\ right index] true 136,100,100left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 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 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 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 docid\ nw9vavkey2v75moxm muo examples evaluate a single array with no null values this example evaluates to false because none of the array values are null 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 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 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 select id, tags from demo array table where for some(tags) is null; output \| 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 select id, tags from demo array table where for some(tags) is null or tags is null; output \| 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 select id, tags from demo array table where for all(tags) is not null; output \| 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 select id, tags from demo array table where for all(tags) is not null and array length(tags) > 0; output \| 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 true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type related links docid\ nhymyoadealtcppijauc5 docid\ mrrm7nwt xwcg zvmy6 p docid\ saa2re dvaqcs gtjp3ps docid\ xa9jf8l59tk 4facsfczh docid\ ti3mdibvgmuudmlqu9xpl docid\ czxgepf3prq9ufwhwbiuy docid\ s0dywbqubbanzcnc4z9fx