DuckDB provides a few options for slicing lists, including the list_slice() function and its alias, array_slice(). We can also use bracket notation for a slightly more concise option. Bracket notation is where we append the slice details to the list.
Below are examples of using bracket notation to slice lists in DuckDB.
DuckDB’s LIMIT and SAMPLE clauses both allow users to work with subsets of data, but they serve fundamentally different purposes and operate on different principles. While LIMIT returns a fixed number of rows from the beginning of a result set, SAMPLE provides a random sample from the dataset.
When presented with a list of numbers, there are many things we might want to do with it. One is to get the average value of all values in the list. Fortunately DuckDB provides us with several ways to do this.
DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.
The way it works is, we pass a date to the function, and it returns the date of the last day of that month.
DuckDB provides us with a list_resize() function for those occasions where we need to resize a list. It also offers an alias called array_resize() that does exactly the same thing. When we resize a list using these functions, we specify how big we want the resulting list to be, and we can also specify a value to use for any extra elements that are added to the list.
DuckDB has a repeat() function that enables us to output a repeating value easily and concisely. The way it works is that we pass the value to the function, followed by how many times we want it to be repeated. It returns that value repeated the specified number of times.
In DuckDB, we can use the ISINF() function to check whether a value is finite. DuckDB supports infinite values, and so this function allows us to check for that. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. The function also works on date and timestamp values, as they can be infinite too.
Occasionally we might need to filter data based on the existence of a given path in a JSON document in the database. When using DuckDB, we can achieve this outcome with the JSON_EXISTS() function. This function returns true or false, depending on whether the specified path exists. Therefore, we can use it to help us filter our query on that basis.
In DuckDB, we can use the json_contains() function to check whether a specified JSON value is contained within another JSON structure. It returns a boolean (TRUE or FALSE) that indicates whether or not the value was found. We can also check for key value pairs, or even a full JSON object within the JSON document.
This article shows you how to output your DuckDB query results in Markdown table format when using the DuckDB command line interface (CLI). This can be useful when creating documentation or preparing content for platforms that support Markdown.