Variant Functions

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Functions

Variant Functions

This section describes functions that operate on the VARIANT type.

New Most variant_* functions were introduced in DuckDB 2.0. The variant_extract, variant_normalize and variant_typeof functions were already available in DuckDB 1.5.

Name	Description
`variant_typeof(v)`	Return the full type of a `VARIANT` value, including nested structure.
`variant_type(v)`	Return the top-level type category of a `VARIANT` value.
`variant_keys(v)`	Return the keys of a `VARIANT` object as a list.
`variant_extract(v, field)`	Extract a nested value by object field name or list index. Returns a `VARIANT`.
`variant_extract_string(v, field)`	Extract a nested value as a `VARCHAR`.
`variant_array_length(v)`	Return the number of elements in a `VARIANT` array.
`variant_exists(v, key)`	Return whether the given key exists in a `VARIANT` object.
`variant_contains(haystack, needle)`	Return whether the `needle` `VARIANT` is contained in the `haystack` `VARIANT`.
`variant_comparator(v)`	Return a binary sort key that orders a `VARIANT` value by the logical ordering of `VARIANT` values.
`variant_normalize(v)`	Return a canonical representation of a `VARIANT` value, so that values representing the same data share the same form.
`variant_group_object(key, value)`	Aggregate. Return a `VARIANT` object built from all `key`, `value` pairs in the group.
`variant_group_array(x)`	Aggregate. Collect all values of `x` into a `VARIANT` array.

`variant_typeof(v)`

Description	Return the full type of a `VARIANT` value, including nested structure.
Example	`variant_typeof('{"a": 1}'::JSON::VARIANT)`
Result	`OBJECT(a)`

`variant_type(v)`

Description	Return the top-level type category of a `VARIANT` value.
Example	`variant_type('{"a": 1}'::JSON::VARIANT)`
Result	`OBJECT`

`variant_keys(v)`

Description	Return the keys of a `VARIANT` object as a list.
Example	`variant_keys('{"a": 1, "b": 2}'::JSON::VARIANT)`
Result	`[a, b]`

`variant_extract(v, field)`

Description	Extract a nested value by object field name or list index. Returns a `VARIANT`.
Example	`variant_extract('{"name": "Fry"}'::JSON::VARIANT, 'name')`
Result	`Fry`

`variant_extract_string(v, field)`

Description	Extract a nested value as a `VARCHAR`.
Example	`variant_extract_string('{"name": "Fry"}'::JSON::VARIANT, 'name')`
Result	`Fry`

`variant_array_length(v)`

Description	Return the number of elements in a `VARIANT` array.
Example	`variant_array_length('[1, 2, 3]'::JSON::VARIANT)`
Result	`3`

`variant_exists(v, key)`

Description	Return whether the given key exists in a `VARIANT` object.
Example	`variant_exists('{"a": 1}'::JSON::VARIANT, 'a')`
Result	`true`

`variant_contains(haystack, needle)`

Description	Return whether the `needle` `VARIANT` is contained in the `haystack` `VARIANT`.
Example	`variant_contains('{"user": {"id": 42}}'::JSON::VARIANT, '{"user": {"id": 42}}'::JSON::VARIANT)`
Result	`true`

`variant_comparator(v)`

Description	Return a binary sort key (a `BLOB`) that orders a `VARIANT` value according to the logical ordering of `VARIANT` values. This is used internally so that `VARIANT`s compare and sort consistently with the corresponding native DuckDB types, rather than by their internal byte representation.
Example	`variant_comparator({'a': 1, 'b': 2}::VARIANT)`
Result	A `BLOB` sort key

Values are ordered first by type bucket (for example, booleans before numbers, numbers before strings) and then by value within a bucket, so that sorting a VARIANT column matches the order of the underlying values:

SELECT i
FROM range(-2, 2) t(i)
ORDER BY i::VARIANT;

i
-2
-1
0
1

`variant_normalize(v)`

Description	Return a canonical representation of a `VARIANT` value. Two `VARIANT` values that represent the same data normalize to the same result, regardless of how they were constructed or physically stored. This is useful when comparing or storing `VARIANT` values.
Example	`variant_normalize({'b': [1, 2, 3], 'a': 42}::VARIANT)`
Result	A normalized `VARIANT`

`variant_group_object(key, value)`

Description	Aggregate function. Returns a `VARIANT` object containing all `key`, `value` pairs in the group, where `key` is a `VARCHAR` and `value` is a `VARIANT`. A `NULL` key or a duplicate key raises an error.
Example	`variant_group_object('duck', 'goose'::VARIANT)`
Result	`{'duck': goose}`

New The variant_group_object aggregate was added in DuckDB 2.0.

`variant_group_array(x)`

Description	Collects all values of `x` into a `VARIANT` array, preserving order. Defined as a macro equivalent to `list(x)::VARIANT`, so it has aggregate semantics and also works as a window function. `NULL` values are kept as `VARIANT` nulls.
Example	`variant_group_array(i)`
Result	`[0, 1, 2, 3]`

New The variant_group_array function was added in DuckDB 2.0.

Example

The variant_* functions are useful for inspecting and filtering semi-structured data stored in a VARIANT column:

CREATE TABLE events (payload VARIANT);
INSERT INTO events
VALUES ('{"user": {"id": 42, "tags": ["a", "b"]}}'::JSON::VARIANT);

SELECT variant_type(payload), variant_keys(payload)
FROM events;

SELECT *
FROM events
WHERE variant_contains(payload, {'user': {'id': 42}}::VARIANT);

DuckDB Home Code of Conduct Trademark Use Blog