Edit this page

List Functions

Function	Description
`list[index]`	Extracts a single list element using a (1-based) `index`.
`list[begin[:end][:step]]`	Extracts a sublist using slice conventions. Negative values are accepted.
`list1 && list2`	Alias for `list_has_any`.
`list1 <-> list2`	Alias for `list_distance`.
`list1 <=> list2`	Alias for `list_cosine_distance`.
`list1 <@ list2`	Alias for `list_has_all`.
`list1 @> list2`	Alias for `list_has_all`.
`arg1 \|\| arg2`	Concatenates two strings, lists, or blobs. Any `NULL` input results in `NULL`. See also `concat(arg1, arg2, ...)` and `list_concat(list1, list2, ...)`.
`aggregate(list, function_name, ...)`	Alias for `list_aggregate`.
`apply(list, lambda(x))`	Alias for `list_transform`.
`array_aggr(list, function_name, ...)`	Alias for `list_aggregate`.
`array_aggregate(list, function_name, ...)`	Alias for `list_aggregate`.
`array_append(list, element)`	Alias for `list_append`.
`array_apply(list, lambda(x))`	Alias for `list_transform`.
`array_cat(list_1, ..., list_n)`	Alias for `list_concat`.
`array_concat(list_1, ..., list_n)`	Alias for `list_concat`.
`array_contains(list, element)`	Alias for `list_contains`.
`array_distinct(list)`	Alias for `list_distinct`.
`array_extract(list, index)`	Extracts the `index`th (1-based) value from the `list`.
`array_filter(list, lambda(x))`	Alias for `list_filter`.
`array_grade_up(list[, col1][, col2])`	Alias for `list_grade_up`.
`array_has(list, element)`	Alias for `list_contains`.
`array_has_all(list1, list2)`	Alias for `list_has_all`.
`array_has_any(list1, list2)`	Alias for `list_has_any`.
`array_indexof(list, element)`	Alias for `list_position`.
`array_intersect(list1, list2)`	Alias for `list_intersect`.
`array_length(list)`	Alias for `length`.
`array_pop_back(list)`	Returns the `list` without the last element.
`array_pop_front(list)`	Returns the `list` without the first element.
`array_position(list, element)`	Alias for `list_position`.
`array_prepend(element, list)`	Alias for `list_prepend`.
`array_push_back(list, element)`	Alias for `list_append`.
`array_push_front(list, element)`	Prepends `element` to `list`.
`array_reduce(list, lambda(x,y)[, initial_value])`	Alias for `list_reduce`.
`array_resize(list, size[[, value]])`	Alias for `list_resize`.
`array_reverse(list)`	Alias for `list_reverse`.
`array_reverse_sort(list[, col1])`	Alias for `list_reverse_sort`.
`array_select(value_list, index_list)`	Alias for `list_select`.
`array_slice(list, begin, end)`	Alias for `list_slice`.
`array_slice(list, begin, end, step)`	Alias for `list_slice`.
`array_sort(list[, col1][, col2])`	Alias for `list_sort`.
`array_to_string(list, delimiter)`	Concatenates list/array elements using an optional `delimiter`.
`array_to_string_comma_default(array)`	Concatenates list/array elements with a comma delimiter.
`array_transform(list, lambda(x))`	Alias for `list_transform`.
`array_unique(list)`	Alias for `list_unique`.
`array_where(value_list, mask_list)`	Alias for `list_where`.
`array_zip(list_1, ..., list_n[, truncate])`	Alias for `list_zip`.
`char_length(list)`	Alias for `length`.
`character_length(list)`	Alias for `length`.
`concat(value, ...)`	Concatenates multiple strings or lists. `NULL` inputs are skipped. See also operator `\|\|`.
`contains(list, element)`	Returns `true` if the `list` contains the `element`.
`filter(list, lambda(x))`	Alias for `list_filter`.
`flatten(nested_list)`	Flattens a nested list by one level.
`generate_series(start[, stop][, step])`	Creates a list of values between `start` and `stop` - the stop parameter is inclusive.
`grade_up(list[, col1][, col2])`	Alias for `list_grade_up`.
`len(list)`	Alias for `length`.
`length(list)`	Returns the length of the `list`.
`list_aggr(list, function_name, ...)`	Alias for `list_aggregate`.
`list_aggregate(list, function_name, ...)`	Executes the aggregate function `function_name` on the elements of `list`. See the List Aggregates section for more details.
`list_any_value(list)`	Applies aggregate function `any_value` to the `list`.
`list_append(list, element)`	Appends `element` to `list`.
`list_apply(list, lambda(x))`	Alias for `list_transform`.
`list_approx_count_distinct(list)`	Applies aggregate function `approx_count_distinct` to the `list`.
`list_avg(list)`	Applies aggregate function `avg` to the `list`.
`list_bit_and(list)`	Applies aggregate function `bit_and` to the `list`.
`list_bit_or(list)`	Applies aggregate function `bit_or` to the `list`.
`list_bit_xor(list)`	Applies aggregate function `bit_xor` to the `list`.
`list_bool_and(list)`	Applies aggregate function `bool_and` to the `list`.
`list_bool_or(list)`	Applies aggregate function `bool_or` to the `list`.
`list_cat(list_1, ..., list_n)`	Alias for `list_concat`.
`list_concat(list_1, ..., list_n)`	Concatenates lists. `NULL` inputs are skipped. See also operator `\|\|`.
`list_contains(list, element)`	Returns true if the list contains the element.
`list_cosine_distance(list1, list2)`	Computes the cosine distance between two same-sized lists.
`list_cosine_similarity(list1, list2)`	Computes the cosine similarity between two same-sized lists.
`list_count(list)`	Applies aggregate function `count` to the `list`.
`list_distance(list1, list2)`	Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
`list_distinct(list)`	Removes all duplicates and `NULL` values from a list. Does not preserve the original order.
`list_dot_product(list1, list2)`	Alias for `list_inner_product`.
`list_element(list, index)`	Alias for `list_extract`.
`list_entropy(list)`	Applies aggregate function `entropy` to the `list`.
`list_extract(list, index)`	Extract the `index`th (1-based) value from the list.
`list_filter(list, lambda(x))`	Constructs a list from those elements of the input `list` for which the `lambda` function returns `true`. SereneDB must be able to cast the `lambda` function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See `list_filter` examples.
`list_first(list)`	Applies aggregate function `first` to the `list`.
`list_grade_up(list[, col1][, col2])`	Works like `list_sort`, but the results are the indexes that correspond to the position in the original list instead of the actual values.
`list_has(list, element)`	Alias for `list_contains`.
`list_has_all(list1, list2)`	Returns true if all elements of list2 are in list1. NULLs are ignored.
`list_has_any(list1, list2)`	Returns true if the lists have any element in common. NULLs are ignored.
`list_histogram(list)`	Applies aggregate function `histogram` to the `list`.
`list_indexof(list, element)`	Alias for `list_position`.
`list_inner_product(list1, list2)`	Computes the inner product between two same-sized lists.
`list_intersect(list1, list2)`	Returns a list of all the elements that exist in both `list1` and `list2`, without duplicates.
`list_kurtosis(list)`	Applies aggregate function `kurtosis` to the `list`.
`list_kurtosis_pop(list)`	Applies aggregate function `kurtosis_pop` to the `list`.
`list_last(list)`	Applies aggregate function `last` to the `list`.
`list_mad(list)`	Applies aggregate function `mad` to the `list`.
`list_max(list)`	Applies aggregate function `max` to the `list`.
`list_median(list)`	Applies aggregate function `median` to the `list`.
`list_min(list)`	Applies aggregate function `min` to the `list`.
`list_mode(list)`	Applies aggregate function `mode` to the `list`.
`list_negative_dot_product(list1, list2)`	Alias for `list_negative_inner_product`.
`list_negative_inner_product(list1, list2)`	Computes the negative inner product between two same-sized lists.
`list_pack(arg, ...)`	Alias for `list_value`.
`list_position(list, element)`	Returns the index of the `element` if the `list` contains the `element`. If the `element` is not found, it returns `NULL`.
`list_prepend(element, list)`	Prepends `element` to `list`.
`list_product(list)`	Applies aggregate function `product` to the `list`.
`list_reduce(list, lambda(x,y)[, initial_value])`	Reduces all elements of the input `list` into a single scalar value by executing the `lambda` function on a running result and the next list element. The `lambda` function has an optional `initial_value` argument. See `list_reduce` examples.
`list_resize(list, size[[, value]])`	Resizes the `list` to contain `size` elements. Initializes new elements with `value` or `NULL` if `value` is not set.
`list_reverse(list)`	Reverses the `list`.
`list_reverse_sort(list[, col1])`	Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about sorting order and `NULL` values.
`list_select(value_list, index_list)`	Returns a list based on the elements selected by the `index_list`.
`list_sem(list)`	Applies aggregate function `sem` to the `list`.
`list_skewness(list)`	Applies aggregate function `skewness` to the `list`.
`list_slice(list, begin, end)`	Extracts a sublist or substring using slice conventions. Negative values are accepted.
`list_slice(list, begin, end, step)`	list_slice with added step feature.
`list_sort(list[, col1][, col2])`	Sorts the elements of the list. See the Sorting Lists section for more details about sorting order and `NULL` values.
`list_stddev_pop(list)`	Applies aggregate function `stddev_pop` to the `list`.
`list_stddev_samp(list)`	Applies aggregate function `stddev_samp` to the `list`.
`list_string_agg(list)`	Applies aggregate function `string_agg` to the `list`.
`list_sum(list)`	Applies aggregate function `sum` to the `list`.
`list_transform(list, lambda(x))`	Returns a list that is the result of applying the `lambda` function to each element of the input `list`. The return type is defined by the return type of the `lambda` function. See `list_transform` examples.
`list_unique(list)`	Counts the unique elements of a `list`.
`list_value(arg, ...)`	Creates a LIST containing the argument values.
`list_var_pop(list)`	Applies aggregate function `var_pop` to the `list`.
`list_var_samp(list)`	Applies aggregate function `var_samp` to the `list`.
`list_where(value_list, mask_list)`	Returns a list with the `BOOLEAN`s in `mask_list` applied as a mask to the `value_list`.
`list_zip(list_1, ..., list_n[, truncate])`	Zips n `LIST`s to a new `LIST` whose length will be that of the longest list. Its elements are structs of n elements from each list `list_1`, …, `list_n`, missing elements are replaced with `NULL`. If `truncate` is set, all lists are truncated to the smallest list length.
`range(start[, stop][, step])`	Creates a list of values between `start` and `stop` - the stop parameter is exclusive.
`reduce(list, lambda(x,y)[, initial_value])`	Alias for `list_reduce`.
`repeat(list, count)`	Repeats the `list` `count` number of times.
`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.
`unpivot_list(arg, ...)`	Identical to list_value, but generated as part of unpivot for better error messages.

`list[index]`

Extracts a single list element using a (1-based) index. Alias: list_extract.

Query

SELECT [4, 5, 6][3] AS list_index;

Result

 list_index------------          6

`list[begin[:end][:step]]`

Extracts a sublist using slice conventions. Negative values are accepted. Alias: list_slice.

Query

SELECT [4, 5, 6][3] AS list_slice_bracket;

Result

 list_slice_bracket--------------------                  6

`arg1 || arg2`

Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...).

Query

SELECT 'Serene' || 'DB' AS concat;

Result

 concat---------- SereneDB

Query

SELECT [1, 2, 3] || [4, 5, 6] AS concat;

Result

 concat--------------- {1,2,3,4,5,6}

Query

SELECT '\xAA'::BLOB || '\xBB'::BLOB AS concat;

Result

 concat--------- \\xaabb

`array_extract(list, index)`

Extracts the indexth (1-based) value from the list.

Query

SELECT array_extract([4, 5, 6], 3) AS array_extract;

Result

 array_extract---------------             6

`array_pop_back(list)`

Returns the list without the last element.

Query

SELECT array_pop_back([4, 5, 6]) AS array_pop_back;

Result

 array_pop_back---------------- {4,5}

`array_pop_front(list)`

Returns the list without the first element.

Query

SELECT array_pop_front([4, 5, 6]) AS array_pop_front;

Result

 array_pop_front----------------- {5,6}

`array_push_front(list, element)`

Prepends element to list.

Query

SELECT array_push_front([4, 5, 6], 3) AS array_push_front;

Result

 array_push_front------------------ {3,4,5,6}

`array_to_string(list, delimiter)`

Concatenates list/array elements using an optional delimiter.

Query

SELECT array_to_string([1, 2, 3], '-') AS str;

Result

 str------- 1-2-3

Query

SELECT array_to_string(['aa', 'bb', 'cc'], '') AS str;

Result

 str-------- aabbcc

`array_to_string_comma_default(array)`

Concatenates list/array elements with a comma delimiter.

Query

SELECT array_to_string_comma_default(['Banana', 'Apple', 'Melon']) AS str;

Result

 str-------------------- Banana,Apple,Melon

`concat(value, ...)`

Concatenates multiple strings or lists. NULL inputs are skipped. See also operator ||.

Query

SELECT concat('Hello', ' ', 'World') AS concat;

Result

 concat------------- Hello World

Query

SELECT concat([1, 2, 3], NULL, [4, 5, 6]) AS concat;

Result

 concat--------------- {1,2,3,4,5,6}

`contains(list, element)`

Returns true if the list contains the element.

Query

SELECT contains([1, 2, NULL], 1) AS contains;

Result

 contains---------- t

`flatten(nested_list)`

Flattens a nested list by one level.

Query

SELECT flatten([[1, 2, 3], [4, 5]]) AS flatten;

Result

 flatten------------- {1,2,3,4,5}

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

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

Query

SELECT generate_series(2, 5, 3) AS generate_series;

Result

 generate_series-----------------               2               5

`length(list)`

Returns the length of the list. Aliases: char_length, character_length, len.

Query

SELECT length([1, 2, 3]) AS length;

Result

 length--------      3

`list_aggregate(list, function_name, ...)`

Executes the aggregate function function_name on the elements of list. See the List Aggregates section for more details. Aliases: aggregate, array_aggr, array_aggregate, list_aggr.

Query

SELECT list_aggregate([1, 2, NULL], 'min') AS list_aggregate;

Result

 list_aggregate----------------              1

`list_any_value(list)`

Applies aggregate function any_value to the list.

Query

SELECT list_any_value([3, 3, 9]) AS list_any_value;

Result

 list_any_value----------------              3

`list_append(list, element)`

Appends element to list. Aliases: array_append, array_push_back.

Query

SELECT list_append([2, 3], 4) AS list_append;

Result

 list_append------------- {2,3,4}

`list_approx_count_distinct(list)`

Applies aggregate function approx_count_distinct to the list.

Query

SELECT list_approx_count_distinct([3, 3, 9]) AS list_approx_count_distinct;

Result

 list_approx_count_distinct----------------------------                          2

`list_avg(list)`

Applies aggregate function avg to the list.

Query

SELECT list_avg([3, 3, 9]) AS list_avg;

Result

 list_avg----------        5

`list_bit_and(list)`

Applies aggregate function bit_and to the list.

Query

SELECT list_bit_and([3, 3, 9]) AS list_bit_and;

Result

 list_bit_and--------------            1

`list_bit_or(list)`

Applies aggregate function bit_or to the list.

Query

SELECT list_bit_or([3, 3, 9]) AS list_bit_or;

Result

 list_bit_or-------------          11

`list_bit_xor(list)`

Applies aggregate function bit_xor to the list.

Query

SELECT list_bit_xor([3, 3, 9]) AS list_bit_xor;

Result

 list_bit_xor--------------            9

`list_bool_and(list)`

Applies aggregate function bool_and to the list.

Query

SELECT list_bool_and([true, false]) AS list_bool_and;

Result

 list_bool_and--------------- f

`list_bool_or(list)`

Applies aggregate function bool_or to the list.

Query

SELECT list_bool_or([true, false]) AS list_bool_or;

Result

 list_bool_or-------------- t

`list_concat(list_1, ..., list_n)`

Concatenates lists. NULL inputs are skipped. See also operator ||. Aliases: list_cat, array_concat, array_cat.

Query

SELECT list_concat([2, 3], [4, 5, 6], [7]) AS list_concat;

Result

 list_concat--------------- {2,3,4,5,6,7}

`list_contains(list, element)`

Returns true if the list contains the element. Aliases: array_contains, array_has, list_has.

Query

SELECT list_contains([1, 2, NULL], 1) AS list_contains;

Result

 list_contains--------------- t

`list_cosine_distance(list1, list2)`

Computes the cosine distance between two same-sized lists. Alias: <=>.

Query

SELECT list_cosine_distance([1, 2, 3], [1, 2, 3]) AS list_cosine_distance;

Result

 list_cosine_distance----------------------                    0

`list_cosine_similarity(list1, list2)`

Computes the cosine similarity between two same-sized lists.

Query

SELECT list_cosine_similarity([1, 2, 3], [1, 2, 3]) AS list_cosine_similarity;

Result

 list_cosine_similarity------------------------                      1

`list_count(list)`

Applies aggregate function count to the list.

Query

SELECT list_count([3, 3, 9]) AS list_count;

Result

 list_count------------          3

`list_distance(list1, list2)`

Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length. Alias: <->.

Query

SELECT list_distance([1, 2, 3], [1, 2, 5]) AS list_distance;

Result

 list_distance---------------             2

`list_distinct(list)`

Removes all duplicates and NULL values from a list. Does not preserve the original order. Alias: array_distinct.

Query

SELECT list_sort(list_distinct([1, 1, NULL, -3, 1, 5])) AS list_distinct;

Result

 list_distinct--------------- {-3,1,5}

`list_entropy(list)`

Applies aggregate function entropy to the list.

Query

SELECT round(list_entropy([3, 3, 9]), 10) AS list_entropy;

Result

 list_entropy-------------- 0.9182958341

`list_extract(list, index)`

Extract the indexth (1-based) value from the list. Alias: list_element.

Query

SELECT list_extract([4, 5, 6], 3) AS list_extract;

Result

 list_extract--------------            6

`list_filter(list, lambda(x))`

Constructs a list from those elements of the input list for which the lambda function returns true. SereneDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's. See list_filter examples. Aliases: array_filter, filter.

Query

SELECT list_filter([3, 4, 5], lambda x : x > 4) AS list_filter;

Result

 list_filter------------- {5}

`list_first(list)`

Applies aggregate function first to the list.

Query

SELECT list_first([3, 3, 9]) AS list_first;

Result

 list_first------------          3

`list_grade_up(list[, col1][, col2])`

Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values. Aliases: array_grade_up, grade_up.

Query

SELECT list_grade_up([3, 6, 1, 2]) AS list_grade_up;

Result

 list_grade_up--------------- {3,4,1,2}

`list_has_all(list1, list2)`

Returns true if all elements of list2 are in list1. NULLs are ignored. Aliases: <@, @>, array_has_all.

Query

SELECT list_has_all([1, 2, 3], [2, 3]) AS list_has_all;

Result

 list_has_all-------------- t

`list_has_any(list1, list2)`

Returns true if the lists have any element in common. NULLs are ignored. Aliases: &&, array_has_any.

Query

SELECT list_has_any([1, 2, 3], [2, 3, 4]) AS list_has_any;

Result

 list_has_any-------------- t

`list_histogram(list)`

Applies aggregate function histogram to the list.

Query

SELECT list_histogram([3, 3, 9]) AS list_histogram;

Result

 list_histogram------------------- {"(3,2)","(9,1)"}

`list_inner_product(list1, list2)`

Computes the inner product between two same-sized lists. Alias: list_dot_product.

Query

SELECT list_inner_product([1, 2, 3], [1, 2, 3]) AS list_inner_product;

Result

 list_inner_product--------------------                 14

`list_intersect(list1, list2)`

Returns a list of all the elements that exist in both list1 and list2, without duplicates. Alias: array_intersect.

Query

SELECT list_sort(list_intersect([1, 2, 3], [2, 3, 4])) AS list_intersect;

Result

 list_intersect---------------- {2,3}

`list_kurtosis(list)`

Applies aggregate function kurtosis to the list.

Query

SELECT list_kurtosis([3, 3, 9]) AS list_kurtosis;

Result

 list_kurtosis--------------- NULL

`list_kurtosis_pop(list)`

Applies aggregate function kurtosis_pop to the list.

Query

SELECT list_kurtosis_pop([3, 3, 9]) AS list_kurtosis_pop;

Result

 list_kurtosis_pop--------------------- -1.4999999999999978

`list_last(list)`

Applies aggregate function last to the list.

Query

SELECT list_last([3, 3, 9]) AS list_last;

Result

 list_last-----------         9

`list_mad(list)`

Applies aggregate function mad to the list.

Query

SELECT list_mad([3, 3, 9]) AS list_mad;

Result

 list_mad----------        0

`list_max(list)`

Applies aggregate function max to the list.

Query

SELECT list_max([3, 3, 9]) AS list_max;

Result

 list_max----------        9

`list_median(list)`

Applies aggregate function median to the list.

Query

SELECT list_median([3, 3, 9]) AS list_median;

Result

 list_median-------------           3

`list_min(list)`

Applies aggregate function min to the list.

Query

SELECT list_min([3, 3, 9]) AS list_min;

Result

 list_min----------        3

`list_mode(list)`

Applies aggregate function mode to the list.

Query

SELECT list_mode([3, 3, 9]) AS list_mode;

Result

 list_mode-----------         3

`list_negative_inner_product(list1, list2)`

Computes the negative inner product between two same-sized lists. Alias: list_negative_dot_product.

Query

SELECT list_negative_inner_product([1, 2, 3], [1, 2, 3]) AS list_negative_inner_product;

Result

 list_negative_inner_product-----------------------------                         -14

`list_position(list, element)`

Returns the index of the element if the list contains the element. If the element is not found, it returns NULL. Aliases: array_indexof, array_position, list_indexof.

Query

SELECT list_position([1, 2, NULL], 2) AS list_position;

Result

 list_position---------------             2

`list_prepend(element, list)`

Prepends element to list. Alias: array_prepend.

Query

SELECT list_prepend(3, [4, 5, 6]) AS list_prepend;

Result

 list_prepend-------------- {3,4,5,6}

`list_product(list)`

Applies aggregate function product to the list.

Query

SELECT list_product([3, 3, 9]) AS list_product;

Result

 list_product--------------           81

`list_reduce(list, lambda(x,y)[, initial_value])`

Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument. See list_reduce examples. Aliases: array_reduce, reduce.

Query

SELECT list_reduce([1, 2, 3], lambda x, y : x + y) AS list_reduce;

Result

 list_reduce-------------           6

`list_resize(list, size[[, value]])`

Resizes the list to contain size elements. Initializes new elements with value or NULL if value is not set. Alias: array_resize.

Query

SELECT list_resize([1, 2, 3], 5, 0) AS list_resize;

Result

 list_resize------------- {1,2,3,0,0}

`list_reverse(list)`

Reverses the list. Alias: array_reverse.

Query

SELECT list_reverse([3, 6, 1, 2]) AS list_reverse;

Result

 list_reverse-------------- {2,1,6,3}

`list_reverse_sort(list[, col1])`

Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about sorting order and NULL values. Alias: array_reverse_sort.

Query

SELECT list_reverse_sort([3, 6, 1, 2]) AS list_reverse_sort;

Result

 list_reverse_sort------------------- {6,3,2,1}

`list_select(value_list, index_list)`

Returns a list based on the elements selected by the index_list. Alias: array_select.

Query

SELECT list_select([10, 20, 30, 40], [1, 4]) AS list_select;

Result

 list_select------------- {10,40}

`list_sem(list)`

Applies aggregate function sem to the list.

Query

SELECT list_sem([3, 3, 9]) AS list_sem;

Result

 list_sem-------------------- 1.6329931618554523

`list_skewness(list)`

Applies aggregate function skewness to the list.

Query

SELECT list_skewness([3, 3, 9]) AS list_skewness;

Result

 list_skewness-------------------- 1.7320508075688796

`list_slice(list, begin, end)`

Extracts a sublist or substring using slice conventions. Negative values are accepted. Alias: array_slice.

Query

SELECT list_slice([4, 5, 6], 2, 3) AS list_slice;

Result

 list_slice------------ {5,6}

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

list_slice with added step feature. Alias: array_slice.

Query

SELECT list_slice([4, 5, 6], 1, 3, 2) AS list_slice;

Result

 list_slice------------ {4,6}

`list_sort(list[, col1][, col2])`

Sorts the elements of the list. See the Sorting Lists section for more details about sorting order and NULL values. Alias: array_sort.

Query

SELECT list_sort([3, 6, 1, 2]) AS list_sort;

Result

 list_sort----------- {1,2,3,6}

`list_stddev_pop(list)`

Applies aggregate function stddev_pop to the list.

Query

SELECT list_stddev_pop([3, 3, 9]) AS list_stddev_pop;

Result

 list_stddev_pop-------------------- 2.8284271247461903

`list_stddev_samp(list)`

Applies aggregate function stddev_samp to the list.

Query

SELECT list_stddev_samp([3, 3, 9]) AS list_stddev_samp;

Result

 list_stddev_samp-------------------- 3.4641016151377544

`list_string_agg(list)`

Applies aggregate function string_agg to the list.

Query

SELECT list_string_agg([3, 3, 9]) AS list_string_agg;

Result

 list_string_agg----------------- 3,3,9

`list_sum(list)`

Applies aggregate function sum to the list.

Query

SELECT list_sum([3, 3, 9]) AS list_sum;

Result

 list_sum----------       15

`list_transform(list, lambda(x))`

Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function. See list_transform examples. Aliases: apply, array_apply, array_transform, list_apply.

Query

SELECT list_transform([1, 2, 3], lambda x : x + 1) AS list_transform;

Result

 list_transform---------------- {2,3,4}

`list_unique(list)`

Counts the unique elements of a list. Alias: array_unique.

Query

SELECT list_unique([1, 1, NULL, -3, 1, 5]) AS list_unique;

Result

 list_unique-------------           3

`list_value(arg, ...)`

Creates a LIST containing the argument values. Alias: list_pack.

Query

SELECT list_value(4, 5, 6) AS list_value;

Result

 list_value------------ {4,5,6}

`list_var_pop(list)`

Applies aggregate function var_pop to the list.

Query

SELECT list_var_pop([3, 3, 9]) AS list_var_pop;

Result

 list_var_pop--------------            8

`list_var_samp(list)`

Applies aggregate function var_samp to the list.

Query

SELECT list_var_samp([3, 3, 9]) AS list_var_samp;

Result

 list_var_samp---------------            12

`list_where(value_list, mask_list)`

Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list. Alias: array_where.

Query

SELECT list_where([10, 20, 30, 40], [true, false, false, true]) AS list_where;

Result

 list_where------------ {10,40}

`list_zip(list_1, ..., list_n[, truncate])`

Zips n LISTs to a new LIST whose length will be that of the longest list. Its elements are structs of n elements from each list list_1, …, list_n, missing elements are replaced with NULL. If truncate is set, all lists are truncated to the smallest list length. Alias: array_zip.

Query

SELECT list_zip([1, 2], [3, 4], [5, 6]) AS list_zip;

Result

 list_zip----------------------- {"(1,3,5)","(2,4,6)"}

Query

SELECT list_zip([1, 2], [3, 4], [5, 6, 7]) AS list_zip;

Result

 list_zip------------------------------- {"(1,3,5)","(2,4,6)","(,,7)"}

Query

SELECT list_zip([1, 2], [3, 4], [5, 6, 7], true) AS list_zip;

Result

 list_zip----------------------- {"(1,3,5)","(2,4,6)"}

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

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

Query

SELECT range(2, 5, 3) AS range;

Result

 range------- {2}

`repeat(list, count)`

Repeats the list count number of times.

Query

SELECT repeat([1, 2, 3], 5) AS repeat;

Result

 repeat--------------------------------- {1,2,3,1,2,3,1,2,3,1,2,3,1,2,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.

Query

SELECT unnest([1, 2, 3]) AS x ORDER BY x;

Result

 x--- 1 2 3

`unpivot_list(arg, ...)`

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

Query

SELECT unpivot_list(4, 5, 6) AS unpivot_list;

Result

 unpivot_list-------------- {4,5,6}

List Operators

The following operators are supported for lists:

Operator	Description	Example	Result
`&&`	Alias for `list_has_any`.	`[1, 2, 3, 4, 5] && [2, 5, 5, 6]`	`true`
`@>`	Alias for `list_has_all`, where the list on the right of the operator is the sublist.	`[1, 2, 3, 4] @> [3, 4, 3]`	`true`
`<@`	Alias for `list_has_all`, where the list on the left of the operator is the sublist.	`[1, 4] <@ [1, 2, 3, 4]`	`true`
`\|\|`	Similar to `list_concat`, except any `NULL` input results in `NULL`.	`[1, 2, 3] \|\| [4, 5, 6]`	`[1, 2, 3, 4, 5, 6]`
`<=>`	Alias for `list_cosine_distance`.	`[1, 2, 3] <=> [1, 2, 5]`	`0.007416606`
`<->`	Alias for `list_distance`.	`[1, 2, 3] <-> [1, 2, 5]`	`2.0`

List Comprehension

Python-style list comprehension can be used to compute expressions over elements in a list. For example:

Query

SELECT [lower(x) FOR x IN strings] AS stringsFROM (VALUES (['Hello', '', 'World'])) t(strings);

Result

 strings------------------ {hello,"",world}

Query

SELECT [upper(x) FOR x IN strings IF len(x) > 0] AS stringsFROM (VALUES (['Hello', '', 'World'])) t(strings);

Result

 strings--------------- {HELLO,WORLD}

List comprehensions can also use the position of the list elements by adding a second variable. In the following example, we use x, i, where x is the value and i is the position:

Query

SELECT [4, 5, 6] AS l, [x FOR x, i IN l IF i != 2] AS filtered;

Result

 l       | filtered---------+---------- {4,5,6} | {4,6}

Under the hood, [f(x) FOR x IN l IF g(x)] is translated to:

Query

l.list_apply(lambda x, i: {'filter': g(x, i), 'result': f(x, i)})    .list_filter(lambda x: x.filter)    .list_apply(lambda x: x.result)

Result

db error: ERROR: Referenced column "l" was not found because the FROM clause is missing

Range Functions

SereneDB offers two range functions, range(start, stop, step) and generate_series(start, stop, step), and their variants with default arguments for stop and step. The two functions' behavior is different regarding their stop argument. This is documented below.

`range`

The range function creates a list of values in the range between start and stop. The start parameter is inclusive, while the stop parameter is exclusive. The default value of start is 0 and the default value of step is 1.

Based on the number of arguments, the following variants of range exist.

`range(stop)`

Query

SELECT range(5);

Result

 range------------- {0,1,2,3,4}

`range(start, stop)`

Query

SELECT range(2, 5);

Result

 range--------- {2,3,4}

`range(start, stop, step)`

Query

SELECT range(2, 5, 3);

Result

 range------- {2}

`generate_series`

The generate_series function creates a list of values in the range between start and stop. Both the start and the stop parameters are inclusive. The default value of start is 0 and the default value of step is 1. Based on the number of arguments, the following variants of generate_series exist.

`generate_series(stop)`

Query

SELECT generate_series(5);

Result

 generate_series-----------------               0               1               2               3               4               5

`generate_series(start, stop)`

Query

SELECT generate_series(2, 5);

Result

 generate_series-----------------               2               3               4               5

`generate_series(start, stop, step)`

Query

SELECT generate_series(2, 5, 3);

Result

 generate_series-----------------               2               5

`generate_subscripts(arr, dim)`

The generate_subscripts(arr, dim) function generates indexes along the dimth dimension of array arr.

Query

SELECT generate_subscripts([4, 5, 6], 1) AS i;

Result

 i--- 1 2 3

Date Ranges

Date ranges are also supported for TIMESTAMP and TIMESTAMP WITH TIME ZONE values. Note that for these types, the stop and step arguments have to be specified explicitly (a default value is not provided).

`range` for Date Ranges

Query

SELECT *FROM range(DATE '1992-01-01', DATE '1992-03-01', INTERVAL '1' MONTH);

Result

 range--------------------- 1992-01-01 00:00:00 1992-02-01 00:00:00

`generate_series` for Date Ranges

Query

SELECT *FROM generate_series(DATE '1992-01-01', DATE '1992-03-01', INTERVAL '1' MONTH);

Result

 generate_series--------------------- 1992-01-01 00:00:00 1992-02-01 00:00:00 1992-03-01 00:00:00

Slicing

The function list_slice can be used to extract a sublist from a list. The following variants exist:

list_slice(list, begin, end)
list_slice(list, begin, end, step)
array_slice(list, begin, end)
array_slice(list, begin, end, step)
list[begin:end]
list[begin:end:step]

The arguments are as follows:

list
- Is the list to be sliced
begin
- Is the index of the first element to be included in the slice
- When begin < 0 the index is counted from the end of the list
- When begin < 0 and -begin > length, begin is clamped to the beginning of the list
- When begin > length, the result is an empty list
- Bracket Notation: When begin is omitted, it defaults to the beginning of the list
end
- Is the index of the last element to be included in the slice
- When end < 0 the index is counted from the end of the list
- When end > length, end is clamped to length
- When end < begin, the result is an empty list
- Bracket Notation: When end is omitted, it defaults to the end of the list. When end is omitted and a step is provided, end must be replaced with a -
step (optional)
- Is the step size between elements in the slice
- When step < 0 the slice is reversed, and begin and end are swapped
- Must be non-zero

Examples:

Query

SELECT list_slice([1, 2, 3, 4, 5], 2, 4);

Result

 list_slice------------ {2,3,4}

Query

SELECT ([1, 2, 3, 4, 5])[2:4:2];

Result

 ?column?---------- {2,4}

Query

SELECT([1, 2, 3, 4, 5])[4:2:-2];

Result

 ?column?---------- {4,2}

Query

SELECT ([1, 2, 3, 4, 5])[:];

Result

 ?column?------------- {1,2,3,4,5}

Query

SELECT ([1, 2, 3, 4, 5])[:-:2];

Result

 ?column?---------- {1,3,5}

Query

SELECT ([1, 2, 3, 4, 5])[:-:-2];

Result

 ?column?---------- {5,3,1}

List Aggregates

The function list_aggregate allows the execution of arbitrary existing aggregate functions on the elements of a list. Its first argument is the list (column), its second argument is the aggregate function name, e.g., min, histogram or sum.

list_aggregate accepts additional arguments after the aggregate function name. These extra arguments are passed directly to the aggregate function, which serves as the second argument of list_aggregate.

Order-sensitive aggregate functions are applied in the order of the list. The ORDER BY, DISTINCT and FILTER clauses are not supported by list_aggregate. They may instead be emulated using list_sort, list_grade_up, list_select, list_distinct and list_filter.

Query

SELECT list_aggregate([1, 2, -4, NULL], 'min');

Result

 list_aggregate----------------             -4

Query

SELECT list_aggregate([2, 4, 8, 42], 'sum');

Result

 list_aggregate----------------             56

Query

SELECT list_aggregate([[1, 2], [NULL], [2, 10, 3]], 'last');

Result

 list_aggregate---------------- {2,10,3}

Query

SELECT list_aggregate([2, 4, 8, 42], 'string_agg', '|');

Result

 list_aggregate---------------- 2|4|8|42

`list_*` Rewrite Functions

The following is a list of existing rewrites. Rewrites simplify the use of the list aggregate function by only taking the list (column) as their argument. list_avg, list_var_samp, list_var_pop, list_stddev_pop, list_stddev_samp, list_sem, list_approx_count_distinct, list_bit_xor, list_bit_or, list_bit_and, list_bool_and, list_bool_or, list_count, list_entropy, list_last, list_first, list_kurtosis, list_kurtosis_pop, list_min, list_max, list_product, list_skewness, list_sum, list_string_agg, list_mode, list_median, list_mad and list_histogram.

Query

SELECT list_min([1, 2, -4, NULL]);

Result

 list_min----------       -4

Query

SELECT list_sum([2, 4, 8, 42]);

Result

 list_sum----------       56

Query

SELECT list_last([[1, 2], [NULL], [2, 10, 3]]);

Result

 list_last----------- {2,10,3}

`array_to_string`

Concatenates list/array elements using an optional delimiter.

Query

SELECT array_to_string([1, 2, 3], '-') AS str;

Result

 str------- 1-2-3

This is equivalent to the following SQL:

Query

SELECT list_aggr([1, 2, 3], 'string_agg', '-') AS str;

Result

 str------- 1-2-3

Sorting Lists

The function list_sort sorts the elements of a list either in ascending or descending order. In addition, it allows specifying whether NULL values should be moved to the beginning or to the end of the list. It has the same sorting behavior as SereneDB's ORDER BY clause. Therefore, (nested) values compare the same in list_sort as in ORDER BY.

By default, if no modifiers are provided, SereneDB sorts ASC NULLS LAST. I.e., the values are sorted in ascending order and NULL values are placed last. This is identical to the default sort order of SQLite. The default sort order can be changed using PRAGMA statements..

list_sort leaves it open to the user whether they want to use the default sort order or a custom order. list_sort takes up to two additional optional parameters. The second parameter provides the sort order and can be either ASC or DESC. The third parameter provides the NULL order and can be either NULLS FIRST or NULLS LAST.

This query uses the default sort order and the default NULL order.

Query

SELECT list_sort([1, 3, NULL, 5, NULL, -5]);

Result

 list_sort---------------------- {-5,1,3,5,NULL,NULL}

Query

[-5, 1, 3, 5, NULL, NULL]

Result

 list_value---------------------- {-5,1,3,5,NULL,NULL}

This query provides the sort order. The NULL order uses the configurable default value.

Query

SELECT list_sort([1, 3, NULL, 2], 'ASC');

Result

 list_sort-------------- {1,2,3,NULL}

Query

[1, 2, 3, NULL]

Result

 list_value-------------- {1,2,3,NULL}

This query provides both the sort order and the NULL order.

Query

SELECT list_sort([1, 3, NULL, 2], 'DESC', 'NULLS FIRST');

Result

 list_sort-------------- {NULL,3,2,1}

Query

[NULL, 3, 2, 1]

Result

 list_value-------------- {NULL,3,2,1}

list_reverse_sort has an optional second parameter providing the NULL sort order. It can be either NULLS FIRST or NULLS LAST.

This query uses the default NULL sort order.

Query

SELECT list_sort([1, 3, NULL, 5, NULL, -5]);

Result

 list_sort---------------------- {-5,1,3,5,NULL,NULL}

Query

[-5, 1, 3, 5, NULL, NULL]

Result

 list_value---------------------- {-5,1,3,5,NULL,NULL}

This query provides the NULL sort order.

Query

SELECT list_reverse_sort([1, 3, NULL, 2], 'NULLS LAST');

Result

 list_reverse_sort------------------- {3,2,1,NULL}

Query

[3, 2, 1, NULL]

Result

 list_value-------------- {3,2,1,NULL}

Flattening

The flatten function is a scalar function that converts a list of lists into a single list by concatenating each sub-list together. Note that this only flattens one level at a time, not all levels of sub-lists.

Convert a list of lists into a single list:

Query

SELECT    flatten([        [1, 2],        [3, 4]    ]);

Result

 flatten----------- {1,2,3,4}

If the list has multiple levels of lists, only the first level of sub-lists is concatenated into a single list:

Query

SELECT    flatten([        [            [1, 2],            [3, 4],        ],        [            [5, 6],            [7, 8],        ]    ]);

Result

 flatten--------------------------- {{1,2},{3,4},{5,6},{7,8}}

In general, the input to the flatten function should be a list of lists (not a single level list). However, the flatten function has specific behavior when handling empty lists and NULL values.

If the input list is empty, return an empty list:

Query

SELECT flatten([]);

Result

 flatten--------- {}

If the entire input to flatten is NULL, return NULL:

Query

SELECT flatten(NULL);

Result

 flatten--------- NULL

If a list whose only entry is NULL is flattened, return an empty list:

Query

SELECT flatten([NULL]);

Result

 flatten--------- {}

If the sub-list in a list of lists only contains NULL, do not modify the sub-list:

Query

-- (Note the extra set of parentheses vs. the prior example)SELECT flatten([[NULL]]);

Result

 flatten--------- {NULL}

Even if the only contents of each sub-list is NULL, still concatenate them together. Note that no de-duplication occurs when flattening. See list_distinct function for de-duplication:

Query

SELECT flatten([[NULL], [NULL]]);

Result

 flatten------------- {NULL,NULL}

Lambda Functions

SereneDB supports lambda functions in the form lambda parameter1, parameter2, ...: expression. For details, see the lambda functions page.

The aggregate functions list and histogram produce lists and lists of structs.
The unnest function is used to unnest a list by one level.

list[index]​

list[begin[:end][:step]]​

arg1 || arg2​

array_extract(list, index)​

array_pop_back(list)​

array_pop_front(list)​

array_push_front(list, element)​

array_to_string(list, delimiter)​

array_to_string_comma_default(array)​

concat(value, ...)​

contains(list, element)​

flatten(nested_list)​

generate_series(start[, stop][, step])​

length(list)​

list_aggregate(list, function_name, ...)​

list_any_value(list)​

list_append(list, element)​

list_approx_count_distinct(list)​

list_avg(list)​

list_bit_and(list)​

list_bit_or(list)​

list_bit_xor(list)​

list_bool_and(list)​

list_bool_or(list)​

list_concat(list_1, ..., list_n)​

list_contains(list, element)​

list_cosine_distance(list1, list2)​

list_cosine_similarity(list1, list2)​

list_count(list)​

list_distance(list1, list2)​

list_distinct(list)​

list_entropy(list)​

list_extract(list, index)​

list_filter(list, lambda(x))​

list_first(list)​

list_grade_up(list[, col1][, col2])​

list_has_all(list1, list2)​

list_has_any(list1, list2)​

list_histogram(list)​

list_inner_product(list1, list2)​

list_intersect(list1, list2)​

list_kurtosis(list)​

list_kurtosis_pop(list)​

list_last(list)​

list_mad(list)​

list_max(list)​

list_median(list)​

list_min(list)​

list_mode(list)​

list_negative_inner_product(list1, list2)​

list_position(list, element)​

list_prepend(element, list)​

list_product(list)​

list_reduce(list, lambda(x,y)[, initial_value])​

list_resize(list, size[[, value]])​

list_reverse(list)​

list_reverse_sort(list[, col1])​

list_select(value_list, index_list)​

list_sem(list)​

list_skewness(list)​

list_slice(list, begin, end)​

list_slice(list, begin, end, step)​

list_sort(list[, col1][, col2])​

list_stddev_pop(list)​

list_stddev_samp(list)​

list_string_agg(list)​

list_sum(list)​

list_transform(list, lambda(x))​

list_unique(list)​

list_value(arg, ...)​

list_var_pop(list)​

list_var_samp(list)​

list_where(value_list, mask_list)​

list_zip(list_1, ..., list_n[, truncate])​

range(start[, stop][, step])​

repeat(list, count)​

unnest(list)​

unpivot_list(arg, ...)​

List Operators​

List Comprehension​

`list[index]`

`list[begin[:end][:step]]`

`arg1 || arg2`

`array_extract(list, index)`

`array_pop_back(list)`

`array_pop_front(list)`

`array_push_front(list, element)`

`array_to_string(list, delimiter)`

`array_to_string_comma_default(array)`

`concat(value, ...)`

`contains(list, element)`

`flatten(nested_list)`

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

`length(list)`

`list_aggregate(list, function_name, ...)`

`list_any_value(list)`

`list_append(list, element)`

`list_approx_count_distinct(list)`

`list_avg(list)`

`list_bit_and(list)`

`list_bit_or(list)`

`list_bit_xor(list)`

`list_bool_and(list)`

`list_bool_or(list)`

`list_concat(list_1, ..., list_n)`

`list_contains(list, element)`

`list_cosine_distance(list1, list2)`

`list_cosine_similarity(list1, list2)`

`list_count(list)`

`list_distance(list1, list2)`

`list_distinct(list)`

`list_entropy(list)`

`list_extract(list, index)`

`list_filter(list, lambda(x))`

`list_first(list)`

`list_grade_up(list[, col1][, col2])`

`list_has_all(list1, list2)`

`list_has_any(list1, list2)`

`list_histogram(list)`

`list_inner_product(list1, list2)`

`list_intersect(list1, list2)`

`list_kurtosis(list)`

`list_kurtosis_pop(list)`

`list_last(list)`

`list_mad(list)`

`list_max(list)`

`list_median(list)`

`list_min(list)`

`list_mode(list)`

`list_negative_inner_product(list1, list2)`

`list_position(list, element)`

`list_prepend(element, list)`

`list_product(list)`

`list_reduce(list, lambda(x,y)[, initial_value])`

`list_resize(list, size[[, value]])`

`list_reverse(list)`

`list_reverse_sort(list[, col1])`

`list_select(value_list, index_list)`

`list_sem(list)`

`list_skewness(list)`

`list_slice(list, begin, end)`

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

`list_sort(list[, col1][, col2])`

`list_stddev_pop(list)`

`list_stddev_samp(list)`

`list_string_agg(list)`

`list_sum(list)`

`list_transform(list, lambda(x))`

`list_unique(list)`

`list_value(arg, ...)`

`list_var_pop(list)`

`list_var_samp(list)`

`list_where(value_list, mask_list)`

`list_zip(list_1, ..., list_n[, truncate])`

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

`repeat(list, count)`

`unnest(list)`

`unpivot_list(arg, ...)`

List Operators

List Comprehension