Nested Functions
Page Source
Version dev
Version:

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_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_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]

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

Search Shortcut cmd + k | ctrl + k