Materialized Columns

Extract frequently queried JSON metadata into typed columns, add column indexes, and monitor rebuilds for fast filtering at scale.

4 min read

Document metadata is stored as JSON in meta. That flexibility is useful for source-specific attributes, but repeatedly extracting and converting values from JSON can become slow on a large index.

Materialized columns store selected metadata values as typed columns beside each searchable document part. Column indexes add another optimization for filters and sorting that use those columns frequently.

Materialized columns do not change the original document or its metadata. BotDojo adds the col_ prefix to distinguish configured columns from built-in index fields.

When to use materialized columns

Use a materialized column when:

semantic searches repeatedly filter on the same metadata field;
a value should behave as a date, timestamp, number, or Boolean instead of JSON text;
metadata queries need to sort, group, or compare a field consistently;
a projection index needs a stable typed schema from its parent rows.

Keep exploratory or infrequently queried fields in meta. Materialize the small set of attributes used by production filters, ordering, reporting, or projection logic.

Configure materialized columns

For a standard index, create and populate the index first. Open the index and select Edit → Materialized Columns → Configure.

During projection-index creation, configure materialized columns after selecting the parent index.

The Materialized Columns dialog with column and column-index controls

The Columns section offers two starting points:

Add Column samples scalar metadata fields from indexed rows. Select fields to create their initial column definitions.
Add from Expression creates a definition manually when the value is nested, transformed, combined, or not present in the sample.

Each definition has:

Setting	What it does
Column	Snake_case name entered without the `col_` prefix. A column named `department` is stored as `col_department`.
Type	Converts the result to Text, Date, Timestamp, Number, or Boolean.
Expression	SQL `SELECT` expression that reads or transforms the row, such as `meta->>'department'`.

Examples:

Column	Type	Expression	Stored name
`department`	Text	`lower(trim(meta->>'department'))`	`col_department`
`priority`	Number	`meta->>'priority'`	`col_priority`
`published_at`	Timestamp	`meta->>'published_at'`	`col_published_at`
`customer_visible`	Boolean	`meta->>'customer_visible'`	`col_customer_visible`

Empty strings and values that cannot be converted to the selected type are stored as null. Query a sample after the rebuild to confirm that the source values convert as expected.

Select Validate Updates before closing the dialog. Validation checks names, types, expressions, and column-index definitions without applying them.

Add column indexes

A Column Index creates a B-tree index over one or more materialized columns. Add one when a large index repeatedly filters or sorts on the same column or column combination.

Give the index a descriptive snake_case name.
Select columns in the same order used by the common filter.
Put an equality-filtered column before a range or sort column in a multi-column index.
Do not create an index for every materialized column. Each index adds storage and update work.

For example, searches that commonly filter by department and then a publication range can use an index over department, published_at in that order.

Apply and monitor changes

Changing a column name, type, or expression changes the stored schema and requires a schema rebuild. Changing only Column Index definitions uses a smaller index sync.

BotDojo explains the required operation before applying the update and queues the corresponding job. Open Index Jobs on the index page to monitor progress or inspect an error. Wait for the update to complete before relying on the new columns.

Index Jobs shows materialized-column rebuild and index-sync progress

Query materialized columns

Select Query on the index page.

The Query panel accepts materialized columns in semantic filters and metadata SQL

In Semantic Search, use a materialized column in the optional WHERE expression:

col_department = 'support'
AND col_published_at >= '2026-01-01'

In Metadata Query, select materialized columns from index_table:

select id, document_id, col_department, col_published_at, meta
from index_table
where col_department = 'support'
order by col_published_at desc
limit 10

The Query editor suggests built-in fields, common metadata expressions, and configured col_ columns. Use Metadata Query to confirm actual values and types before depending on a column in semantic filters, flows, or agents.

Projection indexes also support an optional parent WHERE expression. Test Filter validates it and reports how many parent parts match before the projection is created or updated.

Design guidelines

Use consistent field names and value types across every document in an index.
Start with the filter patterns the agent or flow actually uses.
Materialize only fields used by filters, ordering, reporting, or projection logic.
Add column indexes after identifying repeated access patterns on a large index.
Recheck query results and conversion failures after every schema rebuild.