A practical DuckDB reference with 71 list and array function signatures, runnable SQL examples, and expected query results.
DuckDB list functions and array functions let you extract, transform, aggregate, compare, sort, and reshape nested values directly in SQL. This reference covers 71 functions with their signatures, runnable queries, and expected results.
Use the quick reference to choose a function by task, then jump to its complete SQL example in the table of contents.
| Task | Functions |
|---|---|
| Extract values |
array_extract, list_extract, list_first, list_last
|
| Add or combine values |
list_append, list_prepend, list_concat, concat
|
| Filter or transform values |
list_filter, list_transform, list_where
|
| Aggregate values |
list_aggregate, list_sum, list_avg, list_count
|
| Sort or select values |
list_sort, list_reverse_sort, list_grade_up, list_select
|
| Compare vectors |
list_cosine_similarity, list_cosine_distance, list_distance, list_inner_product
|
| Reshape nested data |
flatten, list_zip, unnest
|
array_extract(list, index)
WITH list_inputs(values, requested_index) AS (
VALUES
([101, 102]::INTEGER[], 1),
([201, 202, 203]::INTEGER[], 2),
([301, 302, 303, 304]::INTEGER[], 4)
)
SELECT
values,
requested_index,
array_extract(values, requested_index) AS extracted_value
FROM list_inputs
ORDER BY requested_index;
values requested_index extracted_value
-------------------- --------------- ---------------
[101, 102] 1 101
[201, 202, 203] 2 202
[301, 302, 303, 304] 4 304
array_pop_back(list)
WITH list_inputs(row_id, values) AS (
VALUES
(1, [11, 12]::SMALLINT[]),
(2, [21, 22, 23]::SMALLINT[]),
(3, [31, 32, 33, 34, 35]::SMALLINT[])
)
SELECT
row_id,
values,
array_pop_back(values) AS values_without_last
FROM list_inputs
ORDER BY row_id;
row_id values values_without_last
------ -------------------- -------------------
1 [11, 12] [11]
2 [21, 22, 23] [21, 22]
3 [31, 32, 33, 34, 35] [31, 32, 33, 34]
array_pop_front(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [41, 42]::INTEGER[]),
(2, [51, 52, 53]::INTEGER[]),
(3, [61, 62, 63, 64]::INTEGER[])
)
SELECT
row_id,
input_values,
array_pop_front(input_values) AS values_without_first
FROM list_inputs
ORDER BY row_id;
row_id input_values values_without_first
------ ---------------- --------------------
1 [41, 42] [42]
2 [51, 52, 53] [52, 53]
3 [61, 62, 63, 64] [62, 63, 64]
array_push_front(list, element)
WITH list_inputs(row_id, new_value, input_values) AS (
VALUES
(1, 70, [71, 72]::INTEGER[]),
(2, 80, [81, 82, 83]::INTEGER[]),
(3, 90, [91, 92, 93, 94]::INTEGER[])
)
SELECT
row_id,
new_value,
input_values,
array_push_front(input_values, new_value) AS pushed_values
FROM list_inputs
ORDER BY row_id;
row_id new_value input_values pushed_values
------ --------- ---------------- --------------------
1 70 [71, 72] [70, 71, 72]
2 80 [81, 82, 83] [80, 81, 82, 83]
3 90 [91, 92, 93, 94] [90, 91, 92, 93, 94]
array_to_string(list, delimiter)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['amber', 'bronze']::VARCHAR[]),
(2, ['cyan', 'denim', 'emerald']::VARCHAR[]),
(3, ['fuchsia', 'gold', 'hazel', 'indigo']::VARCHAR[])
)
SELECT
row_id,
input_values,
array_to_string(input_values, '|') AS joined_text
FROM list_inputs
ORDER BY row_id;
row_id input_values joined_text
------ ------------------------------ -------------------------
1 [amber, bronze] amber|bronze
2 [cyan, denim, emerald] cyan|denim|emerald
3 [fuchsia, gold, hazel, indigo] fuchsia|gold|hazel|indigo
concat(value, ...)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [101, 102]::INTEGER[], [103]::INTEGER[]),
(2, [111, 112, 113]::INTEGER[], [114, 115]::INTEGER[]),
(3, [121, 122, 123, 124]::INTEGER[], [125, 126, 127]::INTEGER[])
)
SELECT
row_id,
left_values,
right_values,
concat(left_values, right_values) AS concatenated_values
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values concatenated_values
------ -------------------- --------------- -----------------------------------
1 [101, 102] [103] [101, 102, 103]
2 [111, 112, 113] [114, 115] [111, 112, 113, 114, 115]
3 [121, 122, 123, 124] [125, 126, 127] [121, 122, 123, 124, 125, 126, 127]
contains(list, element)
WITH list_inputs(row_id, input_values, sought_value) AS (
VALUES
(1, [131, 132]::INTEGER[], 132),
(2, [141, 142, 143]::INTEGER[], 140),
(3, [151, 152, 153, 154]::INTEGER[], 154)
)
SELECT
row_id,
input_values,
sought_value,
contains(input_values, sought_value) AS contains_value
FROM list_inputs
ORDER BY row_id;
row_id input_values sought_value contains_value
------ -------------------- ------------ --------------
1 [131, 132] 132 true
2 [141, 142, 143] 140 false
3 [151, 152, 153, 154] 154 true
flatten(nested_list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [[161, 162], [163]]::INTEGER[][]),
(2, [[171], [172, 173], [174, 175, 176]]::INTEGER[][]),
(3, [[181, 182, 183], [184], [185, 186], [187]]::INTEGER[][])
)
SELECT
row_id,
input_values,
flatten(input_values) AS flattened_values
FROM list_inputs
ORDER BY row_id;
row_id input_values flattened_values
------ ------------------------------------------- -----------------------------------
1 [[161, 162], [163]] [161, 162, 163]
2 [[171], [172, 173], [174, 175, 176]] [171, 172, 173, 174, 175, 176]
3 [[181, 182, 183], [184], [185, 186], [187]] [181, 182, 183, 184, 185, 186, 187]
length(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['jade', 'khaki']::VARCHAR[]),
(2, ['lilac', 'magenta', 'navy']::VARCHAR[]),
(3, ['ochre', 'pearl', 'quartz', 'rose']::VARCHAR[])
)
SELECT
row_id,
input_values,
length(input_values) AS list_length
FROM list_inputs
ORDER BY row_id;
row_id input_values list_length
------ ---------------------------- -----------
1 [jade, khaki] 2
2 [lilac, magenta, navy] 3
3 [ochre, pearl, quartz, rose] 4
list_aggregate(list, function_name, ...)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [191, 192]::INTEGER[]),
(2, [205, 206, 207]::INTEGER[]),
(3, [211, 212, 213, 214]::INTEGER[])
)
SELECT
row_id,
input_values,
list_aggregate(input_values, 'sum') AS total
FROM list_inputs
ORDER BY row_id;
row_id input_values total
------ -------------------- -----
1 [191, 192] 383
2 [205, 206, 207] 618
3 [211, 212, 213, 214] 850
list_any_value(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['spruce', 'tulip']::VARCHAR[]),
(2, ['umber', 'violet', 'willow']::VARCHAR[]),
(3, ['xenia', 'yucca', 'zinnia', 'acacia']::VARCHAR[])
)
SELECT
row_id,
input_values,
list_any_value(input_values) AS selected_value
FROM list_inputs
ORDER BY row_id;
row_id input_values selected_value
------ ------------------------------ --------------
1 [spruce, tulip] spruce
2 [umber, violet, willow] umber
3 [xenia, yucca, zinnia, acacia] xenia
list_append(list, element)
WITH list_inputs(row_id, input_values, appended_value) AS (
VALUES
(1, [221, 222]::INTEGER[], 223),
(2, [231, 232, 233]::INTEGER[], 234),
(3, [241, 242, 243, 244]::INTEGER[], 245)
)
SELECT
row_id,
input_values,
appended_value,
list_append(input_values, appended_value) AS appended_values
FROM list_inputs
ORDER BY row_id;
row_id input_values appended_value appended_values
------ -------------------- -------------- -------------------------
1 [221, 222] 223 [221, 222, 223]
2 [231, 232, 233] 234 [231, 232, 233, 234]
3 [241, 242, 243, 244] 245 [241, 242, 243, 244, 245]
list_approx_count_distinct(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [251, 251]::INTEGER[]),
(2, [261, 262, 261]::INTEGER[]),
(3, [271, 272, 273, 271]::INTEGER[])
)
SELECT
row_id,
input_values,
list_approx_count_distinct(input_values) AS approximate_distinct_count
FROM list_inputs
ORDER BY row_id;
row_id input_values approximate_distinct_count
------ -------------------- --------------------------
1 [251, 251] 1
2 [261, 262, 261] 2
3 [271, 272, 273, 271] 3
list_avg(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [2.0, 4.0]::DOUBLE[]),
(2, [3.0, 6.0, 9.0]::DOUBLE[]),
(3, [5.0, 10.0, 15.0, 20.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_avg(input_values) AS average_value
FROM list_inputs
ORDER BY row_id;
row_id input_values average_value
------ ----------------------- -------------
1 [2.0, 4.0] 3.0
2 [3.0, 6.0, 9.0] 6.0
3 [5.0, 10.0, 15.0, 20.0] 12.5
list_bit_and(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [15, 7]::INTEGER[]),
(2, [31, 14, 7]::INTEGER[]),
(3, [63, 31, 15, 7]::INTEGER[])
)
SELECT
row_id,
input_values,
list_bit_and(input_values) AS bitwise_and
FROM list_inputs
ORDER BY row_id;
row_id input_values bitwise_and
------ --------------- -----------
1 [15, 7] 7
2 [31, 14, 7] 6
3 [63, 31, 15, 7] 7
list_bit_or(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [256, 1]::INTEGER[]),
(2, [512, 4, 2]::INTEGER[]),
(3, [1024, 16, 8, 1]::INTEGER[])
)
SELECT
row_id,
input_values,
list_bit_or(input_values) AS bitwise_or
FROM list_inputs
ORDER BY row_id;
row_id input_values bitwise_or
------ ---------------- ----------
1 [256, 1] 257
2 [512, 4, 2] 518
3 [1024, 16, 8, 1] 1049
list_bit_xor(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [3, 5]::INTEGER[]),
(2, [9, 6, 3]::INTEGER[]),
(3, [12, 10, 6, 3]::INTEGER[])
)
SELECT
row_id,
input_values,
list_bit_xor(input_values) AS bitwise_xor
FROM list_inputs
ORDER BY row_id;
row_id input_values bitwise_xor
------ -------------- -----------
1 [3, 5] 6
2 [9, 6, 3] 12
3 [12, 10, 6, 3] 3
list_bool_and(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [true, true]::BOOLEAN[]),
(2, [true, false, true]::BOOLEAN[]),
(3, [true, true, true, true]::BOOLEAN[])
)
SELECT
row_id,
input_values,
list_bool_and(input_values) AS all_true
FROM list_inputs
ORDER BY row_id;
row_id input_values all_true
------ ------------------------ --------
1 [true, true] true
2 [true, false, true] false
3 [true, true, true, true] true
list_bool_or(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [false, false]::BOOLEAN[]),
(2, [false, true, false]::BOOLEAN[]),
(3, [false, false, false, false]::BOOLEAN[])
)
SELECT
row_id,
input_values,
list_bool_or(input_values) AS any_true
FROM list_inputs
ORDER BY row_id;
row_id input_values any_true
------ ---------------------------- --------
1 [false, false] false
2 [false, true, false] true
3 [false, false, false, false] false
list_concat(list_1, ..., list_n)
WITH list_inputs(row_id, first_values, second_values) AS (
VALUES
(1, [281, 282]::INTEGER[], [283]::INTEGER[]),
(2, [291, 292, 293]::INTEGER[], [294, 295]::INTEGER[]),
(3, [301, 302, 303, 304]::INTEGER[], [305, 306, 307]::INTEGER[])
)
SELECT
row_id,
first_values,
second_values,
list_concat(first_values, second_values) AS combined_values
FROM list_inputs
ORDER BY row_id;
row_id first_values second_values combined_values
------ -------------------- --------------- -----------------------------------
1 [281, 282] [283] [281, 282, 283]
2 [291, 292, 293] [294, 295] [291, 292, 293, 294, 295]
3 [301, 302, 303, 304] [305, 306, 307] [301, 302, 303, 304, 305, 306, 307]
list_contains(list, element)
WITH list_inputs(row_id, input_values, sought_value) AS (
VALUES
(1, [311, 312]::INTEGER[], 311),
(2, [321, 322, 323]::INTEGER[], 329),
(3, [331, 332, 333, 334]::INTEGER[], 333)
)
SELECT
row_id,
input_values,
sought_value,
list_contains(input_values, sought_value) AS contains_value
FROM list_inputs
ORDER BY row_id;
row_id input_values sought_value contains_value
------ -------------------- ------------ --------------
1 [311, 312] 311 true
2 [321, 322, 323] 329 false
3 [331, 332, 333, 334] 333 true
list_cosine_distance(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [1.0, 0.0]::DOUBLE[], [0.0, 1.0]::DOUBLE[]),
(2, [1.0, 2.0, 3.0]::DOUBLE[], [1.0, 2.0, 3.0]::DOUBLE[]),
(3, [1.0, 1.0, 1.0, 1.0]::DOUBLE[], [2.0, 2.0, 2.0, 2.0]::DOUBLE[])
)
SELECT
row_id,
left_values,
right_values,
list_cosine_distance(left_values, right_values) AS cosine_distance
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values cosine_distance
------ -------------------- -------------------- ---------------
1 [1.0, 0.0] [0.0, 1.0] 1.0
2 [1.0, 2.0, 3.0] [1.0, 2.0, 3.0] 0.0
3 [1.0, 1.0, 1.0, 1.0] [2.0, 2.0, 2.0, 2.0] 0.0
list_cosine_similarity(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [1.0, 0.0]::DOUBLE[], [1.0, 0.0]::DOUBLE[]),
(2, [1.0, 2.0, 2.0]::DOUBLE[], [2.0, 1.0, 2.0]::DOUBLE[]),
(3, [1.0, 2.0, 3.0, 4.0]::DOUBLE[], [4.0, 3.0, 2.0, 1.0]::DOUBLE[])
)
SELECT
row_id,
left_values,
right_values,
list_cosine_similarity(left_values, right_values) AS cosine_similarity
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values cosine_similarity
------ -------------------- -------------------- ------------------
1 [1.0, 0.0] [1.0, 0.0] 1.0
2 [1.0, 2.0, 2.0] [2.0, 1.0, 2.0] 0.8888888888888888
3 [1.0, 2.0, 3.0, 4.0] [4.0, 3.0, 2.0, 1.0] 0.6666666666666666
list_count(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [341, NULL]::INTEGER[]),
(2, [351, NULL, 353]::INTEGER[]),
(3, [361, 362, NULL, 364]::INTEGER[])
)
SELECT
row_id,
input_values,
list_count(input_values) AS non_null_count
FROM list_inputs
ORDER BY row_id;
row_id input_values non_null_count
------ --------------------- --------------
1 [341, NULL] 1
2 [351, NULL, 353] 2
3 [361, 362, NULL, 364] 3
list_distance(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [0.0, 0.0]::DOUBLE[], [3.0, 4.0]::DOUBLE[]),
(2, [1.0, 2.0, 3.0]::DOUBLE[], [4.0, 6.0, 3.0]::DOUBLE[]),
(3, [1.0, 1.0, 1.0, 1.0]::DOUBLE[], [2.0, 3.0, 4.0, 5.0]::DOUBLE[])
)
SELECT
row_id,
left_values,
right_values,
list_distance(left_values, right_values) AS euclidean_distance
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values euclidean_distance
------ -------------------- -------------------- ------------------
1 [0.0, 0.0] [3.0, 4.0] 5.0
2 [1.0, 2.0, 3.0] [4.0, 6.0, 3.0] 5.0
3 [1.0, 1.0, 1.0, 1.0] [2.0, 3.0, 4.0, 5.0] 5.477225575051661
list_distinct(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [371, 371]::INTEGER[]),
(2, [381, 382, 381]::INTEGER[]),
(3, [391, 392, 391, 393]::INTEGER[])
)
SELECT
row_id,
input_values,
list_sort(list_distinct(input_values)) AS distinct_values
FROM list_inputs
ORDER BY row_id;
row_id input_values distinct_values
------ -------------------- ---------------
1 [371, 371] [371]
2 [381, 382, 381] [381, 382]
3 [391, 392, 391, 393] [391, 392, 393]
list_entropy(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [7, 7]::INTEGER[]),
(2, [8, 8, 9]::INTEGER[]),
(3, [10, 10, 11, 11]::INTEGER[])
)
SELECT
row_id,
input_values,
list_entropy(input_values) AS entropy_value
FROM list_inputs
ORDER BY row_id;
row_id input_values entropy_value
------ ---------------- ------------------
1 [7, 7] 0.0
2 [8, 8, 9] 0.9182958340544893
3 [10, 10, 11, 11] 1.0
list_extract(list, index)
WITH list_inputs(row_id, input_values, requested_index) AS (
VALUES
(1, [401, 402]::INTEGER[], 2),
(2, [411, 412, 413]::INTEGER[], 1),
(3, [421, 422, 423, 424]::INTEGER[], 3)
)
SELECT
row_id,
input_values,
requested_index,
list_extract(input_values, requested_index) AS extracted_value
FROM list_inputs
ORDER BY row_id;
row_id input_values requested_index extracted_value
------ -------------------- --------------- ---------------
1 [401, 402] 2 402
2 [411, 412, 413] 1 411
3 [421, 422, 423, 424] 3 423
list_filter(list, lambda(x))
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [-2, 2]::INTEGER[]),
(2, [-3, 0, 3]::INTEGER[]),
(3, [-4, -1, 1, 4]::INTEGER[])
)
SELECT
row_id,
input_values,
list_filter(input_values, lambda value: value > 0) AS positive_values
FROM list_inputs
ORDER BY row_id;
row_id input_values positive_values
------ -------------- ---------------
1 [-2, 2] [2]
2 [-3, 0, 3] [3]
3 [-4, -1, 1, 4] [1, 4]
list_first(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['birch', 'cedar']::VARCHAR[]),
(2, ['dogwood', 'elm', 'fir']::VARCHAR[]),
(3, ['ginkgo', 'hemlock', 'ironwood', 'juniper']::VARCHAR[])
)
SELECT
row_id,
input_values,
list_first(input_values) AS first_value
FROM list_inputs
ORDER BY row_id;
row_id input_values first_value
------ ------------------------------------ -----------
1 [birch, cedar] birch
2 [dogwood, elm, fir] dogwood
3 [ginkgo, hemlock, ironwood, juniper] ginkgo
list_grade_up(list[, col1][, col2])
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [5, 1]::INTEGER[]),
(2, [3, 1, 2]::INTEGER[]),
(3, [4, 2, 3, 1]::INTEGER[])
)
SELECT
row_id,
input_values,
list_grade_up(input_values) AS sorted_indexes
FROM list_inputs
ORDER BY row_id;
row_id input_values sorted_indexes
------ ------------ --------------
1 [5, 1] [2, 1]
2 [3, 1, 2] [2, 3, 1]
3 [4, 2, 3, 1] [4, 2, 3, 1]
list_has_all(list1, list2)
WITH list_inputs(row_id, available_values, required_values) AS (
VALUES
(1, [431, 432]::INTEGER[], [431]::INTEGER[]),
(2, [441, 442, 443]::INTEGER[], [441, 449]::INTEGER[]),
(3, [451, 452, 453, 454]::INTEGER[], [451, 453, 454]::INTEGER[])
)
SELECT
row_id,
available_values,
required_values,
list_has_all(available_values, required_values) AS has_all_values
FROM list_inputs
ORDER BY row_id;
row_id available_values required_values has_all_values
------ -------------------- --------------- --------------
1 [431, 432] [431] true
2 [441, 442, 443] [441, 449] false
3 [451, 452, 453, 454] [451, 453, 454] true
list_has_any(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [461, 462]::INTEGER[], [462]::INTEGER[]),
(2, [471, 472, 473]::INTEGER[], [478, 479]::INTEGER[]),
(3, [481, 482, 483, 484]::INTEGER[], [480, 482, 489]::INTEGER[])
)
SELECT
row_id,
left_values,
right_values,
list_has_any(left_values, right_values) AS has_any_value
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values has_any_value
------ -------------------- --------------- -------------
1 [461, 462] [462] true
2 [471, 472, 473] [478, 479] false
3 [481, 482, 483, 484] [480, 482, 489] true
list_histogram(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1, 1]::INTEGER[]),
(2, [2, 2, 3]::INTEGER[]),
(3, [4, 5, 4, 6]::INTEGER[])
)
SELECT
row_id,
input_values,
list_histogram(input_values) AS value_histogram
FROM list_inputs
ORDER BY row_id;
row_id input_values value_histogram
------ ------------ ---------------
1 [1, 1] {1=2}
2 [2, 2, 3] {2=2, 3=1}
3 [4, 5, 4, 6] {4=2, 5=1, 6=1}
list_inner_product(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [1.0, 2.0]::DOUBLE[], [3.0, 4.0]::DOUBLE[]),
(2, [2.0, 3.0, 4.0]::DOUBLE[], [5.0, 6.0, 7.0]::DOUBLE[]),
(3, [1.0, 3.0, 5.0, 7.0]::DOUBLE[], [7.0, 5.0, 3.0, 1.0]::DOUBLE[])
)
SELECT
row_id,
left_values,
right_values,
list_inner_product(left_values, right_values) AS inner_product
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values inner_product
------ -------------------- -------------------- -------------
1 [1.0, 2.0] [3.0, 4.0] 11.0
2 [2.0, 3.0, 4.0] [5.0, 6.0, 7.0] 56.0
3 [1.0, 3.0, 5.0, 7.0] [7.0, 5.0, 3.0, 1.0] 44.0
list_intersect(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [491, 492]::INTEGER[], [492, 493]::INTEGER[]),
(2, [501, 502, 503]::INTEGER[], [500, 502, 503]::INTEGER[]),
(3, [511, 512, 513, 514]::INTEGER[], [510, 512, 514, 519]::INTEGER[])
)
SELECT
row_id,
left_values,
right_values,
list_sort(list_intersect(left_values, right_values)) AS common_values
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values common_values
------ -------------------- -------------------- -------------
1 [491, 492] [492, 493] [492]
2 [501, 502, 503] [500, 502, 503] [502, 503]
3 [511, 512, 513, 514] [510, 512, 514, 519] [512, 514]
list_kurtosis(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1.0, 2.0, 3.0, 4.0]::DOUBLE[]),
(2, [2.0, 3.0, 4.0, 5.0, 6.0]::DOUBLE[]),
(3, [1.0, 2.0, 2.0, 3.0, 4.0, 8.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_kurtosis(input_values) AS kurtosis_value
FROM list_inputs
ORDER BY row_id;
row_id input_values kurtosis_value
------ ------------------------------ -------------------
1 [1.0, 2.0, 3.0, 4.0] -1.200000000000001
2 [2.0, 3.0, 4.0, 5.0, 6.0] -1.2000000000000857
3 [1.0, 2.0, 2.0, 3.0, 4.0, 8.0] 2.8485740153915953
list_kurtosis_pop(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [10.0, 20.0, 30.0, 40.0]::DOUBLE[]),
(2, [5.0, 10.0, 15.0, 20.0, 25.0]::DOUBLE[]),
(3, [1.0, 1.0, 2.0, 3.0, 5.0, 8.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_kurtosis_pop(input_values) AS population_kurtosis
FROM list_inputs
ORDER BY row_id;
row_id input_values population_kurtosis
------ ------------------------------ -------------------
1 [10.0, 20.0, 30.0, 40.0] -1.36
2 [5.0, 10.0, 15.0, 20.0, 25.0] -1.3000000000000045
3 [1.0, 1.0, 2.0, 3.0, 5.0, 8.0] -0.6562499999999964
list_last(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['koala', 'lemur']::VARCHAR[]),
(2, ['marten', 'newt', 'otter']::VARCHAR[]),
(3, ['panda', 'quail', 'rabbit', 'stoat']::VARCHAR[])
)
SELECT
row_id,
input_values,
list_last(input_values) AS last_value
FROM list_inputs
ORDER BY row_id;
row_id input_values last_value
------ ----------------------------- ----------
1 [koala, lemur] lemur
2 [marten, newt, otter] otter
3 [panda, quail, rabbit, stoat] stoat
list_mad(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1.0, 3.0]::DOUBLE[]),
(2, [2.0, 4.0, 8.0]::DOUBLE[]),
(3, [1.0, 2.0, 5.0, 9.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_mad(input_values) AS median_absolute_deviation
FROM list_inputs
ORDER BY row_id;
row_id input_values median_absolute_deviation
------ -------------------- -------------------------
1 [1.0, 3.0] 1.0
2 [2.0, 4.0, 8.0] 2.0
3 [1.0, 2.0, 5.0, 9.0] 2.0
list_max(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [521, 522]::INTEGER[]),
(2, [531, 539, 533]::INTEGER[]),
(3, [541, 542, 549, 544]::INTEGER[])
)
SELECT
row_id,
input_values,
list_max(input_values) AS maximum_value
FROM list_inputs
ORDER BY row_id;
row_id input_values maximum_value
------ -------------------- -------------
1 [521, 522] 522
2 [531, 539, 533] 539
3 [541, 542, 549, 544] 549
list_median(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [2.0, 8.0]::DOUBLE[]),
(2, [1.0, 5.0, 9.0]::DOUBLE[]),
(3, [2.0, 4.0, 6.0, 8.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_median(input_values) AS median_value
FROM list_inputs
ORDER BY row_id;
row_id input_values median_value
------ -------------------- ------------
1 [2.0, 8.0] 5.0
2 [1.0, 5.0, 9.0] 5.0
3 [2.0, 4.0, 6.0, 8.0] 5.0
list_min(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [551, 552]::INTEGER[]),
(2, [569, 561, 563]::INTEGER[]),
(3, [579, 572, 573, 574]::INTEGER[])
)
SELECT
row_id,
input_values,
list_min(input_values) AS minimum_value
FROM list_inputs
ORDER BY row_id;
row_id input_values minimum_value
------ -------------------- -------------
1 [551, 552] 551
2 [569, 561, 563] 561
3 [579, 572, 573, 574] 572
list_mode(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [581, 581]::INTEGER[]),
(2, [591, 592, 591]::INTEGER[]),
(3, [601, 602, 602, 603]::INTEGER[])
)
SELECT
row_id,
input_values,
list_mode(input_values) AS modal_value
FROM list_inputs
ORDER BY row_id;
row_id input_values modal_value
------ -------------------- -----------
1 [581, 581] 581
2 [591, 592, 591] 591
3 [601, 602, 602, 603] 602
list_negative_inner_product(list1, list2)
WITH list_inputs(row_id, left_values, right_values) AS (
VALUES
(1, [2.0, 3.0]::DOUBLE[], [4.0, 5.0]::DOUBLE[]),
(2, [1.0, 2.0, 3.0]::DOUBLE[], [3.0, 2.0, 1.0]::DOUBLE[]),
(3, [1.0, 1.0, 2.0, 2.0]::DOUBLE[], [2.0, 3.0, 4.0, 5.0]::DOUBLE[])
)
SELECT
row_id,
left_values,
right_values,
list_negative_inner_product(left_values, right_values) AS negative_inner_product
FROM list_inputs
ORDER BY row_id;
row_id left_values right_values negative_inner_product
------ -------------------- -------------------- ----------------------
1 [2.0, 3.0] [4.0, 5.0] -23.0
2 [1.0, 2.0, 3.0] [3.0, 2.0, 1.0] -10.0
3 [1.0, 1.0, 2.0, 2.0] [2.0, 3.0, 4.0, 5.0] -23.0
list_position(list, element)
WITH list_inputs(row_id, input_values, sought_value) AS (
VALUES
(1, [611, 612]::INTEGER[], 612),
(2, [621, 622, 623]::INTEGER[], 629),
(3, [631, 632, 633, 634]::INTEGER[], 633)
)
SELECT
row_id,
input_values,
sought_value,
list_position(input_values, sought_value) AS value_position
FROM list_inputs
ORDER BY row_id;
row_id input_values sought_value value_position
------ -------------------- ------------ --------------
1 [611, 612] 612 2
2 [621, 622, 623] 629 NULL
3 [631, 632, 633, 634] 633 3
list_prepend(element, list)
WITH list_inputs(row_id, prepended_value, input_values) AS (
VALUES
(1, 640, [641, 642]::INTEGER[]),
(2, 650, [651, 652, 653]::INTEGER[]),
(3, 660, [661, 662, 663, 664]::INTEGER[])
)
SELECT
row_id,
prepended_value,
input_values,
list_prepend(prepended_value, input_values) AS prepended_values
FROM list_inputs
ORDER BY row_id;
row_id prepended_value input_values prepended_values
------ --------------- -------------------- -------------------------
1 640 [641, 642] [640, 641, 642]
2 650 [651, 652, 653] [650, 651, 652, 653]
3 660 [661, 662, 663, 664] [660, 661, 662, 663, 664]
list_product(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [2, 3]::INTEGER[]),
(2, [2, 3, 4]::INTEGER[]),
(3, [1, 2, 3, 4]::INTEGER[])
)
SELECT
row_id,
input_values,
list_product(input_values) AS product_value
FROM list_inputs
ORDER BY row_id;
row_id input_values product_value
------ ------------ -------------
1 [2, 3] 6.0
2 [2, 3, 4] 24.0
3 [1, 2, 3, 4] 24.0
list_reduce(list, lambda(x,y)[, initial_value])
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [671, 672]::INTEGER[]),
(2, [681, 682, 683]::INTEGER[]),
(3, [691, 692, 693, 694]::INTEGER[])
)
SELECT
row_id,
input_values,
list_reduce(
input_values,
lambda accumulator, value: accumulator + value
) AS reduced_sum
FROM list_inputs
ORDER BY row_id;
row_id input_values reduced_sum
------ -------------------- -----------
1 [671, 672] 1343
2 [681, 682, 683] 2046
3 [691, 692, 693, 694] 2770
list_resize(list, size[[, value]])
WITH list_inputs(row_id, input_values, target_size, fill_value) AS (
VALUES
(1, [701, 702]::INTEGER[], 3, 799),
(2, [711, 712, 713]::INTEGER[], 5, 798),
(3, [721, 722, 723, 724]::INTEGER[], 2, 797)
)
SELECT
row_id,
input_values,
target_size,
fill_value,
list_resize(input_values, target_size, fill_value) AS resized_values
FROM list_inputs
ORDER BY row_id;
row_id input_values target_size fill_value resized_values
------ -------------------- ----------- ---------- -------------------------
1 [701, 702] 3 799 [701, 702, 799]
2 [711, 712, 713] 5 798 [711, 712, 713, 798, 798]
3 [721, 722, 723, 724] 2 797 [721, 722]
list_reverse(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [731, 732]::INTEGER[]),
(2, [741, 742, 743]::INTEGER[]),
(3, [751, 752, 753, 754]::INTEGER[])
)
SELECT
row_id,
input_values,
list_reverse(input_values) AS reversed_values
FROM list_inputs
ORDER BY row_id;
row_id input_values reversed_values
------ -------------------- --------------------
1 [731, 732] [732, 731]
2 [741, 742, 743] [743, 742, 741]
3 [751, 752, 753, 754] [754, 753, 752, 751]
list_reverse_sort(list[, col1])
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['lime', 'apple']::VARCHAR[]),
(2, ['pear', 'banana', 'mango']::VARCHAR[]),
(3, ['kiwi', 'orange', 'grape', 'fig']::VARCHAR[])
)
SELECT
row_id,
input_values,
list_reverse_sort(input_values) AS descending_values
FROM list_inputs
ORDER BY row_id;
row_id input_values descending_values
------ -------------------------- --------------------------
1 [lime, apple] [lime, apple]
2 [pear, banana, mango] [pear, mango, banana]
3 [kiwi, orange, grape, fig] [orange, kiwi, grape, fig]
list_select(value_list, index_list)
WITH list_inputs(row_id, value_list, index_list) AS (
VALUES
(1, [761, 762]::INTEGER[], [2]::BIGINT[]),
(2, [771, 772, 773]::INTEGER[], [3, 1]::BIGINT[]),
(3, [781, 782, 783, 784]::INTEGER[], [4, 2, 1]::BIGINT[])
)
SELECT
row_id,
value_list,
index_list,
list_select(value_list, index_list) AS selected_values
FROM list_inputs
ORDER BY row_id;
row_id value_list index_list selected_values
------ -------------------- ---------- ---------------
1 [761, 762] [2] [762]
2 [771, 772, 773] [3, 1] [773, 771]
3 [781, 782, 783, 784] [4, 2, 1] [784, 782, 781]
list_sem(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [6.0, 10.0]::DOUBLE[]),
(2, [12.0, 18.0, 30.0]::DOUBLE[]),
(3, [7.0, 14.0, 21.0, 35.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_sem(input_values) AS standard_error
FROM list_inputs
ORDER BY row_id;
row_id input_values standard_error
------ ----------------------- -----------------
1 [6.0, 10.0] 1.414213562373095
2 [12.0, 18.0, 30.0] 4.320493798938574
3 [7.0, 14.0, 21.0, 35.0] 5.176569810212164
list_skewness(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1.0, 2.0, 4.0]::DOUBLE[]),
(2, [1.0, 2.0, 3.0, 8.0]::DOUBLE[]),
(3, [1.0, 2.0, 3.0, 4.0, 10.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_skewness(input_values) AS skewness_value
FROM list_inputs
ORDER BY row_id;
row_id input_values skewness_value
------ -------------------------- ------------------
1 [1.0, 2.0, 4.0] 0.9352195295828271
2 [1.0, 2.0, 3.0, 8.0] 1.5970779829307837
3 [1.0, 2.0, 3.0, 4.0, 10.0] 1.6970562748477134
list_slice(list, begin, end)
WITH list_inputs(row_id, input_values, begin_index, end_index) AS (
VALUES
(1, [791, 792]::INTEGER[], 1, 1),
(2, [801, 802, 803]::INTEGER[], 2, 3),
(3, [811, 812, 813, 814]::INTEGER[], 2, 4)
)
SELECT
row_id,
input_values,
begin_index,
end_index,
list_slice(input_values, begin_index, end_index) AS sliced_values
FROM list_inputs
ORDER BY row_id;
row_id input_values begin_index end_index sliced_values
------ -------------------- ----------- --------- ---------------
1 [791, 792] 1 1 [791]
2 [801, 802, 803] 2 3 [802, 803]
3 [811, 812, 813, 814] 2 4 [812, 813, 814]
list_slice(list, begin, end, step)
WITH list_inputs(row_id, input_values, begin_index, end_index, step_size) AS (
VALUES
(1, [821, 822, 823]::INTEGER[], 1, 3, 2),
(2, [831, 832, 833, 834]::INTEGER[], 1, 4, 2),
(3, [841, 842, 843, 844, 845]::INTEGER[], 2, 5, 2)
)
SELECT
row_id,
input_values,
begin_index,
end_index,
step_size,
list_slice(input_values, begin_index, end_index, step_size) AS sliced_values
FROM list_inputs
ORDER BY row_id;
row_id input_values begin_index end_index step_size sliced_values
------ ------------------------- ----------- --------- --------- -------------
1 [821, 822, 823] 1 3 2 [821, 823]
2 [831, 832, 833, 834] 1 4 2 [831, 833]
3 [841, 842, 843, 844, 845] 2 5 2 [842, 844]
list_sort(list[, col1][, col2])
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [852, 851]::INTEGER[]),
(2, [863, 861, 862]::INTEGER[]),
(3, [874, 872, 871, 873]::INTEGER[])
)
SELECT
row_id,
input_values,
list_sort(input_values) AS sorted_values
FROM list_inputs
ORDER BY row_id;
row_id input_values sorted_values
------ -------------------- --------------------
1 [852, 851] [851, 852]
2 [863, 861, 862] [861, 862, 863]
3 [874, 872, 871, 873] [871, 872, 873, 874]
list_stddev_pop(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [2.0, 6.0]::DOUBLE[]),
(2, [2.0, 4.0, 6.0]::DOUBLE[]),
(3, [1.0, 3.0, 5.0, 7.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_stddev_pop(input_values) AS population_stddev
FROM list_inputs
ORDER BY row_id;
row_id input_values population_stddev
------ -------------------- -----------------
1 [2.0, 6.0] 2.0
2 [2.0, 4.0, 6.0] 1.632993161855452
3 [1.0, 3.0, 5.0, 7.0] 2.23606797749979
list_stddev_samp(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [13.0, 19.0]::DOUBLE[]),
(2, [14.0, 20.0, 29.0]::DOUBLE[]),
(3, [6.0, 12.0, 24.0, 30.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_stddev_samp(input_values) AS sample_stddev
FROM list_inputs
ORDER BY row_id;
row_id input_values sample_stddev
------ ----------------------- ------------------
1 [13.0, 19.0] 4.242640687119285
2 [14.0, 20.0, 29.0] 7.54983443527075
3 [6.0, 12.0, 24.0, 30.0] 10.954451150103322
list_string_agg(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, ['ant', 'bee']::VARCHAR[]),
(2, ['cat', 'dog', 'eel']::VARCHAR[]),
(3, ['fox', 'goat', 'hare', 'ibis']::VARCHAR[])
)
SELECT
row_id,
input_values,
list_string_agg(input_values) AS joined_values
FROM list_inputs
ORDER BY row_id;
row_id input_values joined_values
------ ----------------------- ------------------
1 [ant, bee] ant,bee
2 [cat, dog, eel] cat,dog,eel
3 [fox, goat, hare, ibis] fox,goat,hare,ibis
list_sum(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [881, 882]::INTEGER[]),
(2, [891, 892, 893]::INTEGER[]),
(3, [901, 902, 903, 904]::INTEGER[])
)
SELECT
row_id,
input_values,
list_sum(input_values) AS total_value
FROM list_inputs
ORDER BY row_id;
row_id input_values total_value
------ -------------------- -----------
1 [881, 882] 1763
2 [891, 892, 893] 2676
3 [901, 902, 903, 904] 3610
list_transform(list, lambda(x))
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1, 2]::INTEGER[]),
(2, [3, 4, 5]::INTEGER[]),
(3, [6, 7, 8, 9]::INTEGER[])
)
SELECT
row_id,
input_values,
list_transform(input_values, lambda value: value * 10) AS transformed_values
FROM list_inputs
ORDER BY row_id;
row_id input_values transformed_values
------ ------------ ------------------
1 [1, 2] [10, 20]
2 [3, 4, 5] [30, 40, 50]
3 [6, 7, 8, 9] [60, 70, 80, 90]
list_unique(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [911, 911]::INTEGER[]),
(2, [921, 922, 921]::INTEGER[]),
(3, [931, 932, 933, 931]::INTEGER[])
)
SELECT
row_id,
input_values,
list_unique(input_values) AS unique_count
FROM list_inputs
ORDER BY row_id;
row_id input_values unique_count
------ -------------------- ------------
1 [911, 911] 1
2 [921, 922, 921] 2
3 [931, 932, 933, 931] 3
list_var_pop(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [10.0, 14.0]::DOUBLE[]),
(2, [10.0, 20.0, 30.0]::DOUBLE[]),
(3, [2.0, 6.0, 10.0, 14.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_var_pop(input_values) AS population_variance
FROM list_inputs
ORDER BY row_id;
row_id input_values population_variance
------ ---------------------- -------------------
1 [10.0, 14.0] 4.0
2 [10.0, 20.0, 30.0] 66.66666666666667
3 [2.0, 6.0, 10.0, 14.0] 20.0
list_var_samp(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [11.0, 15.0]::DOUBLE[]),
(2, [10.0, 15.0, 20.0]::DOUBLE[]),
(3, [3.0, 7.0, 11.0, 15.0]::DOUBLE[])
)
SELECT
row_id,
input_values,
list_var_samp(input_values) AS sample_variance
FROM list_inputs
ORDER BY row_id;
row_id input_values sample_variance
------ ---------------------- ------------------
1 [11.0, 15.0] 8.0
2 [10.0, 15.0, 20.0] 25.0
3 [3.0, 7.0, 11.0, 15.0] 26.666666666666668
list_where(value_list, mask_list)
WITH list_inputs(row_id, value_list, mask_list) AS (
VALUES
(1, [941, 942]::INTEGER[], [true, false]::BOOLEAN[]),
(2, [951, 952, 953]::INTEGER[], [false, true, true]::BOOLEAN[]),
(3, [961, 962, 963, 964]::INTEGER[], [true, false, true, false]::BOOLEAN[])
)
SELECT
row_id,
value_list,
mask_list,
list_where(value_list, mask_list) AS selected_values
FROM list_inputs
ORDER BY row_id;
row_id value_list mask_list selected_values
------ -------------------- -------------------------- ---------------
1 [941, 942] [true, false] [941]
2 [951, 952, 953] [false, true, true] [952, 953]
3 [961, 962, 963, 964] [true, false, true, false] [961, 963]
list_zip(list_1, ..., list_n[, truncate])
WITH list_inputs(row_id, number_list, label_list) AS (
VALUES
(1, [971, 972]::INTEGER[], ['a', 'b']::VARCHAR[]),
(2, [981, 982, 983]::INTEGER[], ['c', 'd', 'e']::VARCHAR[]),
(3, [991, 992, 993, 994]::INTEGER[], ['f', 'g', 'h', 'i']::VARCHAR[])
)
SELECT
row_id,
number_list,
label_list,
list_zip(number_list, label_list) AS zipped_values
FROM list_inputs
ORDER BY row_id;
row_id number_list label_list zipped_values
------ -------------------- ------------ ----------------------------------------
1 [971, 972] [a, b] [(971, a), (972, b)]
2 [981, 982, 983] [c, d, e] [(981, c), (982, d), (983, e)]
3 [991, 992, 993, 994] [f, g, h, i] [(991, f), (992, g), (993, h), (994, i)]
repeat(list, count)
WITH list_inputs(row_id, input_values, repeat_count) AS (
VALUES
(1, [10, 11]::INTEGER[], 2),
(2, [20, 21, 22]::INTEGER[], 3),
(3, [30, 31, 32, 33]::INTEGER[], 1)
)
SELECT
row_id,
input_values,
repeat_count,
repeat(input_values, repeat_count) AS repeated_values
FROM list_inputs
ORDER BY row_id;
row_id input_values repeat_count repeated_values
------ ---------------- ------------ ------------------------------------
1 [10, 11] 2 [10, 11, 10, 11]
2 [20, 21, 22] 3 [20, 21, 22, 20, 21, 22, 20, 21, 22]
3 [30, 31, 32, 33] 1 [30, 31, 32, 33]
unnest(list)
WITH list_inputs(row_id, input_values) AS (
VALUES
(1, [1001, 1002]::INTEGER[]),
(2, [1011, 1012, 1013]::INTEGER[]),
(3, [1021, 1022, 1023, 1024]::INTEGER[])
)
SELECT
row_id,
input_values,
unnest(input_values) AS unnested_value
FROM list_inputs
ORDER BY row_id, unnested_value;
row_id input_values unnested_value
------ ------------------------ --------------
1 [1001, 1002] 1001
1 [1001, 1002] 1002
2 [1011, 1012, 1013] 1011
2 [1011, 1012, 1013] 1012
2 [1011, 1012, 1013] 1013
3 [1021, 1022, 1023, 1024] 1021
3 [1021, 1022, 1023, 1024] 1022
3 [1021, 1022, 1023, 1024] 1023
3 [1021, 1022, 1023, 1024] 1024
regexp_extract(string, pattern, name_list[, options])
SELECT
1 AS row_id,
CAST(
regexp_extract(
'2024-07',
'^([0-9]+)-([0-9]+)$',
['year', 'month']::VARCHAR[]
) AS VARCHAR
) AS extracted_values
UNION ALL
SELECT
2,
CAST(
regexp_extract(
'2025-08-19',
'^([0-9]+)-([0-9]+)-([0-9]+)$',
['year', 'month', 'day']::VARCHAR[]
) AS VARCHAR
)
UNION ALL
SELECT
3,
CAST(
regexp_extract(
'duckdb-1-4-2',
'^([a-z]+)-([0-9]+)-([0-9]+)-([0-9]+)$',
['kind', 'major', 'minor', 'patch']::VARCHAR[]
) AS VARCHAR
)
ORDER BY row_id;
row_id extracted_values
------ ----------------------------------------------------
1 {'year': 2024, 'month': 07}
2 {'year': 2025, 'month': 08, 'day': 19}
3 {'kind': duckdb, 'major': 1, 'minor': 4, 'patch': 2}
Use array_extract or list_extract with the collection and its index. DuckDB list indexes begin at 1, so index 1 returns the first item in the examples.
Use list_filter with a lambda expression to keep values that satisfy a condition. Use list_where when you already have a Boolean mask that identifies the values to keep.
Use list_transform with a lambda expression. DuckDB applies the expression to every list element and returns the transformed list.
Use list_aggregate with an aggregate function name, or choose a dedicated helper such as list_sum, list_avg, list_min, or list_max.
Use unnest to emit each list element as an output row. The example above preserves the source row identifier alongside every unnested value.
Explore date and time representation in Timestamp 101: Unit and Precision Are Different.