This section describes functions and operators for examining and manipulating nested values. There are three nested data types: lists, structs, and maps.
List Functions
In the descriptions, l is the three element list [4, 5, 6].
| Function | Description | Example | Result |
|---|---|---|---|
list[index] |
Bracket notation serves as an alias for list_extract. |
l[2] |
6 |
list_extract(list, index) |
Extract the indexth (0-based) value from the list. |
list_extract(l, 2) |
6 |
list_element(list, index) |
Alias for list_extract. |
list_element(l, 2) |
6 |
array_extract(list, index) |
Alias for list_extract. |
array_extract(l, 2) |
6 |
list[begin:end] |
Bracket notation with colon is an alias for list_slice. Missing arguments are interpreted as NULLs. |
l[1:2] |
[5, 6] |
list_slice(list, begin, end) |
Extract a sublist using slice conventions. NULLs are interpreted as the bounds of the LIST. Negative values are accepted. |
list_slice(l, 1, NULL) |
[5,6] |
array_slice(list, begin, end) |
Alias for list_slice. | array_slice(l, 1, NULL) |
[5,6] |
list_value(any, ...) |
Create a LIST containing the argument values. |
list_value(4, 5, 6) |
[4, 5, 6] |
list_pack(any, ...) |
Alias for list_value. |
list_pack(4, 5, 6) |
[4, 5, 6] |
len(list) |
Return the length of the list. | len([1, 2, 3]) |
3 |
array_length(list) |
Alias for len. |
array_length([1, 2, 3]) |
3 |
unnest(list) |
Unnests a list by one level. Note that this is a special function that alters the cardinality of the result. See the UNNEST page for more details. | unnest([1, 2, 3]) |
1, 2, 3 |
list_concat(list1, list2) |
Concatenates two lists. | list_concat([2, 3], [4, 5, 6]) |
[2, 3, 4, 5, 6] |
list_cat(list1, list2) |
Alias for list_concat. |
list_cat([2, 3], [4, 5, 6]) |
[2, 3, 4, 5, 6] |
array_concat(list1, list2) |
Alias for list_concat. |
array_concat([2, 3], [4, 5, 6]) |
[2, 3, 4, 5, 6] |
array_cat(list1, list2) |
Alias for list_concat. |
array_cat([2, 3], [4, 5, 6]) |
[2, 3, 4, 5, 6] |
list_prepend(element, list) |
Prepends element to list. |
list_prepend(3, [4, 5, 6]) |
[3, 4, 5, 6] |
array_prepend(element, list) |
Alias for list_prepend. |
array_prepend(3, [4, 5, 6]) |
[3, 4, 5, 6] |
list_append(list, element) |
Appends element to list. |
list_append([2, 3], 4) |
[2, 3, 4] |
array_append(list, element) |
Alias for list_append. |
array_append([2, 3], 4) |
[2, 3, 4] |
Struct Functions
| Function | Description | Example | Result |
|---|---|---|---|
struct.entry |
Dot notation serves as an alias for struct_extract. |
({'i': 3, 's': 'string'}).s |
string |
struct[entry] |
Bracket notation serves as an alias for struct_extract. |
({'i': 3, 's': 'string'})['s'] |
string |
row(any, ...) |
Create a STRUCT containing the argument values. If the values are column references, the entry name will be the column name; otherwise it will be the string 'vN' where N is the (1-based) position of the argument. |
row(i, i % 4, i / 4) |
{'i': 3, 'v2': 3, 'v3': 0} |
struct_extract(struct, 'entry') |
Extract the named entry from the struct. | struct_extract(s, 'i') |
4 |
struct_pack(name := any, ...) |
Create a STRUCT containing the argument values. The entry name will be the bound variable name. |
struct_pack(i := 4, s := 'string') |
{'i': 3, 's': 'string'} |
Map Functions
| Function | Description | Example | Result |
|---|---|---|---|
map[entry] |
Alias for element_at |
map([100, 5], ['a', 'b'])[100] |
[a] |
element_at(map, key) |
Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map’s keys else an error is returned. | element_at(map([100, 5], [42, 43]),100); |
[42] |
cardinality(map) |
Return the size of the map (or the number of entries in the map). | cardinality( map([4, 2], ['a', 'b']) ); |
2 |
map() |
Returns an empty map. | map() |
{} |
Range Functions
The functions range and generate_series create a list of values in the range between start and stop.
The start parameter is inclusive.
For the range function, the stop parameter is exclusive, while for generate_series, it is inclusive.
Based on the number of arguments, the following variants exist:
range(start,stop,step)range(start,stop)range(stop)generate_series(start,stop,step)generate_series(start,stop)generate_series(stop)
The default value of start is 0 and the default value of step is 1.
SELECT range(5);
-- [0, 1, 2, 3, 4]
SELECT range(2, 5);
-- [2, 3, 4]
SELECT range(2, 5, 3);
-- [2]
SELECT generate_series(5);
-- [0, 1, 2, 3, 4, 5]
SELECT generate_series(2, 5);
-- [2, 3, 4, 5]
SELECT generate_series(2, 5, 3);
-- [2, 5]
Date ranges are also supported:
SELECT * FROM range(date '1992-01-01', date '1992-03-01', interval '1' month);
┌─────────────────────┐
│ range │
├─────────────────────┤
│ 1992-01-01 00:00:00 │
│ 1992-02-01 00:00:00 │
└─────────────────────┘
generate_subscripts
The generate_subscript(arr, dim) function generates indexes along the dimth dimension of array arr.
SELECT generate_subscripts([4,5,6], 1) AS i;
┌───┐
│ i │
├───┤
│ 1 │
│ 2 │
│ 3 │
└───┘
Related Functions
There are also aggregate functions list and histogram that produces lists and lists of structs.