Skip to content

Implement Spark-compatible to_variant_object #280

Open

Description

@andygrove

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_SUGGESTION

These 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, a DECIMAL(38,2) value of 1.00 is 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.
  • How these render through to_json (from the suite): booleans and numbers print as Java toString (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 on spark.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 has allowDuplicateKeys = false, so by code reading this raises VARIANT_DUPLICATE_KEY directly, without the malformed-record wrapper
    that parse_json adds. Maps cannot hold duplicate keys under the default spark.sql.mapKeyDedupPolicy.
  • Map keys can be any string collation. Non-string keys fail at analysis, at any depth.
  • TIME (4.1+): checkDataType accepts every DatetimeType, which includes TimeType, but buildVariant has no
    TIME case. Check what Spark does with to_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_LIMIT from 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 ToVariantObject or castToVariant between 4.0.4, 4.1.3 and 4.2.0. TIME types exist only
    from 4.1.

Implementation notes

  • Spark: ToVariantObject in
    sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/variant/variantExpressions.scala, and
    castToVariant / buildVariant in VariantExpressionEvalUtils.scala in the same package.
    VariantGet.checkDataType is in the same file. The encoder is common/variant/.../VariantBuilder.java.
  • arrow-rs parquet_variant_compute::cast_to_variant covers most Arrow types, but its encoding differs from
    Spark's:
    • Int32 becomes INT4 and Decimal128 always becomes DECIMAL16, with no narrowing.
    • ObjectBuilder sorts 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 DECIMAL is always Decimal128 in 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_json issue, and the same encoder can back CAST(x AS VARIANT).
  • [EPIC] Native Variant Support datafusion-comet#5438 tracks native Variant support in Comet.
  • Put it in crates/spark/src/function/variant/. Add crates/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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions