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.
what is
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.
A Look at DuckDB’s LIST_SORT() Function
DuckDB has a list_sort() function that does exactly what its name promises; sorts lists.
While the easiest way to use this function is to simply pass a list, we can also pass other arguments to fine-tune the results.
Using DATE_PART() to Extract Date and Time Components in DuckDB
DuckDB provides us with a good selection of functions for working with date/time values. Among them is date_part(), which we can use to extract specific components from dates, timestamps, and intervals.
In this article, we’ll look how the date_part() function works, along with some basic examples.
Looking at DuckDB’s STRING_AGG() Function
DuckDB has a string_agg() function, which allows us to concatenate strings from a group of rows into a single string. This function can be useful when we need to aggregate text data in a meaningful way.
In this article, we’ll explore how the string_agg() function works, along with some simple examples to demonstrate its usage.
Removing Duplicates from a List in DuckDB with the LIST_DISTINCT() Function
DuckDB has a function called list_distinct() that enables us to remove duplicate values from a list. Any value that appears more than once is “deduped” and is returned once in the resulting list.
The function also removes any NULL values.
4 Functions That Check Whether a List Contains a Given Value in DuckDB
When working with DuckDB, we may need to determine whether a specific value exists within a list or array. Fortunately, DuckDB offers four synonymous functions that enable us to accomplish this.
Unnesting Lists & Structs with DuckDB’s UNNEST() Function
DuckDB has an unnest() function that we can use to unnest lists and structs. Well, it can also be applied to NULL, but that’ll return an empty result.
By “unnest” I mean it takes the list or struct, and it returns its contents as rows in a table. You might say that it converts lists and structs into tables, where each item in the list or struct becomes a row in the table.
Below are examples of using DuckDB’s unnest() function to unnest lists and structs.