DuckDB provides us with a json_transform_strict() function that works just like the json_transform() function, except that it throws an error when type casting fails. The json_transform() function on the other hand, returns NULL for the respective field in such cases.
Category: DBMS
Database Management Systems
5 Ways to Convert a List to a String in DuckDB
If we have a list in DuckDB, and we want to convert it to a string, there are a number of options on the table. Basically, what we want to do is combine/concatenate all elements into a single string. Let’s take a look at five ways to convert a list to a string in DuckDB.
Continue readingOutput Query Results as a Tab-Separated List in DuckDB
DuckDB’s command-line interface (CLI) provides a simple built-in method for outputting query results as tab-separated values (TSV) using the .mode tabs command.
This article takes a quick look at this output mode and explores some of the options available for when outputting query results as a tab-separated list.
Continue readingParse and Minify JSON with DuckDB’s JSON() Function
One of the many JSON functions that DuckDB provides is the json() function, which parses and minifies JSON values. We pass the JSON data to the function, and it parses and minifies the JSON, then returns the result.
4 DuckDB Functions that Find the Position of a Value in a List
As with most other database management systems (DBMSs), DuckDB provides us with a way of finding the position of an element in a list. This is the index at which the specified element occurs in the list.
Actually, DuckDB provides us with four synonymous functions for doing this (i.e. they all do the same thing).
Continue readingGet the Day Name from a Date in DuckDB
DuckDB provides us with a couple of ways to get the weekday name from a date or timestamp value. This could be the full day name, such as Monday, or the short/abbreviated day name, such as Mon.
Here are two ways to return the day name from a date in DuckDB.
Continue readingThe Difference Between CAST() and TRY_CAST() in DuckDB
DuckDB offers two primary functions for type conversion: cast() and try_cast(). While they serve similar purposes, their behavior when handling invalid conversions differs significantly, which can greatly impact our data processing workflows.
Using JSON_KEYS() in DuckDB to Get the Keys in a JSON Document
DuckDB has a json_keys() function that we can use to extract the keys from a JSON object. It returns the keys as a list of strings (LIST of VARCHAR).
3 Ways to Get the Minimum Value from a List in DuckDB
Most SQL developers are familiar with the min() function that allows us to get the minimum value from a data set. But if we want to get the minimum value from a list, passing the list to the min() function won’t quite cut it. But don’t despair! Finding the minimum value in a list is just as easy. Here are three ways to do it. And yes, we can even use the min() function if we want.
4 Ways to Concatenate 3 or More Lists in DuckDB
If you’ve ever used list_concat() or any of its aliases to concatenate lists in DuckDB, you may have been disappointed to find out that it only concatenates two lists. Any more than two lists and it returns an error. At least that’s how it works at the time of this writing.
Fortunately, there are some alternatives we can use in order to concatenate three or more lists.
Here are four ways to concatenate three or more lists in DuckDB.
Continue reading