Search Shortcut cmd + k | ctrl + k
0.10 (stable)
Nested Functions

## List Functions

FunctionDescriptionExampleResultAliases

`flatten(``nested_list``)`

Flatten a nested list by one level

`flatten([[1, 2, 3], [4, 5]])`

`[1, 2, 3, 4, 5]`

`generate_series(``start``, ``stop``, ``step``)`

Create a list of values between start and stop - the stop parameter is inclusive

`generate_series(2, 5, 3)`

`[2, 5]`

`list_aggregate(``list``, ``name``)`

Executes the aggregate function name on the elements of list

`list_aggregate([1, 2, NULL], 'min')`

`1`

`array_aggregate`, `list_aggr`, `array_aggr`, `aggregate`

`list_cosine_similarity(``list1``, ``list2``)`

Compute the cosine similarity between two lists

`list_cosine_similarity([1, 2, 3], [1, 2, 3])`

`1.0`

`<=>`

`list_distance(``list1``, ``list2``)`

Compute the distance between two lists

`list_distance([1, 2, 3], [1, 2, 3])`

`0.0`

`<->`

`list_distinct(``list``)`

Removes all duplicates and NULLs from a list. Does not preserve the original order

`list_distinct([1, 1, NULL, -3, 1, 5])`

`[5, -3, 1]`

`array_distinct`

`list_filter(``list``, ``lambda``)`

Constructs a list from those elements of the input list for which the lambda function returns true

`list_filter([3, 4, 5], x -> x > 4)`

`[5]`

`array_filter`, `filter`

`list_grade_up(``list``)`

Returns the index of their sorted position.

`list_grade_up([3, 6, 1, 2])`

`[3, 4, 1, 2]`

`array_grade_up`, `grade_up`

`list_inner_product(``list1``, ``list2``)`

Compute the inner product between two lists

`list_inner_product([1, 2, 3], [1, 2, 3])`

`14.0`

`list_dot_product`, `<#>`

`list_reduce(``list``, ``lambda``)`

Returns a single value that is the result of applying the lambda function to each element of the input list, starting with the first element and then repeatedly applying the lambda function to the result of the previous application and the next element of the list.

`list_reduce([1, 2, 3], (x, y) -> x + y)`

`6`

`array_reduce`, `reduce`

`list_reverse_sort(``list``)`

Sorts the elements of the list in reverse order

`list_reverse_sort([3, 6, 1, 2])`

`[6, 3, 2, 1]`

`array_reverse_sort`

`list_slice(``list``, ``begin``, ``end[``, ``step]``)`

Extract a sublist using slice conventions. Negative values are accepted

`list_slice(l, 2, 4)`

`array_slice`

`list_sort(``list``)`

Sorts the elements of the list

`list_sort([3, 6, 1, 2])`

`[1, 2, 3, 6]`

`array_sort`

`list_transform(``list``, ``lambda``)`

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

`list_transform([1, 2, 3], x -> x + 1)`

`[2, 3, 4]`

`array_transform`, `list_apply`, `array_apply`, `apply`

`list_unique(``list``)`

Counts the unique elements of a list

`list_unique([1, 1, NULL, -3, 1, 5])`

`3`

`array_unique`

`list_value(``any``, ``...``)`

Create a LIST containing the argument values

`list_value(4, 5, 6)`

`[4, 5, 6]`

`list_pack`

`range(``start``, ``stop``, ``step``)`

Create a list of values between start and stop - the stop parameter is exclusive

`range(2, 5, 3)`

`[2]`

`unpivot_list(``any``, ``...``)`

Identical to list_value, but generated as part of unpivot for better error messages

`unpivot_list(4, 5, 6)`

`[4, 5, 6]`

## Map Functions

FunctionDescriptionExampleResultAliases

`cardinality(``map``)`

Returns the size of the map (or the number of entries in the map)

`cardinality( map([4, 2], ['a', 'b']) );`

`2`

`map(``keys``, ``values``)`

Creates a map from a set of keys and values

`map(['key1', 'key2'], ['val1', 'val2'])`

`{key1=val1, key2=val2}`

`map_concat(``any``, ``...``)`

Returns a map created from merging the input maps, on key collision the value is taken from the last map with that key

`map_concat(map([1,2], ['a', 'b']), map([2,3], ['c', 'd']));`

`{1=a, 2=c, 3=d}`

`map_entries(``map``)`

Returns the map entries as a list of keys/values

`map_entries(map(['key'], ['val']))`

`[{'key': key, 'value': val}]`

`map_extract(``map``, ``key``)`

Returns 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

`map_extract(map(['key'], ['val']), 'key')`

`[val]`

`element_at`

`map_from_entries(``map``)`

Returns a map created from the entries of the array

`map_from_entries([{k: 5, v: 'val1'}, {k: 3, v: 'val2'}]);`

`{5=val1, 3=val2}`

`map_keys(``map``)`

Returns the keys of a map as a list

`map_keys(map(['key'], ['val']))`

`[key]`

`map_values(``map``)`

Returns the values of a map as a list

`map_values(map(['key'], ['val']))`

`[val]`

## Struct Functions

FunctionDescriptionExampleResultAliases

`row(``any``)`

Creates an unnamed STRUCT containing the argument values.

`row(4, 'hello')`

`(4, hello)`

`struct_insert(``struct``, ``any``)`

Adds field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)

`struct_insert({'a': 1}, b := 2)`

`{'a': 1, 'b': 2}`

`struct_pack(``any``)`

Creates a STRUCT containing the argument values. The entry name will be the bound variable name

`struct_pack(i := 4, s := 'string')`

`{'i': 4, 's': string}`

## Union Functions

FunctionDescriptionExampleResultAliases

`union_extract(``union``, ``tag``)`

Extract the value with the named tags from the union. NULL if the tag is not currently selected

`union_extract(s, 'k')`

`union_tag(``union``)`

Retrieve the currently selected tag of the union as an ENUM

`union_tag(union_value(k := 'foo'))`

`k`

`union_value(``tag``)`

Create a single member UNION containing the argument value. The tag of the value will be the bound variable name

`union_value(k := 'hello')`

`hello`