Computed columns

On this page
Related contents

A computed column exposes an expression as a named, typed, read-only table column. Use it to extract a stable field from a changing event payload or reuse a calculation across queries. Its current definition applies to both historical and incoming rows.

Define a computed column

In a Catalog table definition, set the column's expression alongside its name and data_type. For example, this table exposes an integer status from a semi-structured payload:

{
  "name": "computed_requests",
  "columns": [
    { "name": "request_id", "data_type": "string" },
    { "name": "payload", "data_type": "object" },
    { "name": "status", "data_type": "int", "expression": "payload['status']" }
  ]
}

Create it through the Catalog API or a client's Catalog interface. To add status to an existing table, read its complete definition, add the column, then preview and apply the updated definition. Catalog writes replace the definition, so preserve its other columns and settings.

Deployments with SQL DDL enabled also support AS (<expr>) in CREATE TABLE and ALTER TABLE ... ADD COLUMN:

CREATE TABLE computed_requests (
    request_id string,
    payload object,
    status int AS (payload['status'])
)

For an existing table that already has request_id and payload, add the column with:

ALTER TABLE computed_requests
ADD COLUMN status int AS (payload['status'])

These are alternative ways to define the same column. SQL DDL requires features.legacy_statements to be enabled on the server; it is disabled by default. Use the Catalog API when SQL DDL is unavailable.

Types and NULL handling

ScopeDB evaluates the expression and converts its result to the declared column type. The behavior is equivalent to try(<expr>::<col_type>): an evaluation or conversion error produces NULL for that row. Invalid definitions, such as an unknown column or function, are rejected when the definition is saved.

Append these rows to computed_requests using NDJSON ingestion:

{"request_id":"req-1","payload":{"status":"0200"}}
{"request_id":"req-2","payload":{"status":"bad"}}
{"request_id":"req-3","payload":{"status":null}}
{"request_id":"req-4","payload":{}}

Query the computed column like an ordinary column:

FROM computed_requests
ORDER BY request_id
SELECT request_id, status
+------------+--------+
| request_id | status |
+------------+--------+
| req-1      | 200    |
| req-2      | NULL   |
| req-3      | NULL   |
| req-4      | NULL   |
+------------+--------+

The string "0200" converts to an integer. Invalid text, an explicit JSON null, and a missing object field produce NULL. You can use status in filters, joins, grouping, sorting, and views, and it appears in SELECT *.

Expression rules

Computed expressions must use deterministic scalar operations over ordinary columns in the same table. An ordinary column may appear earlier or later in the table definition. Constants are allowed.

  • Reference ordinary columns by name; positional references such as $0 are rejected.
  • A computed column cannot reference itself or another computed column. Write the calculation in terms of ordinary columns instead.
  • Nondeterministic functions such as now(), aggregate functions such as sum(), and window functions such as row_number() are rejected, including inside try() or an unused conditional branch.

The same rules apply when adding a column or changing its expression.

Write ordinary columns

Computed columns are read-only. Omit them from NDJSON objects and Arrow schemas, even when their value would be NULL. Supplying a computed column is an input-schema error.

Where SQL mutations are enabled, INSERT without a column list maps values to ordinary columns in table order, skipping computed columns. An explicit column list may name only ordinary columns. UPDATE may read computed columns in expressions and predicates, but cannot assign to them; changing an ordinary column changes the derived result.

Change or remove a definition

To change a computed column through Catalog, edit its expression in the complete table definition, then preview and apply the change. For example, replace payload['status'] with payload['http']['status'] when the payload format changes. Existing and new rows use the replacement expression; historical rows with only the old field then return NULL.

SQL DDL provides the equivalent operation:

ALTER TABLE computed_requests
ALTER COLUMN status SET EXPRESSION AS (payload['http']['status'])

The column keeps its name and declared type. An existing column's type and ordinary/computed kind cannot be changed in place. SET EXPRESSION requires an existing computed column.

To remove a computed column, remove it from the Catalog definition or use:

ALTER TABLE computed_requests DROP COLUMN status

You cannot remove an ordinary column while a computed expression still references it. Remove or change the dependent expression first. Table keys and dependent views must also remain valid after a definition change.

Indexes and table keys

ScopeDB automatically adds a materialized index for a computed column, alongside its default range index. Computed int and string columns also receive inverted indexes; computed any and object columns receive path indexes. You do not need a separate CREATE MATERIALIZED INDEX statement to cache the derived value.

New writes and compaction build the indexes. Historical files without a matching index still return the correct value by evaluating the current expression. Changing an expression updates its index requirements with the definition; it does not require re-ingesting historical rows.

Computed columns can be referenced in partition, cluster, and deduplication keys and explicit index definitions. The expanded expression must satisfy the rules of that key or index; for example, a constant computed column cannot serve as a partition key.

Inspect a definition

Read the table through the Catalog API to see each computed column's expression.

Where the server enables both legacy statements and system views, DESCRIBE TABLE computed_requests includes the expression, and SHOW CREATE TABLE computed_requests includes the AS (...) definitions. With system views enabled, you can also query:

FROM system.columns
WHERE database_name = 'scopedb'
  AND schema_name = 'public'
  AND table_name = 'computed_requests'
SELECT column_name, data_type, expression

The expression value is NULL for ordinary columns.