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.
functions
JSON_EXTRACT() vs JSON_EXTRACT_STRING() in DuckDB: What’s the Difference?
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.
Get the Number of Elements in a JSON Array with JSON_ARRAY_LENGTH() in DuckDB
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.
Create a TIMESTAMP Value in DuckDB with MAKE_TIMESTAMP()
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.
Understanding the JSON_GROUP_STRUCTURE() Function in DuckDB
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.
A Quick Look at DuckDB’s JSON_STRUCTURE() Function
In DuckDB, the json_structure() is used to parse and introspect JSON data, returning the structure of a JSON document. This is especially useful when you’re working with semi-structured JSON data and you want to understand its schema — including nested keys, arrays, and types — without manually inspecting the raw JSON.
How EPOCH_NS() Works in DuckDB
DuckDB provides an epoch_ns() function that returns the Unix epoch time from a given date/time value in nanoseconds.
Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but it can also be expressed in other units, such as milliseconds, microseconds, and nanoseconds. DuckDB has specific functions for each of these units, with epoch_ns() being the function that returns it in nanoseconds.
Understanding DuckDB’s JSON_VALID() Function
Most of the major RDBMSs that support JSON also include a json_valid() function for checking the validity of a JSON document, and DuckDB’s no exception. Like the other RDBMSs, DuckDB’s implementation of json_valid() checks its argument and returns a boolean value to indicate whether or not it’s valid JSON.
3 DuckDB Functions that Prepend a Value to a List
DuckDB provides us with a few functions that prepend a value to a list. Actually, these functions are synonymous, so they all do the same thing, with the same/similar basic syntax.
Using LIST_INTERSECT() to Extract Overlapping Elements from Two Lists in DuckDB
The list_intersect() can be quite a handy function when working with lists in DuckDB. It accepts two lists as arguments, and it returns a list of elements that are common to both lists. It essentially performs a set intersection operation while preserving the list data type.
Duplicate values are ignored, so only one value is returned even if there are multiple occurrences in one or both lists.