Common Table Expressions (CTEs) are a handy way to break down a complex query into readable, reusable pieces. When you need several intermediate results – say, a filtered set, an aggregation, and a ranking – you can stack multiple CTE definitions together. PostgreSQL, SQL Server, MySQL 8+, and many other engines support this syntax.
DuckDB
7 Ways to Extract Data from JSON in DuckDB
Most DuckDB distributions come with the JSON extension, and this extension is loaded upon first use. That means we can go ahead and run queries against JSON data right out of the box. One common task we’ll face when working with JSON is extracting data from within the JSON documents. This can include extracting scalar values, or extracting nested JSON from within the outer document.
DuckDB provides us with multiple ways to extract such data. The option we use will largely depend on our use case. Either way, here are seven options for extracting data from JSON documents in DuckDB.
How to Limit a Date Range to Just Business Days in DuckDB
DuckDB enables us to easily generate a range of dates, for example by using the generate_series() function and specifying the start date and end date. But what if you need to limit the output to just those dates that are business days?
This article provides an easy way to get that result.
The Difference Between LIST_SELECT() and LIST_SLICE() in DuckDB
DuckDB has a list_select() function and a list_slice() function, and both do a similar thing. They allow us to extract values from lists based on their index in the list. But they’re quite different in the way they work. One function allows us to select elements based on a range, while the other function allows us to handpick each element we want returned.
5 Functions that Extract the Month from a Date in DuckDB
Sometimes when working with SQL databases like DuckDB, we need to return the month part from a date for presentation or for further processing. DuckDB provides us with a handful of functions that can help in this regard.
In this article, we’ll look at five different functions we can use to extract the month from a date in DuckDB.
Extracting the First Non-NULL Value from a List with DuckDB’s LIST_ANY_VALUE() Function
DuckDB provides us with a good selection of list functions. This includes a list_any_value() function, which returns the first non-NULL value from a given list.
4 Ways to Get the Last Value from a List in DuckDB
When it comes to working with lists, DuckDB often provides us with multiple methods of performing a given task. Getting the last value from a list is no exception.
Here are four options we can use to extract the last value from a list in DuckDB.
Quick Overview of the List Operators in DuckDB
DuckDB provides us with a bunch of functions and operators that we can use when working with lists. These include operators for doing things like concatenating lists to performing calculations on the lists. Most of these operators are aliases for a corresponding function, so we can choose which one to use in those cases.
This article provides an overview of the six list operators available in DuckDB, along with basic examples to demonstrate their usage.
Subtract Months from a Date in DuckDB
If you find yourself in the situation where you need to subtract a number of months from a date in DuckDB, here are two options for you.
The Difference Between RANGE() and GENERATE_SERIES() in DuckDB
DuckDB offers two handy functions for generating sequences of numbers: range() and generate_series(). While they both do the same thing, and share basically the same syntax, there is one important difference between them.
The primary difference between them is in their inclusivity behavior with regard to the stop value.