DuckDB has a list_slice()
function that enables us to select multiple list items from a list, based on their index range within the list. For example, we can select all list items between position 5 and 10 in a list.
Tag: lists
3 Ways to Add the Numbers in a List in DuckDB
If you ever have a list of numbers in DuckDB that you want to add up, there are several ways to do this. By “add up” I mean, say you have a list that contains three numeric values. You can use the following methods to get a sum of all those values.
Continue readingCheck for Overlapping Elements Across Lists in DuckDB: LIST_HAS_ANY()
DuckDB provides us with the list_has_any()
function that enables us to find any overlapping elements across two lists. The way it works is that we pass two lists, and it returns true
or false
, depending on whether any element exists in both lists.
Understanding LIST_ZIP() in DuckDB
DuckDB has a list_zip()
function that zips one or more lists to a new list with a length of the longest list provided. The new list contains unnamed structs of the elements from the original lists. We pass the lists to the function when we call it. We can also pass a Boolean value to specify whether or not to truncate all lists to the smallest list.
Below are some basic examples that demonstrate how the list_zip()
function works.
How to Output Query Results as Pipe-Separated Lists in the DuckDB CLI
This tutorial walks you through outputting DuckDB query results in list format, which presents each record as a pipe-separated list of values. This format is handy for data processing tasks and when working with tools that expect pipe-delimited input.
Continue readingUnderstanding DuckDB’s LIST_GRADE_UP() Function
Among DuckDB’s many tools for handling list data is the list_grade_up()
function. This function works similarly to a sort operation, but instead of returning the sorted values themselves, it returns the indexes that represent the positions of those values in the original list.
Let’s take a quick look.
Continue readingUsing LIST_UNIQUE() to Count the Unique Elements of a List in DuckDB
If you ever find yourself in the situation where you need to count up the number of unique elements in a list in DuckDB, you’ll be happy to know that there’s a list_unique()
function that does exactly that.
How LIST_SELECT() Works in DuckDB
DuckDB’s list_select()
function allows us to extract specific elements from list columns based on their positions. This can be useful when we need to access or manipulate elements within arrays or lists.
A Quick Overview of LIST_REVERSE_SORT() in DuckDB
While it’s true that DuckDB has a list_sort()
function that allows us to sort lists, it’s also true that there’s a list_reverse_sort()
function that sorts lists in reverse order.
Using LIST_REVERSE() to Reverse the Items in a List in DuckDB
Deep amongst DuckDB’s vast selection of list functions lies the list_reverse()
function. This function reverses the elements in a list. We simply pass the list to the function and it reverses the order of the elements in that list.
Not to be confused with the list_sort()
or list_reverse_sort()
functions, the list_reverse()
function reverses the order of the items as they currently sit in the list. The ...sort()
functions on the other hand, actually perform a sort operation on the list items.