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.

JSON columns are stored as VARCHAR (Iceberg has no native JSON type yet). Both layers parse on every call, so for hot paths consider extracting once into a typed column.

Trino-named layer

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 V3 has no native JSON primitive yet. SQE accepts JSON in CREATE TABLE and stores it as VARCHAR underneath. Predicates and projections work as text. When Iceberg adds a JSON primitive, the storage layout will change but the SQL surface stays the same.

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.