Dynamic fields

A JSON leaf path in a raw table normally has the Dynamic type. Each row retains a concrete value type at that path. One row can contain a number, another a string, and another no value.

This flexibility removes the need to choose one column type before ingestion. It also makes the intended meaning of a field part of query design. The number 42 and the string "42" need not mean the same thing.

Stored types and missing values

dynamicType identifies the concrete type for a value.

SELECT value, dynamicType(value) AS value_type FROM events ORDER BY value_type

A known path absent from a row and an explicit JSON null both read as NULL. An entirely unknown path produces UNKNOWN_IDENTIFIER. These are different situations: missing data in a known field is not a missing field definition.

A Dynamic wrapper is not an ordinary nullable column. For presence checks, countIf(value IS NOT NULL) expresses the intended count. count(value) can count the wrapper even when its stored value is null. count() counts rows.

Conversion and exact extraction

A cast converts compatible values to a chosen type. For example, a numeric string can participate in a numeric calculation after conversion.

SELECT value::Int64 AS value_number FROM events

A strict cast can fail for incompatible values. It can also convert a missing value to a target default, such as zero or an empty string. Those defaults can create false matches or combine unrelated rows in a group.

Exact extraction selects one stored type instead of converting other types. A stored numeric string does not become an integer through this expression:

SELECT __raw_data.value.:Int64 AS exact_integer FROM events

A nonmatching type returns NULL when the target supports nulls. Some targets that cannot be nullable return their default instead. Exact extraction and conversion therefore answer different questions.

accurateCastOrNull expresses tolerant conversion. It returns NULL for incompatible or out-of-range values, so the query can handle them explicitly.

SELECT accurateCastOrNull(value, 'Int64') AS value_number FROM events

Comparisons, grouping, and ordering

Operations on dynamic fields can dispatch by concrete type. A comparison that works for numeric variants need not interpret string variants numerically. A successful comparison is not proof that every type participated as intended.

Grouping and distinct operations do not supply a universal conversion rule. Compatible numeric variants can normalize, while a numeric value and its string representation remain different values. A shared business identity requires an explicit common type.

Ordering also depends on the stored types and engine behavior. A mixed field does not automatically define a useful business order. Numeric scores, dates, and identifiers need the appropriate conversion before sorting or aggregation. Null placement is a separate choice.

Joins and arrays

Dynamic join keys are disabled by default. Converting both sides to the same concrete type makes their intended relationship explicit. Missing or invalid keys need separate handling, since converting both to an empty string can create false matches.

Array values retain element types. A field can contain arrays in some rows and scalars or nulls in others.

SELECT dynamicType(tags) AS tags_type, tags FROM events

Array operations can omit incompatible variants. An empty array also produces no rows in an ordinary ARRAY JOIN. These outcomes affect row counts, not just formatting.

Runtime type-compatibility warnings

Query hints are runtime type-compatibility warnings. They report cases where RawTree skips incompatible Dynamic values or substitutes NULL or default results while continuing execution.

Scalar functions emit hints when they handle an incompatible concrete type. For example, length(value) can return NULL for integer values while processing strings. With dynamic_throw_on_type_mismatch = 0, the default, the function uses NULL or its defined default for supported mismatch cases. Set dynamic_throw_on_type_mismatch = 1 to raise an exception for those scalar mismatches. This setting does not control aggregate skipping.

Numeric/string comparisons can also produce hints. With the default setting, comparing a string value to a number returns false for equality and ordering comparisons, but true for !=. A message about skipped rows does not always mean the result excludes those rows. A SELECT expression can retain the row with a default result, while a WHERE condition can exclude it.

Aggregates can skip types that their signatures do not support. For values 1, 2, 3, "hello", and "world", sum(value) returns 6 and avg(value) returns 2. Each emits a hint about incompatible String values. count(value) accepts those types and returns 5 without a hint. Missing values and ordinary NULL handling do not themselves produce these warnings.

Some aggregate paths with multiple arguments can skip incompatible types without a hint. An absent hint does not prove that every value contributed to the result. Inspect the stored types and choose whether to convert values, select one type, or count excluded values explicitly.

The JSON result exposes warnings in its optional hints field. Query logging also records them in system.query_log.hints. See Query response format for the field contract and Query fields with mixed types for examples.

Type conversion and performance

Conversion can also affect index use. The correct type semantics come first. The query plan and execution statistics then show whether a predicate can skip data. No general rule says that every cast disables optimization.

Dynamic fields

On this page