---
title: "List, struct and map functions"
slug: "dev-database-reference-sql-functions-lists-and-structs"
updated: 2026-10-30T18:19:11Z
published: 2026-10-30T18:19:11Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-and-structs"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/develop/docs/dev-database-reference-sql-functions-a-z).

## Size and elements

| Function | What it does |
| --- | --- |
| [array_length](/develop/docs/dev-database-reference-sql-functions-lists-array-length) | Count the elements of a list with array_length, NULL elements included; only the first dimension. |
| [list_extract](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-array-value) | Build a fixed-size array from values or columns with array_value. |
| [generate_series](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-value) (also `list_pack`, `[...]`, `ARRAY[...]`) | Build a list from values or columns with list_value, list_pack, the [...] literal or ARRAY[...]. |
| [range](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-array-pop-back) | Return a list without its last element. |
| [array_pop_front](/develop/docs/dev-database-reference-sql-functions-lists-array-pop-front) | Return a list without its first element. |
| [list_append](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-reverse) (also `array_reverse`) | Return the elements of a list in reverse order; array_reverse is the same function. |
| [list_select](/develop/docs/dev-database-reference-sql-functions-lists-list-select) (also `array_select`) | Pick the elements at a list of positions, in that order; array_select is the same function. |
| [list_where](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-flatten) | Turn a list of lists into one list, one level deep. |
| [list_zip](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-has-all) (also `array_has_all`, `@&gt;`, `&lt;@`) | 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](/develop/docs/dev-database-reference-sql-functions-lists-list-has-any) (also `array_has_any`, `&amp;&amp;`) | Test whether two lists share at least one element; array_has_any and the && operator are the same function. |
| [list_intersect](/develop/docs/dev-database-reference-sql-functions-lists-list-intersect) (also `array_intersect`) | Return the elements two lists have in common, without duplicates; array_intersect is the same function. |
| [list_position](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-distinct) (also `array_distinct`) | Remove duplicate and NULL elements from a list; array_distinct is the same function. |
| [list_unique](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-comprehension) | 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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-avg) | Compute the mean of the elements of a list: one DOUBLE per row, NULL elements skipped. |
| [list_median](/develop/docs/dev-database-reference-sql-functions-lists-list-median) | Find the middle value of the elements of a list: numbers, text and dates, one value per row. |
| [list_min](/develop/docs/dev-database-reference-sql-functions-lists-list-min) | Find the smallest element of a list: numbers, text, dates, booleans or lists, one value per row. |
| [list_mode](/develop/docs/dev-database-reference-sql-functions-lists-list-mode) | Find the most frequent element of a list: numbers, text or lists, one value per row. |
| [list_product](/develop/docs/dev-database-reference-sql-functions-lists-list-product) | Multiply the elements of a list together: one product per row, always returned as DOUBLE. |
| [list_sum](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-lists-list-any-value) | Return the first non-NULL element of a list. |
| [list_count](/develop/docs/dev-database-reference-sql-functions-lists-list-count) | Count the non-NULL elements of a list. |
| [list_first](/develop/docs/dev-database-reference-sql-functions-lists-list-first) | Return the first element of a list, even when it's NULL. |
| [list_last](/develop/docs/dev-database-reference-sql-functions-lists-list-last) | Return the last element of a list, even when it's NULL. |
| [list_max](/develop/docs/dev-database-reference-sql-functions-lists-list-max) | Return the largest non-NULL element of a list. |
| [list_stddev_pop](/develop/docs/dev-database-reference-sql-functions-lists-list-stddev-pop) | Compute the population standard deviation of the numbers in a list. |
| [list_stddev_samp](/develop/docs/dev-database-reference-sql-functions-lists-list-stddev-samp) | Compute the sample standard deviation of the numbers in a list. |
| [list_var_samp](/develop/docs/dev-database-reference-sql-functions-lists-list-var-samp) | Compute the sample variance of the numbers in a list. |
| [list_entropy](/develop/docs/dev-database-reference-sql-functions-lists-list-entropy) | Compute the Shannon entropy, in bits, of the values in a list. |
| [list_histogram](/develop/docs/dev-database-reference-sql-functions-lists-list-histogram) | Count how often each distinct value occurs in a list, as a map. |
| [list_kurtosis_pop](/develop/docs/dev-database-reference-sql-functions-lists-list-kurtosis-pop) | Compute the population excess kurtosis of the elements of a numeric list. |
| [list_kurtosis](/develop/docs/dev-database-reference-sql-functions-lists-list-kurtosis) | Compute the sample excess kurtosis of the elements of a numeric list. |
| [list_mad](/develop/docs/dev-database-reference-sql-functions-lists-list-mad) | Compute the median absolute deviation of the elements of a list. |
| [list_sem](/develop/docs/dev-database-reference-sql-functions-lists-list-sem) | Compute the standard error of the mean of the elements of a numeric list. |
| [list_skewness](/develop/docs/dev-database-reference-sql-functions-lists-list-skewness) | Compute the sample skewness of the elements of a numeric list. |
| [list_var_pop](/develop/docs/dev-database-reference-sql-functions-lists-list-var-pop) | Compute the population variance of the elements of a numeric list. |
| [list_approx_count_distinct](/develop/docs/dev-database-reference-sql-functions-lists-list-approx-count-distinct) | Estimate the number of distinct non-NULL elements in each row's list. |
| [list_bool_and](/develop/docs/dev-database-reference-sql-functions-lists-list-bool-and) | Tell whether every non-NULL element of a BOOLEAN list is true, one result per row. |
| [list_bool_or](/develop/docs/dev-database-reference-sql-functions-lists-list-bool-or) | Tell whether any non-NULL element of a BOOLEAN list is true, one result per row. |
| [list_string_agg](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-map-from-entries) | Build a map from a list of two-field structs, key first and value second. |
| [map](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-map-keys) | Get the keys of a map as a list, in the order the map holds them. |
| [map_values](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-cardinality) | Count the key-value pairs in a map. |

## Search a map

| Function | What it does |
| --- | --- |
| [map_contains_entry](/develop/docs/dev-database-reference-sql-functions-maps-map-contains-entry) | Test whether a map has a key with a given value. |
| [map_contains_value](/develop/docs/dev-database-reference-sql-functions-maps-map-contains-value) | Test whether any key of a map has a given value. |
| [map_contains](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-maps-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](/develop/docs/dev-database-reference-sql-functions-structs-struct-extract-at) | Read one field of a struct by its position. |
| [struct_extract](/develop/docs/dev-database-reference-sql-functions-structs-struct-extract) (also `s.field`, `s[&apos;field&apos;]`, `array_extract(s, &apos;field&apos;)`) | 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](/develop/docs/dev-database-reference-sql-functions-structs-struct-pack) (also `{&apos;name&apos;: value, ...}`) | Build a struct with named fields; the {'name': value} literal is the same. |
| [row](/develop/docs/dev-database-reference-sql-functions-structs-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](/develop/docs/dev-database-reference-sql-functions-structs-struct-concat) | Join the fields of several structs into one struct. |
| [struct_insert](/develop/docs/dev-database-reference-sql-functions-structs-struct-insert) | Add named fields at the end of a struct. |

## Union values

| Function | What it does |
| --- | --- |
| [union_extract](/develop/docs/dev-database-reference-sql-functions-utility-union-extract) | Read one named member of a UNION value; a member the value doesn't hold gives NULL. |
| [union_value](/develop/docs/dev-database-reference-sql-functions-utility-union-value) | Build a value of the UNION type, with one named member, from a value; union values exist only inside a query. |

## See also

- [SQL functions](/develop/docs/dev-database-reference-sql-functions-index) and [all functions A-Z](/develop/docs/dev-database-reference-sql-functions-a-z).