functions – Page 2

7 Ways to Extract Data from JSON in DuckDB

August 22, 2025 by Ian

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 Use STRING_AGG() in SQL Server with Custom Separators and Sorting

August 8, 2025 by Ian

In SQL Server, STRING_AGG() is an aggregate function that concatenates string values from a group into a single string. It’s a handy tool for doing things like creating comma-separated lists from related data.

In this article we’ll check out how to use STRING_AGG() with different separators. We’ll also see how we can control the order of the concatenated strings.

When to Use CONVERT() vs CAST() for Date Formatting in SQL Server

August 6, 2025 by Ian

When formatting dates in SQL Server you may be wondering whether to use CONVERT() or CAST(). After all, both functions allow us to convert between data types. Let’s take a look at at these two functions and figure out when to use each one.

The Difference Between LIST_SELECT() and LIST_SLICE() in DuckDB

August 2, 2025 by Ian

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.

Extracting the First Non-NULL Value from a List with DuckDB’s LIST_ANY_VALUE() Function

July 25, 2025 by Ian

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.

The Difference Between RANGE() and GENERATE_SERIES() in DuckDB

July 21, 2025 by Ian

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.

Convert a Table to a JSON Document with JSON_GROUP_OBJECT() in DuckDB

July 20, 2025 by Ian

In DuckDB, the json_group_object() function is a convenient way to aggregate data into JSON objects by pairing keys and values across rows within groups.

The function is especially useful when we’re transforming tabular data into a more hierarchical or nested JSON structure for web APIs, reporting, or downstream processing. It helps pivot rows into a single JSON object, making the data more compact and easier to consume in applications that require JSON formats.

Appending Values to Lists with 3 DuckDB Functions

July 18, 2025 by Ian

Appending values to lists is a common task when working with DuckDB and other DBMSs (not to mention programming languages in general). Usually we’ll get a function that’s purpose built for the task. As it turns out, DuckDB provides us with at least three functions to do this task.

3 Functions for Extracting a Value from a List in DuckDB

July 17, 2025 by Ian

DuckDB provides us with a list_extract() function that extracts a value from a list, based on the element’s index in that list.

But as if one function isn’t enough, it also provides us with a couple of aliases that we can use instead of list_extract() if we prefer.

So let’s call it three functions that we can use to extract a value from a list.

DUCKDB_TABLES() Examples

November 15, 2025July 16, 2025 by Ian

The duckdb_tables() function is a system function in DuckDB that provides useful metadata about all tables in your database. The function returns a table containing information about each table, including its schema, name, and various properties. It can be quite a handy tool for database introspection and management.