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.
Tag: what is
Parse 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 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).
TYPEOF() vs PG_TYPEOF() in DuckDB: What’s the Difference?
You may be aware that DuckDB includes a typeof() function that works just like the SQLite equivalent; it returns the data type of its argument. But did you know that DuckDB also provides us with a pg_typeof() function that does essentially the same thing?
So why would DuckDB need a pg_typeof() function that does basically the same thing as typeof()? Let’s find out!
A Quick Look at DuckDB’s LIST_WHERE() Function
DuckDB has a list_where() function that returns a list after a user-supplied mask has been applied. We pass the list as the first argument, and a list of Boolean values as the second. The list of Boolean values is applied as a mask to the list, which determines which values in the list are returned.
LIMIT vs SAMPLE in DuckDB: What’s the Difference?
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.
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.
Continue readingUsing 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.