Variant

You use Hamelin's variant type to work with JSON and other semi-structured data. Hamelin adopts the Variant trend for representing the JSON object model, making JSON feel native and easy to work with.

Parsing JSON into variant

You parse a JSON string into a variant using the parse_json() function:

FROM api_logs
| SET event_data = parse_json(json_payload)
| SELECT event_data

Inferring a cast schema

These functions are DataFusion only. The DataFusion backend can inspect parsed Variant values and return a type string that a Variant cast accepts. Use the scalar function for one value. Use the aggregate function to merge the schemas from a group of records. The Trino backend returns an explicit unsupported-backend error for both functions.

Signatures:

• schema_of_variant(value: variant) -> string
• schema_of_variant_agg(value: variant) -> string

Inference and merge rules

DataFusion applies these rules:

• Equal types keep that type.
• Objects merge fields by their exact names.
• DataFusion recursively merges fields that occur in more than one object.
• A field that occurs in only one object stays in the result. A missing field does not cause a type conflict.
• Arrays recursively merge the types of all their elements.
• A null value does not conflict with a known type.
• An int and a double merge to double.
• Decimal-only values use a common decimal precision and scale. If the required precision exceeds 38, the result becomes double. A decimal mixed with an int or double also becomes double.
• Incompatible scalar types merge to variant.
• An object mixed with an array or scalar merges to variant.
• An array mixed with an object or scalar merges to variant.
• An empty array or an array that contains only null values becomes array(variant).
• A retained empty object becomes map(string, variant).
• An object with an empty field name becomes map(string, variant).
• Native non-JSON Variant scalars that do not have a current matching cast, such as binary, date, time, timestamp, and UUID values, become variant.

For schema_of_variant(), a SQL null or Variant null returns variant. For schema_of_variant_agg(), null inputs do not replace a known type. If all aggregate inputs are SQL null or Variant null, the function returns variant.

DataFusion preserves exact field names and backtick-escapes unusual names. It sorts struct fields by their exact names. Aggregate output does not depend on row order, partition order, or partial-aggregate merge order.

Infer and use a schema

First, run an aggregate query to inspect the JSON text in all connector events. The aggregate returns one deterministic Hamelin type string for the complete group:

FROM connectors.example
| AGG schema =
    schema_of_variant_agg(parse_json(event.original))

Suppose the returned string is {event_id: string, metrics: {count: int, rate: double}}. Copy that text into a cast in a second query. The cast creates a typed struct that you can access with normal field syntax:

FROM connectors.example
| SET parsed =
    parse_json(event.original) AS {
        event_id: string,
        metrics: {count: int, rate: double}
    }
| SELECT parsed.event_id, parsed.metrics.count

Inference limits

MAX_VARIANT_SCHEMA_DEPTH is 64, and the root value has depth 1. DataFusion returns an error before it visits a value at depth 65.

MAX_VARIANT_SCHEMA_FIELDS is 10000. The scalar function limits the total object fields visited in one document. The aggregate retains only its canonical schema tree. A partial aggregate state can contain more than 10000 fields because a later conflict can reduce it. DataFusion checks the exact final schema against the 10000-field limit. This keeps successful results and limit errors independent of row, partition, and merge order. A terminal variant conflict discards its child schema instead of keeping conflict history. DataFusion uses the accumulator's reported size to account for partial-state memory.

DataFusion returns an error when inference exceeds either limit. It does not omit fields or return a partial schema.

Backend support

DataFusion supports both schema inference functions. The aggregate supports partial and final distributed aggregation. The Trino backend returns an explicit unsupported-backend error for both functions.

Navigating variant data

You navigate variant substructure safely and ergonomically using dots and square brackets, just like with structs and arrays:

FROM car_sales
| SET json = parse_json(src)
| SELECT 
    sale_date = json.date,
    salesperson_name = json.salesperson.name,
    customer_name = json.customer[0].name

Accessing nested fields

You can access deeply nested fields using the same dot and bracket notation:

FROM events
| SET data = parse_json(event_json)
| SELECT 
    user_email = data.user.profile.email,
    first_item_price = data.transaction.items[0].price

Safe type conversion

Variants cast safely to other Hamelin types. Individual conversion failures become NULL instead of crashing your query:

FROM logs
| SET parsed = parse_json(log_data)
| SELECT 
    log_time = parsed.timestamp AS timestamp,
    user_id = parsed.user_id AS string,
    event_count = parsed.count AS integer

Casting to structured types

You can cast variants to maps, arrays, and structs. These casts are safe and null on failure:

FROM json_data
| SET parsed = parse_json(raw_json)
| SELECT 
    user_info = parsed AS {name: string, age: integer},
    tag_list = parsed.tags AS [string]

Creating variant objects

You create variant objects by casting structs to variant:

SET user_struct = {name: 'Alice', age: 30}
| SET user_variant = user_struct AS variant
| SELECT user_variant

Creating variant arrays

You create variant arrays by casting arrays to variant:

SET numbers = [1, 2, 3, 4, 5]
| SET variant_list = numbers AS variant
| SELECT variant_list

Mixed type handling

Variant handles mixed types within the same structure:

FROM api_responses
| SET response = parse_json(response_body)
| SELECT 
    record_id = response.data.id AS string,
    is_active = response.data.active AS boolean,
    user_score = response.data.score AS double

Database system compatibility

Hamelin adapts to your database system's JSON capabilities:

• Full VARIANT support (Snowflake, Databricks): Hamelin uses native variant storage and operations
• JSON support (BigQuery, Postgres): Hamelin treats JSON as the variant format
• ANSI JSON only: Hamelin provides parsing and access functions but no efficient storage

Working with arrays

You access array elements using zero-based indexing:

FROM events
| SET data = parse_json(event_data)
| SELECT 
    first_item = data.items[0].name,
    last_item = data.items[-1].name

Handling missing fields

Variant navigation is safe - accessing missing fields returns NULL:

FROM logs
| SET parsed = parse_json(log_entry)
| SELECT 
    always_present = parsed.required_field,
    might_be_null = parsed.optional_field

---

Variant types make JSON feel native in Hamelin. They provide safe navigation and conversion without requiring a schema in advance.