Skip to main content

Semi-Structured Data

Function NameDescription
ARRAYS_OVERLAPCompares whether two arrays have at least one element in common. Returns true if the arrays have one or more elements in common; otherwise returns false.
ARRAY_AGGAggregates the provided expression into an array.
ARRAY_APPENDAppends an element to the end of an array.
ARRAY_AVGReturns the average of all non-null elements of a list.
ARRAY_CATReturns a concatenation of two arrays.
ARRAY_COMPACTReturns the input array without null values.
ARRAY_CONTAINSReturns whether a list contains a given value.
ARRAY_DISTINCTGiven an input array, returns an equivalent array that includes only distinct elements.
ARRAY_FREQUENCYReturns a map of key-value pairs: keys are the unique elements in the input array and values specify how many times the keys appear in the input array.
ARRAY_GENERATE_RANGEReturns an array of integers in the specified range.
ARRAY_INSERTReturns an array that contains all of the elements from the input array as well as a new element inserted in the specified position.
ARRAY_MAXReturns the maximum value of a list.
ARRAY_MINReturns the minimum value of a list.
ARRAY_POSITIONReturns the index of the first occurrence of an element in an array.
ARRAY_PREPENDPrepends an element to the beginning of an array.
ARRAY_REMOVERemoves all elements that equal a given value from a list.
ARRAY_REMOVE_ATReturns the input array with the element at the specified position removed.
ARRAY_SIZEReturns the size of the input array.
ARRAY_SLICEReturns an array constructed from the specified subset of elements in the input array.
ARRAY_SUMReturns the sum of all non-null elements of a list.
ARRAY_TO_STRINGReturns a string of the values from the input array, with the values separated by the specified delimiter string.
CARDINALITYReturns the number of elements contained in the specified list or map.
FLATTENExplodes compound values into multiple rows. The FLATTEN function takes a LIST column and produces a lateral view (that is, an inline view that contains correlation referring to other tables that precede it in the FROM clause).
MAP_KEYSReturns all keys from a map expression.
MAP_VALUESReturns all values from a map expression.
SET_UNIONGiven two arrays, returns a single array that includes all of the elements in the given arrays, without duplicates.
SUBLISTReturns an array constructed from the specified subset of elements of the input array.