This article demonstrates how to output your DuckDB query results in TCL list format, which can be useful when integrating with TCL scripts or systems that expect TCL-formatted data.
how to
Using Bracket Notation to Slice a List in DuckDB
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.
3 Ways to Get the Average From a List of Numbers in DuckDB
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.
Get the Last Day of the Month in DuckDB with LAST_DAY()
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.
Using LIST_RESIZE() to Resize a List in DuckDB
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.
Detect Infinite Values in DuckDB with the ISINF() Function
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.
Filtering a Query Based on JSON Path Existence in DuckDB
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.
Using JSON_CONTAINS() in DuckDB to Check if a Value Exists in a JSON Document
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.
Switching to Markdown Mode in DuckDB
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.
4 Functions to Get the ISO Weekday in DuckDB
DuckDB provides us with a good selection of functions for working with dates and timestamps. One of the things we might find ourselves needing to do is extracting the ISO weekday from a date or timestamp—a numeric value where Monday is represented as 1 and Sunday as 7.
This article presents four functions we can use to get the ISO weekday from a date in DuckDB.