Database Management Systems
DuckDB has a bunch of functions that allow us to extract data from JSON documents. For example, there’s the json_extract() function, which extracts JSON from the specified JSON document.
Often times we’ll need to extract multiple values within the same query. For example, we may need to extract both a user’s name and age, so that they’re returned in two separate columns.
DuckDB’s json_type() function is a useful utility for inspecting JSON data structures. It helps us determine what type of data we’re working with at any level within a JSON document. This function can be handy when we need to validate data types or handle different JSON structures programmatically.
Like most other DBMSs, DuckDB provides allows us to add and subtract intervals to/from date, timestamp, and time values. To perform a subtraction, we can use the minus (-) operator or the date_add() function (in conjunction with the minus operator).
Below are examples of using each of these options to subtract seconds from date/time values.
DuckDB has a json_extract() function that extracts JSON from a JSON document. DuckDB also has a function called json_extract_string() that does a similar thing, but extracts its value as a string.
This article takes a look at these two functions side by side.
DuckDB provides us with a few options for slicing lists. Slicing a list is where we take a sublist or “slice” from the list, rather than the whole list. We specify the start and end position for which to return the slice. We can also specify a step, which allows us to skip one or more elements along the slice.
When it comes to converting between data types, DuckDB performs implicit conversions when required, while also enabling us to perform explicit conversions. Implicit conversions are performed automatically by DuckDB when we do some other operation, such as use a function that requires its argument/s in a different data type than the one we’re providing. Regarding explicit conversions, we have the option of using a function like cast() or try_cast(), or using the shorthand method.
In this article we’ll take a quick look at how to convert between data types using the shorthand method.
In DuckDB, the json_array_length() function is used to determine the number of elements in a JSON array. This function can return the count of items in the top-level array or in a nested array within it. It’s especially useful when working with complex JSON structures where arrays are embedded inside objects or other arrays. By combining it with JSON path expressions, you can target specific portions of the data for more precise analysis.
When working with lists in DuckDB, sometimes you might want to return just the first value, rather than the whole list. Fortunately, DuckDB provides us with several ways to do this.
The make_timestamp() function in DuckDB is a handy tool for creating timestamp values from individual components. It allows us to construct timestamp values using two alternative approaches; by specifying the number of milliseconds from epoch, or by specifying the year, month, day, hour, minute, and second components separately.
Let’s take a look at the make_timestamp() function, along with some examples.
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.