The json_transform() function in DuckDB is a handy tool for converting JSON strings into structured data types like STRUCT, MAP, and LIST. This allows you to directly query and manipulate nested JSON data using standard SQL, making it much easier to work with complex JSON objects and arrays.
Think of it as a way to cast your JSON data into a more usable, typed format within your database.
The json_group_structure() function in DuckDB is an aggregate function that inspects all JSON values within a group and returns a JSON representation of their structure. It essentially infers a “schema” for the JSON objects in that group. This can be useful for understanding the shape and consistency of your JSON data.
DuckDB has a fsum() function that can be used instead of the regular sum() function in order to get more accurate results. fsum() calculates the sum using a floating point summation method known as Kahan summation (or compensated summation).
This method helps reduce the accumulation of rounding errors that can occur when summing many floating point numbers when using the regular sum() function.
In analytical SQL workloads, expressing weighted averages can sometimes involve verbose expressions such as combining the sum() function with other operators. DuckDB streamlines this with its native weighted_avg() aggregate function, allowing us to compute weighted averages directly and efficiently. The weighted_avg() function enhances both clarity and speed when dealing with data where values contribute unequally — such as population-adjusted metrics or revenue-weighted scores.
This article explores the weighted_avg() in DuckDB, along with examples to demonstrate its usage.
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.
DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its various aggregate functions is the product() function, which calculates the product of a set of numeric values.
In this article, we’ll explore how the product() function works with some basic examples.
In DuckDB, bool_and() is an aggregate function that returns true if every input value is true, otherwise it returns false.
Here are some basic examples that demonstrate how it works.
In DuckDB, the arg_max_null() function works in a similar way to the arg_max() function, in that it finds the row with the maximum value in one column and returns the corresponding value from another column at that row.
But where it differs from arg_max() is in the way it deals with NULL values. Also, arg_max_null() only accepts two arguments, whereas arg_max() accepts an optional third argument. Additionally, there aren’t any aliases for arg_max_null() at the time of writing (arg_max() has a couple of aliases).
In this article we’ll look at how arg_max_null() works, and we’ll compare it with arg_max() to see how each function handles NULL values.
DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its many functions is one called last(). This function returns the last value in a column.
Let’s take a look at the last() function in DuckDB.
DuckDB has a max_by() function that finds the row with the maximum value in one column and returns the corresponding value from another column at that row.
Below are some examples that demonstrate how it works.