JSON

Two layered JSON surfaces. Both are registered in every session.

1. Trino-named layer in sqe-trino-functions: json_extract, json_extract_scalar, json_array_length, json_parse, json_format, json_object, is_json_scalar, json_array_contains, json_size, json_array_get, to_json. Maps directly to dbt-trino model expectations.
2. DataFusion JSON layer via datafusion-functions-json (registered in session_context.rs:361 and embedded.rs:172): json_get, json_get_str, json_get_int, json_get_float, json_get_bool, json_get_json, json_get_array, json_contains, json_as_text, json_length. Type-specific extractors that avoid an outer CAST.

A JSON column is stored as Iceberg VARIANT on a V3 table (the default for new tables) and as a string on a V2 table; either way it reads back as JSON text (see DDL, VARIANT). Both layers parse on every call, so for hot paths consider extracting once into a typed column.

Trino-named layer

CAST(x AS JSON)

CAST(x AS JSON) is rewritten to to_json(x) and returns the text Trino 476 returns for the same cast:

A cast of a value that is already JSON (json_parse, json_extract, json_array_get, a JSON '...' literal, a nested CAST(.. AS JSON)) returns the value unchanged. JSON '<text>' is read as json_parse('<text>'), so the literal comes back canonical too.

Known differences from Trino:

• Anonymous rows: CAST(ROW(1, 'x') AS JSON) gives {"c0":1,"c1":"x"}; Trino gives {"":1,"":"x"}. Name the fields with CAST(ROW(..) AS ROW(a .., b ..)) to get the same output.
• JSON columns and JSON inside an array or map: on a V2 table SQE stores JSON as VARCHAR with no type marker, so only a cast of a JSON-returning function is recognised as already JSON. CAST(payload AS JSON) on a stored JSON column encodes the text as a JSON string, "{\"a\":1}", where Trino returns it unchanged, {"a":1}; likewise CAST(ARRAY[json_parse('{"a":1}')] AS JSON) gives ["{\"a\":1}"], where Trino embeds it, [{"a":1}]. Use the column directly, or json_parse(payload), instead of casting it.
• Timestamp precision: fractional seconds print without trailing zeros; Trino pads to the declared precision. A zoned timestamp ends in an offset (+00:00), not a zone name (UTC).
• Types Trino refuses: varbinary, time and interval values are written as their quoted text; Trino raises “Cannot cast … to json”. Likewise CAST(json AS VARCHAR) returns the JSON text, where Trino raises an error.

DataFusion JSON layer

These return typed scalars directly, which means no outer CAST and DataFusion can push predicates through.

Path syntax

The Trino layer uses JSONPath ($.foo.bar[0]).

The DataFusion JSON layer uses single-step keys: a string for object access or an integer for array access.

-- Trino-style: full JSONPath
SELECT json_extract_scalar(payload, '$.user.id')
FROM events;

-- DataFusion: chained single-step
SELECT json_get_str(json_get_json(payload, 'user'), 'id')
FROM events;

Both compose. Both work on the same VARCHAR column. Choose by ergonomics:

• Trino-style is one call per path; cleaner SQL.
• DataFusion JSON layer returns native types; no outer CAST.

For a deeply nested path used in a hot WHERE clause, the DataFusion layer wins on speed (no JSON re-encoding between steps).

Comparison

Examples

Extract a typed field for filtering

-- Slowpath: parse JSON, cast to int, compare
SELECT * FROM events
WHERE CAST(json_extract_scalar(payload, '$.user_id') AS BIGINT) = 12345;

-- Faster: typed extractor, no CAST
SELECT * FROM events
WHERE json_get_int(json_get_json(payload, 'user'), 'id') = 12345;

Project several fields at once

SELECT
    json_extract_scalar(payload, '$.user.id')        AS user_id,
    json_extract_scalar(payload, '$.user.email')     AS email,
    json_extract_scalar(payload, '$.session.locale') AS locale,
    CAST(json_extract_scalar(payload, '$.amount') AS DECIMAL(18, 2)) AS amount
FROM events;

Path-existence filter

SELECT * FROM events
WHERE json_contains(payload, '$.metadata.experimental_flag') = true;

Build a structured field for downstream consumers

SELECT json_object(
    'id', id,
    'host', url_extract_host(url),
    'path', url_extract_path(url),
    'received_at', cast(occurred_at AS VARCHAR)
) AS event_doc
FROM events;

Performance tips

• Decode once at the boundary: when a JSON column is queried in many downstream views, consider materialising the relevant subfields into typed columns at ingest time. Repeated parsing is the biggest JSON cost.
• Path index pruning: the DataFusion JSON layer can push json_get_str(payload, 'user_id') = 'X' through to a manifest min/max statistic when the underlying JSON has been pre-extracted. The Trino layer is opaque.
• Avoid round-trip: json_format(json_parse(j)) is a no-op semantically but burns CPU. Skip the round-trip unless you need re-canonicalisation.

Iceberg JSON column type

Iceberg has no JSON primitive. SQE accepts JSON in CREATE TABLE and stores it as VARIANT on a V3 table (the default) and as a string on a V2 table. Either way it reads back as JSON text, and the json* functions accept both. Existing string columns are not converted. See DDL, VARIANT.

What is not registered

• json_array(...) (Trino name for build-an-array). Use cast(make_array(...) AS VARCHAR) followed by json_format, or to_json(make_array(...)).
• json_size at the root: covered. With a path: covered.
• JSONPath wildcards ($..foo, $[*]): not supported by DataFusion’s path parser. Use unnest(json_get_array(...)) for array-level wildcarding.