Repository navigation
Description
Part of #273 and apache/datafusion-comet#7300.
What the function does
to_variant_object(expr) converts a nested value into a VARIANT. The top-level type must be ARRAY, MAP or
STRUCT, and nested values can be any type that can be cast to variant. Structs and maps become variant objects,
which are unordered and sorted by key. Map keys must be strings. Any other input fails analysis with
DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION (srcType = the input type, targetType = "VARIANT"). That includes
scalars, intervals and maps with non-string keys. The function is null-intolerant, so SQL NULL input returns SQL
NULL. Added in Spark 4.0.0.
It is the only way to put structs and maps into a variant. CAST(x AS VARIANT) accepts atomic types and arrays of
them, but rejects any struct or map at any depth. Both go through
VariantExpressionEvalUtils.castToVariant, using a VariantBuilder that does not allow duplicate keys.
Examples
SELECT to_variant_object(named_struct('a', 1, 'b', 2));
-- {"a":1,"b":2}
SELECT to_variant_object(array(1, 2, 3));
-- [1,2,3]
SELECT to_variant_object(array(named_struct('a', 1)));
-- [{"a":1}]
SELECT to_variant_object(array(map('a', 2)));
-- [{"a":2}]
SELECT to_json(to_variant_object(map('z', 1, 'y', 2, 'x', 3)));
-- {"x":3,"y":2,"z":1}
SELECT to_json(to_variant_object(named_struct(
'c', array('123', 'true', 'f'),
'b', map('a', '123', 'b', 'true', 'c', 'f'),
'd', map('a', named_struct('i', 132)),
'a', named_struct('i', 0))));
-- {"a":{"i":0},"b":{"a":"123","b":"true","c":"f"},"c":["123","true","f"],"d":{"a":{"i":132}}}
SELECT to_variant_object(1);
-- error DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION
SELECT to_variant_object(map(1, 1));
-- error DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTIONThese come from the ExpressionDescription and VariantExpressionSuite."cast to variant/to_variant_object". The
suite uses literals, and the SQL above is equivalent.
Edge cases to test
- Element encoding (
buildVariant):- TINYINT, SMALLINT, INT and BIGINT use the smallest of INT1, INT2, INT4 and INT8 that fits the value. They do
not follow the column type. - FLOAT stays FLOAT and DOUBLE stays DOUBLE.
- DECIMAL uses DECIMAL4, DECIMAL8 or DECIMAL16, chosen by the value's precision and the column's scale. For
example, aDECIMAL(38,2)value of1.00is stored as DECIMAL4 with scale 2. - STRING uses a short string up to 63 bytes, otherwise a long string. BINARY stays BINARY.
- DATE is days. TIMESTAMP and TIMESTAMP_NTZ are microseconds.
- Nested VARIANT values are copied with their keys re-added to the new dictionary.
- TINYINT, SMALLINT, INT and BIGINT use the smallest of INT1, INT2, INT4 and INT8 that fits the value. They do
- How these render through
to_json(from the suite): booleans and numbers print as JavatoString(4.0,
5.0), and decimals as plain strings. Binary[1,2,3]prints as"AQID", and date 0 as"1970-01-01".
TIMESTAMP 0 prints as"1970-01-01 00:00:00+00:00"in UTC and"1969-12-31 16:00:00-08:00"in
America/Los_Angeles, so it depends onspark.sql.session.timeZone. TIMESTAMP_NTZ 0 is always
"1970-01-01 00:00:00". - A NULL element, field or map value becomes a variant null (
[null,"a","b","c"]). A top-level NULL is SQL NULL.
An array of variants that holds NULLs works the same way. - Struct field order and map entry order are discarded, and keys are sorted with Java
String.compareTo(UTF-16
order). Struct field names become object keys exactly, case-sensitive. - Duplicate struct field names, such as
named_struct('a', 1, 'a', 2): the builder hasallowDuplicateKeys = false, so by code reading this raisesVARIANT_DUPLICATE_KEYdirectly, without the malformed-record wrapper
thatparse_jsonadds. Maps cannot hold duplicate keys under the defaultspark.sql.mapKeyDedupPolicy. - Map keys can be any string collation. Non-string keys fail at analysis, at any depth.
- TIME (4.1+):
checkDataTypeaccepts everyDatetimeType, which includesTimeType, butbuildVarianthas no
TIME case. Check what Spark does withto_variant_object(array(time'12:00:00'))before deciding. - Intervals are rejected at analysis (SPARK-49985).
- Exceeding the 128 MiB value or metadata limit raises
VARIANT_SIZE_LIMITfrom the builder. - Test literal and column inputs, empty arrays, maps and structs (
[],{}), and deep nesting.
Differences across Spark versions
- 3.x has no VARIANT.
- No differences in
ToVariantObjectorcastToVariantbetween 4.0.4, 4.1.3 and 4.2.0. TIME types exist only
from 4.1.
Implementation notes
- Spark:
ToVariantObjectin
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/variant/variantExpressions.scala, and
castToVariant/buildVariantinVariantExpressionEvalUtils.scalain the same package.
VariantGet.checkDataTypeis in the same file. The encoder iscommon/variant/.../VariantBuilder.java. - arrow-rs
parquet_variant_compute::cast_to_variantcovers most Arrow types, but its encoding differs from
Spark's:Int32becomes INT4 andDecimal128always becomes DECIMAL16, with no narrowing.ObjectBuildersorts keys by UTF-8 bytes.- The metadata builder may set
sorted_strings. - It accepts types that Spark rejects (non-string map keys, unsigned integers, and others).
Use it only as a building block. The type check and narrowing rules have to match Spark.
- Spark's
DECIMALis alwaysDecimal128in datafusion-spark, so the narrowing has to look at the value. - DataFusion core has no VARIANT support. This depends on the VARIANT Arrow representation proposed in the
parse_jsonissue, and the same encoder can backCAST(x AS VARIANT). - [EPIC] Native Variant Support datafusion-comet#5438 tracks native Variant support in Comet.
- Put it in
crates/spark/src/function/variant/. Addcrates/sqllogictest/test_files/variant/to_variant_object.slt
and document the function with#[user_doc].
This issue was generated by an LLM (Claude) from the Apache Spark source code and tests. Examples come from Spark's ExpressionDescription examples, test suites and .sql.out golden files, but please verify them against Spark before relying on them.
Activity