List, struct and map functions

Applies to VAST Cluster 5.5.

Building, reading and changing lists, lambdas, list aggregates, structs, maps, and union values.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Size and elements

Function	What it does
array_length	Count the elements of a list with array_length, NULL elements included; only the first dimension.
list_extract (also `list_element`, `array_extract`, `list[index]`)	Return one element of a list by position with list_extract, list_element, array_extract or l[i].
list_slice (also `array_slice`, `list[begin:end]`, `list[begin:end:step]`)	Return the elements of a list between two positions, optionally with a step with list_slice, array_slice or l[begin:end:step].

Build a list

Function	What it does
array_value	Build a fixed-size array from values or columns with array_value.
generate_series	Build a list of integers or timestamps from a start, a stop and a step with generate_series, which includes the stop value.
list_value (also `list_pack`, `[...]`, `ARRAY[...]`)	Build a list from values or columns with list_value, list_pack, the [...] literal or ARRAY[...].
range	Build a list of integers or timestamps from a start, a stop and a step with range, which stops before the stop value.

Combine and change lists

Function	What it does
list_concat (also `list_cat`, `array_concat`, `array_cat`, `+`, `add`, `\|\|`)	Join two lists into one with list_concat, list_cat, array_concat, array_cat, \|\|, + or add, and how each treats a NULL list.
array_pop_back	Return a list without its last element.
array_pop_front	Return a list without its first element.
list_append (also `array_append`, `array_push_back`)	Add one element at the end of a list; array_append and array_push_back are the same function.
list_prepend (also `array_prepend`, `array_push_front`)	Add one element at the start of a list; array_prepend is the same function, and array_push_front does it with the list first.
list_resize (also `array_resize`)	Cut a list, or pad it, to a given number of elements; array_resize is the same function.
list_reverse (also `array_reverse`)	Return the elements of a list in reverse order; array_reverse is the same function.
list_select (also `array_select`)	Pick the elements at a list of positions, in that order; array_select is the same function.
list_where (also `array_where`)	Keep the elements of a list whose matching entry in a true/false mask is true; array_where is the same function.
array_to_string (also `array_to_string_comma_default(list)`, `array_to_string(list, ',')`)	Join the elements of a list into one VARCHAR, with a separator; array_to_string_comma_default joins with a comma.
flatten	Turn a list of lists into one list, one level deep.
list_zip (also `array_zip`)	Pair up the elements of several lists, position by position, as a list of structs; array_zip is the same function.

Search a list

Function	What it does
list_contains (also `list_has`, `array_contains`, `array_has`, `contains`)	Test whether a list has an element equal to a value; list_has, array_contains, array_has and contains on a list are the same function.
list_has_all (also `array_has_all`, `@>`, `<@`)	Test whether a list contains every element of another list; array_has_all and the @> operator are the same, and <@ tests the other way round.
list_has_any (also `array_has_any`, `&&`)	Test whether two lists share at least one element; array_has_any and the && operator are the same function.
list_intersect (also `array_intersect`)	Return the elements two lists have in common, without duplicates; array_intersect is the same function.
list_position (also `list_indexof`, `array_position`, `array_indexof`)	Return the position of the first element of a list equal to a value; list_indexof, array_position and array_indexof are the same function.
list_distinct (also `array_distinct`)	Remove duplicate and NULL elements from a list; array_distinct is the same function.
list_unique (also `array_unique`)	Count the distinct non-NULL values in a list; array_unique is the same function.

Lambdas

Function	What it does
list_filter (also `array_filter`, `filter`)	Keep the elements of a list for which a lambda is true; array_filter and filter are the same function.
list_reduce (also `array_reduce`, `reduce`)	Fold a list into one value with a lambda, left to right; array_reduce and reduce are the same function.
list_transform (also `list_apply`, `array_transform`, `array_apply`, `apply`)	Apply a lambda to each element of a list and return the results; list_apply, array_transform, array_apply and apply are the same function.
List comprehensions	Build a list from another list with [expression FOR x IN list IF condition]: transform and filter the elements in one expression.

Sort a list

Function	What it does
list_grade_up (also `array_grade_up`, `grade_up`)	Return the positions that would sort a list, to reorder it or another list; array_grade_up and grade_up are the same function.
list_reverse_sort (also `array_reverse_sort`)	Sort the elements of a list in descending order, with NULLs first or last; array_reverse_sort is the same function.
list_sort (also `array_sort`)	Sort the elements of a list in ascending or descending order, with NULLs first or last; array_sort is the same function.

Aggregate a list

Function	What it does
list_aggregate (also `list_aggr`, `array_aggregate`, `array_aggr`, `aggregate`)	Run a named aggregate, such as sum, max, median or string_agg, over the elements of a list; list_aggr, array_aggregate, array_aggr and aggregate are the same function.
list_avg	Compute the mean of the elements of a list: one DOUBLE per row, NULL elements skipped.
list_median	Find the middle value of the elements of a list: numbers, text and dates, one value per row.
list_min	Find the smallest element of a list: numbers, text, dates, booleans or lists, one value per row.
list_mode	Find the most frequent element of a list: numbers, text or lists, one value per row.
list_product	Multiply the elements of a list together: one product per row, always returned as DOUBLE.
list_sum	Add up the elements of a list: one sum per row, HUGEINT for integer lists, DECIMAL for decimals and DOUBLE for doubles.
list_any_value	Return the first non-NULL element of a list.
list_count	Count the non-NULL elements of a list.
list_first	Return the first element of a list, even when it's NULL.
list_last	Return the last element of a list, even when it's NULL.
list_max	Return the largest non-NULL element of a list.
list_stddev_pop	Compute the population standard deviation of the numbers in a list.
list_stddev_samp	Compute the sample standard deviation of the numbers in a list.
list_var_samp	Compute the sample variance of the numbers in a list.
list_entropy	Compute the Shannon entropy, in bits, of the values in a list.
list_histogram	Count how often each distinct value occurs in a list, as a map.
list_kurtosis_pop	Compute the population excess kurtosis of the elements of a numeric list.
list_kurtosis	Compute the sample excess kurtosis of the elements of a numeric list.
list_mad	Compute the median absolute deviation of the elements of a list.
list_sem	Compute the standard error of the mean of the elements of a numeric list.
list_skewness	Compute the sample skewness of the elements of a numeric list.
list_var_pop	Compute the population variance of the elements of a numeric list.
list_approx_count_distinct	Estimate the number of distinct non-NULL elements in each row's list.
list_bool_and	Tell whether every non-NULL element of a BOOLEAN list is true, one result per row.
list_bool_or	Tell whether any non-NULL element of a BOOLEAN list is true, one result per row.
list_string_agg	Join the non-NULL elements of each row's list into one comma-separated string.

Build a map

Function	What it does
map_concat	Merge two or more maps into one; where a key is in several maps, the last map's value wins.
map_from_entries	Build a map from a list of two-field structs, key first and value second.
map	Build a map from a list of keys and a list of values, or from a MAP {key: value} literal.

Keys, values and entries

Function	What it does
map_entries	Get the pairs of a map as a list of structs with fields key and value, to filter or change them or to store a map in a table.
map_keys	Get the keys of a map as a list, in the order the map holds them.
map_values	Get the values of a map as a list, in the order the map holds them.

Look up a key

Function	What it does
map_extract_value (also `m[key]`)	Look up a key in a map and get its value, or NULL when the key isn't there; m[key] is the same.
map_extract (also `element_at`)	Look up a key in a map and get its value inside a list, empty when the key isn't there; element_at is the same function.

Size

Function	What it does
cardinality	Count the key-value pairs in a map.

Search a map

Function	What it does
map_contains_entry	Test whether a map has a key with a given value.
map_contains_value	Test whether any key of a map has a given value.
map_contains (also `contains`)	Test whether a map has a key; contains with a map is the same function.

PostgreSQL compatibility

Function	What it does
map_to_pg_oid (also `pg_catalog.map_to_pg_oid`)	Look up the PostgreSQL type OID for a PostgreSQL type name; it returns a value for few names, so use typeof or DESCRIBE to find a type.

Read a field

Function	What it does
struct_extract_at	Read one field of a struct by its position.
struct_extract (also `s.field`, `s['field']`, `array_extract(s, 'field')`)	Read one field of a struct by name; s.field, s['field'] and array_extract on a struct are the same function.

Build a struct

Function	What it does
struct_pack (also `{'name': value, ...}`)	Build a struct with named fields; the {'name': value} literal is the same.
row (also `(a, b, ...)`)	Build a struct with unnamed fields from a list of values; the tuple (a, b) is the same.

Add fields

Function	What it does
struct_concat	Join the fields of several structs into one struct.
struct_insert	Add named fields at the end of a struct.

Union values

Function	What it does
union_extract	Read one named member of a UNION value; a member the value doesn't hold gives NULL.
union_value	Build a value of the UNION type, with one named member, from a value; union values exist only inside a query.