Computed Indexes

Polecat supports computed indexes on document properties. These indexes use SQL Server's persisted computed columns backed by JSON_VALUE expressions, giving you the performance of a traditional column index without duplicating data outside of the JSON document.

How It Works

When you define a computed index, Polecat:

1. Adds a persisted computed column to the document table using JSON_VALUE(data, '$.path')
2. Creates a standard nonclustered index on that computed column

For example, indexing UserName on a User document produces:

ALTER TABLE [myschema].[pc_doc_user]
    ADD [cc_username] AS CAST(JSON_VALUE(data, '$.userName') AS varchar(250)) PERSISTED;

CREATE NONCLUSTERED INDEX [ix_pc_doc_user_username]
    ON [myschema].[pc_doc_user] ([cc_username]);

Simple Indexes

Use the fluent API via StoreOptions.Schema.For<T>().Index():

var store = DocumentStore.For(opts =>
{
    opts.ConnectionString = "...";
    opts.Schema.For<User>().Index(x => x.UserName);
});

SQL Server will use this index when querying:

var user = await session.Query<User>()
    .FirstOrDefaultAsync(x => x.UserName == "somebody");

Composite (Multi-Column) Indexes

Create a single index across multiple properties using an anonymous type:

opts.Schema.For<User>().Index(x => new { x.FirstName, x.LastName });

This produces one nonclustered index with both columns:

CREATE NONCLUSTERED INDEX [ix_pc_doc_user_firstname_lastname]
    ON [myschema].[pc_doc_user] ([cc_firstname], [cc_lastname]);

Unique Indexes

opts.Schema.For<User>().UniqueIndex(x => x.Email);

Attempting to store two documents with the same email will throw a SQL Server unique constraint violation.

Covering Indexes (INCLUDE Columns)

A query that filters on one property but also selects others can still pay for a lookup back into the table to fetch the extra columns. You can avoid that by carrying those extra members in the index as non-key INCLUDE columns, so SQL Server satisfies the whole query from the index alone. Pass the include: argument — a single member or an anonymous type, just like the key expression:

opts.Schema.For<User>().Index(x => x.UserName, include: x => new { x.FirstName, x.LastName });

Each include member gets its own persisted computed column, and the index gains an INCLUDE clause:

ALTER TABLE [myschema].[pc_doc_user]
    ADD [cc_firstname] AS CAST(JSON_VALUE(data, '$.firstName') AS varchar(250)) PERSISTED;
ALTER TABLE [myschema].[pc_doc_user]
    ADD [cc_lastname] AS CAST(JSON_VALUE(data, '$.lastName') AS varchar(250)) PERSISTED;

CREATE NONCLUSTERED INDEX [ix_pc_doc_user_username]
    ON [myschema].[pc_doc_user] ([cc_username]) INCLUDE ([cc_firstname], [cc_lastname]);

This maps to the IncludeColumns property on DocumentIndex (named after Marten/Weasel's IndexDefinition.IncludeColumns), which you can also set directly in the configure action if you prefer to work in raw JSON paths:

opts.Schema.For<User>().Index(x => x.UserName, idx => idx.IncludeColumns = ["$.firstName", "$.lastName"]);

UniqueIndex(...) accepts the same include: argument. Include columns are always stored with default casing (they are payload, not keys), and covering indexes work on both native json and nvarchar(max) storage.

Customizing an Index

The Index() and UniqueIndex() methods accept an optional Action<DocumentIndex> to customize the index:

opts.Schema.For<User>().Index(x => x.UserName, idx =>
{
    // Force the indexed value to lowercase for case-insensitive lookups
    idx.Casing = IndexCasing.Lower;

    // Override the index name
    idx.IndexName = "ix_user_name_ci";

    // Use a different SQL type (default: varchar(250))
    idx.SqlType = "varchar(500)";

    // Change sort order (default: Ascending)
    idx.SortOrder = SortOrder.Descending;

    // Scope uniqueness per tenant (for conjoined tenancy)
    idx.TenancyScope = TenancyScope.PerTenant;

    // Add a WHERE clause for a filtered (partial) index
    idx.Predicate = "tenant_id <> 'EXCLUDED'";
});

Case Transformations

For case-insensitive lookups, you can apply UPPER() or LOWER() transformations to string-typed index columns. This wraps the JSON_VALUE expression so the persisted computed column stores the normalized value:

// Lowercase index — stores "john.doe@example.com" even if original is "John.Doe@Example.COM"
opts.Schema.For<User>().Index(x => x.Email, idx =>
{
    idx.Casing = IndexCasing.Lower;
});

// Uppercase index
opts.Schema.For<User>().Index(x => x.UserName, idx =>
{
    idx.Casing = IndexCasing.Upper;
});

The generated SQL for a lowercase index:

ALTER TABLE [myschema].[pc_doc_user]
    ADD [cc_email_lower] AS LOWER(CAST(JSON_VALUE(data, '$.email') AS varchar(250))) PERSISTED;

CREATE NONCLUSTERED INDEX [ix_pc_doc_user_email_lower]
    ON [myschema].[pc_doc_user] ([cc_email_lower]);

TIP

Case transformations only apply to string-typed columns. Non-string columns (int, Guid, etc.) ignore the Casing setting.

Case-Insensitive Unique Indexes

Combine casing with unique indexes to enforce uniqueness regardless of case:

opts.Schema.For<User>().UniqueIndex(x => x.Email, idx =>
{
    idx.Casing = IndexCasing.Lower;
});

This rejects both "test@example.com" and "Test@Example.COM" as duplicates.

Attribute-Based Indexes

Instead of (or in addition to) the fluent API, you can declare indexes directly on your document properties using attributes.

[Index] Attribute

Marks a property for a computed index:

using Polecat.Attributes;

public class User
{
    public Guid Id { get; set; }

    [Index]
    public string UserName { get; set; } = "";

    [Index(Casing = IndexCasing.Lower)]
    public string Email { get; set; } = "";

    [Index(SqlType = "int")]
    public int Age { get; set; }
}

[UniqueIndex] Attribute

Marks a property for a unique computed index:

public class User
{
    public Guid Id { get; set; }

    [UniqueIndex]
    public string Email { get; set; } = "";

    public string Name { get; set; } = "";
}

Composite Unique Indexes with Attributes

Use the IndexName property to group multiple properties into a single composite unique index:

public class User
{
    public Guid Id { get; set; }

    [UniqueIndex(IndexName = "ux_fullname")]
    public string FirstName { get; set; } = "";

    [UniqueIndex(IndexName = "ux_fullname")]
    public string LastName { get; set; } = "";
}

This creates one unique index across both FirstName and LastName.

Attribute Options

Both [Index] and [UniqueIndex] support these options:

Option	Type	Default	Description
IndexName	string?	Auto-generated	Explicit index name
Casing	IndexCasing	Default	Case transformation (Upper, Lower, Default)
SqlType	string?	varchar(250)	SQL type for the computed column
SortOrder	SortOrder	Ascending	Sort order (Index only)
TenancyScope	TenancyScope	Global	Per-tenant scoping (UniqueIndex only)

TIP

Attribute-based indexes are discovered automatically when the document type is first used. They can be combined with fluent API indexes on the same document type.

Tenancy-Scoped Indexes

For multi-tenant applications using conjoined tenancy, you can scope unique indexes per tenant:

opts.Schema.For<User>().UniqueIndex(x => x.Email, idx =>
{
    idx.TenancyScope = TenancyScope.PerTenant;
});

This includes tenant_id in the index columns, allowing the same email across different tenants while enforcing uniqueness within each tenant.

Serialized Names and [JsonPropertyName]

The computed column reads the JSON path the serializer writes, not the CLR property name. A member carrying [JsonPropertyName] is indexed under its alias:

public class Metric
{
    public Guid Id { get; set; }

    [JsonPropertyName("bucket_label")]
    public string Label { get; set; } = string.Empty;
}

opts.Schema.For<Metric>().Index(x => x.Label);

produces a column over $.bucket_label, matching what queries on x.Label translate to, so the index is usable.

Upgrading from before 5.20

Prior to 5.20 the index path was built from the CLR member name, so an aliased member got a column over a path the serializer never writes — permanently NULL, and an index that could never match. Queries kept returning correct results by scanning the data column, so there was no error to notice.

On upgrade the correct column and index are created additively; the stale ones are left in place, since Polecat's migrations never drop. For a member previously indexed under an alias you will find an orphaned cc_<clrname> column and its ix_<table>_<clrname> index, both safe to drop by hand once you no longer need to roll back.

Naming Policies

The index path follows the store's configured naming policy, so a non-default casing indexes the path the serializer actually writes:

opts.ConfigureSerialization(casing: Casing.SnakeCase);
opts.Schema.For<Metric>().Index(x => x.ServiceName);

produces a cc_service_name column over $.service_name, matching both the document and the queries translated against it. Every segment of a nested path is converted, and native JsonIndex(...) paths follow the same rule.

An explicit [JsonPropertyName] still wins verbatim and is not put through the policy on top, matching how System.Text.Json itself behaves.

Upgrading from before 5.20

Prior to 5.20 index paths were always camelCased regardless of the configured policy, so on a Casing.SnakeCase store every computed column read a path the document did not contain — NULL for every row, and an index that could never match. As with the alias case above, queries returned correct results by scanning data, so there was nothing to notice.

On upgrade the correctly-named column and index are created additively and the stale ones are left in place. Expect an orphaned cc_<camelCase> column and its index per affected member, both safe to drop by hand once you no longer need to roll back.