VARIANT

VARIANT stores semi-structured values using Parquet/Iceberg Variant encoding. Compared with JSONB, VARIANT can preserve supported leaf types such as integers with their width, decimals, timestamps, binary values, arrays, and nested objects.

Define a VARIANT type

Syntax: VARIANT

Example

CREATE TABLE events (
    id INT PRIMARY KEY,
    payload VARIANT
);

Add values to a VARIANT column

You can populate a VARIANT column in two main ways:

Parse a serialized value with ::VARIANT.
Convert an existing SQL value with to_variant(...).

Examples

INSERT INTO events VALUES
    (1, '{"user":{"id":42,"active":true}}'::VARIANT),
    (2, to_variant(ROW(1, 'blue')::STRUCT<id INT, label VARCHAR>)),
    (3, to_variant(MAP {'retry_primary': 3, 'retry_secondary': 5})),
    (4, to_variant(ROW('us-east-1', 3)::STRUCT<region VARCHAR, retries INT>));

MAP values must share a common value type. Use a typed STRUCT when you need mixed leaf types in one object. When casting from VARCHAR, RisingWave parses the string as a serialized Variant value. to_variant instead boxes the SQL value itself.

SELECT
    '{"a":1}'::VARIANT AS parsed_object,
    to_variant('{"a":1}'::VARCHAR) AS boxed_string;

Access nested values

Use variant_get(value, path) to access nested values and variant_typeof(value) to inspect the stored Variant type.

Example

SELECT
    variant_get(payload, '$.user.id')::VARCHAR AS user_id,
    variant_typeof(variant_get(payload, '$.user.id')) AS user_id_type
FROM events;

If you want invalid paths to return NULL instead of an error, use try_variant_get(value, path).

Casts

RisingWave supports these casts for VARIANT:

From	To
`VARCHAR`	`VARIANT`
`JSONB`	`VARIANT`
`VARIANT`	`VARCHAR`
`VARIANT`	`JSONB`

Limitations

VARIANT cannot be used as a key type. This includes primary keys, index keys, GROUP BY, DISTINCT, ORDER BY, and join keys.
Of the set operations, only UNION ALL is supported for VARIANT columns.
VARIANT is not supported in non-SQL UDF signatures yet.

SQL reference

Python SDK

User-defined functions

Define a VARIANT type

Example

Add values to a VARIANT column

Examples

Access nested values

Example

Casts

Limitations

​Define a VARIANT type

​Example

​Add values to a VARIANT column

​Examples

​Access nested values

​Example

​Casts

​Limitations

Define a VARIANT type

Example

Add values to a VARIANT column

Examples

Access nested values

Example

Casts

Limitations