Skip to main content

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

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, providing safe navigation and conversion without requiring upfront schema knowledge.