⌘K ctrl+k
Search Shortcut cmd + k | ctrl + k
Variant Functions

This section describes functions that operate on the VARIANT type.

New Support for the variant_* functions is introduced in DuckDB 2.0.

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_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

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);
© 2026 DuckDB Foundation, Amsterdam NL
DuckDB Home Code of Conduct Trademark Use Blog