In DuckDB, we can use the json_contains() function to check whether a specified JSON value is contained within another JSON structure. It returns a boolean (TRUE or FALSE) that indicates whether or not the value was found. We can also check for key value pairs, or even a full JSON object within the JSON document.
Tag: functions
Understanding DuckDB’s ARG_MIN_NULL() Function
DuckDB has a arg_min_null() function that works in a similar way to the arg_min() function. That is, it finds the row with the minimum value in one column and returns the corresponding value from another column at that row.
But there’s also a difference between these two functions. The main difference is in the way they deal with NULL values. Also, arg_min_null() only accepts two arguments, whereas arg_min() accepts an optional third argument. Additionally, there aren’t any aliases for arg_min_null() at the time of writing (arg_min() has a couple of aliases).
In this article we’ll look at how arg_min_null() works, and we’ll compare it with arg_min() to see how each function handles NULL values.
LIST_REVERSE() vs LIST_REVERSE_SORT() in DuckDB: What’s the Difference?
DuckDB has a good range of functions for dealing with lists and arrays. Amongst these are list_reverse() and a list_reverse_sort(). Looking at their names, you could be forgiven for thinking that these do the same thing. But they don’t.
If you’re wondering why DuckDB has a list_reverse() and a list_reverse_sort() function, read on.
Convert Column Values to a JSON Array with DuckDB’s JSON_GROUP_ARRAY() Function
The json_group_array() function in DuckDB is used to aggregate values into a JSON array, making it especially useful when working with structured or semi-structured data such as JSON. This function is part of DuckDB’s JSON extension and works similarly to DuckDB’s string_agg() or the group_concat() function in some other RDBMSs, but instead of returning a delimited string, it returns a well-formed JSON array.
This function is particularly helpful when we need to represent grouped or hierarchical data in a JSON format for export, reporting, or further transformation.
Continue readingAn Overview of the TODAY() Function in DuckDB
DuckDB has a today() function that returns the current date. It’s similar to the current_date function, which does the same thing.
Here, we’ll look at how the today() function works, along with some basic examples.
3 Ways to Get the Weighted Average in DuckDB
Weighted averages are common calculations in data analysis, allowing us to assign different levels of importance to individual values in our dataset. Unlike simple averages, where each value has equal impact, weighted averages let us incorporate the relative significance of each observation. This is particularly valuable for scenarios like calculating GPA (where courses have different credit weights), investment portfolio returns (where assets have varying allocations), or quality ratings (where reviewers have different expertise levels).
In this article, we’ll explore three ways of calculating weighted averages in DuckDB.
Continue readingExtract All Values From a JSON Document With DuckDB’s JSON_TRANSFORM() Function
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.
Continue readingUsing JSON_TYPE() in DuckDB to Get the Data Type of a Given JSON Value
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.
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.
Continue readingGet 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.