or run

npx @tessl/cli init

Filter System

Nested AND/OR filters with 19 operators for dimensions, metrics, and table calculations.

Filter Operators

enum FilterOperator {
  // Null checks (all types)
  NULL = 'isNull', NOT_NULL = 'notNull',

  // Equality (all types)
  EQUALS = 'equals', NOT_EQUALS = 'notEquals',

  // String operators
  STARTS_WITH = 'startsWith', ENDS_WITH = 'endsWith',
  INCLUDE = 'include', NOT_INCLUDE = 'doesNotInclude',

  // Numeric/date comparisons
  LESS_THAN = 'lessThan', LESS_THAN_OR_EQUAL = 'lessThanOrEqual',
  GREATER_THAN = 'greaterThan', GREATER_THAN_OR_EQUAL = 'greaterThanOrEqual',
  IN_BETWEEN = 'inBetween', NOT_IN_BETWEEN = 'notInBetween',

  // Date-specific
  IN_THE_PAST = 'inThePast', NOT_IN_THE_PAST = 'notInThePast',
  IN_THE_NEXT = 'inTheNext',
  IN_THE_CURRENT = 'inTheCurrent', NOT_IN_THE_CURRENT = 'notInTheCurrent'
}

enum FilterType { STRING = 'string', NUMBER = 'number', DATE = 'date', BOOLEAN = 'boolean' }

enum UnitOfTime { days = 'days', weeks = 'weeks', months = 'months', quarters = 'quarters', years = 'years' }

Filter Rules

Base filter rule structure for all filter types.

interface FilterRule<O = FilterOperator, T = FieldTarget> {
  id: string;                    // Unique identifier for the filter rule
  target: T;                     // Field being filtered
  operator: O;                   // Filter operator to apply
  values?: any[];                 // Array of values to filter by (content depends on operator)
  settings?: any;                 // Additional settings for date filters
  disabled?: boolean;             // If true, filter is not applied
  required?: boolean;             // If true, filter cannot be removed by users
}

type FieldTarget = { fieldId: string };

type DateFilterSettings = {
  unitOfTime?: UnitOfTime;
  completed?: boolean;  // When true, excludes current incomplete period (e.g., "last 7 completed days" excludes today)
};

type DateFilterRule = FilterRule<FilterOperator, FieldTarget, any, DateFilterSettings>;

function isFilterRule(value: FilterGroup | FilterRule): value is FilterRule;
function isDateFilterRule(filter: FilterRule): filter is DateFilterRule;

Important: Field ID Format

Field IDs in filters use underscore format, not dot notation:

✅ Correct: 'orders_created_at' or 'customers_customer_id'
❌ Incorrect: 'orders.created_at' or 'customers.customer_id'

If a field name itself contains dots, they are replaced with double underscores:

Field customer.id in table customers → Field ID: 'customers_customer__id'

Note: MetricFilterRule (used in AdditionalMetric filters) uses fieldRef with dot notation instead of fieldId:

MetricFilterRule: { fieldRef: 'orders.total_amount' } (dot notation)
FilterRule: { fieldId: 'orders_total_amount' } (underscore format)

Examples

import { FilterOperator, type FilterRule, type DateFilterRule, UnitOfTime } from '@lightdash/common';

// String filter
const nameFilter: FilterRule = {
  id: 'f1',
  target: { fieldId: 'customers_name' },  // Note: underscore format, not dot notation
  operator: FilterOperator.STARTS_WITH,
  values: ['John'],
};

// Numeric filter
const amountFilter: FilterRule = {
  id: 'f2',
  target: { fieldId: 'orders_amount' },  // Note: underscore format
  operator: FilterOperator.GREATER_THAN,
  values: [100],
};

// Date filter (relative)
const dateFilter: DateFilterRule = {
  id: 'f3',
  target: { fieldId: 'orders_created_at' },  // Note: underscore format
  operator: FilterOperator.IN_THE_PAST,
  values: [30],
  settings: { unitOfTime: UnitOfTime.DAYS, completed: true },
};

// Date filter (absolute range)
const dateRangeFilter: FilterRule = {
  id: 'f4',
  target: { fieldId: 'orders_created_at' },
  operator: FilterOperator.IN_BETWEEN,
  values: ['2024-01-01', '2024-12-31'],
};

Filter Groups

Nested AND/OR logic.

type FilterGroup = AndFilterGroup | OrFilterGroup;
type AndFilterGroup = { id: string; and: (FilterGroup | FilterRule)[] };
type OrFilterGroup = { id: string; or: (FilterGroup | FilterRule)[] };

interface Filters {
  dimensions?: FilterGroup;
  metrics?: FilterGroup;
  tableCalculations?: FilterGroup;
}

function isFilterGroup(value: FilterGroup | FilterRule): value is FilterGroup;
function isAndFilterGroup(filterGroup: FilterGroup): boolean;
function isOrFilterGroup(filterGroup: FilterGroup): boolean;

Examples

// Simple AND
const andGroup: FilterGroup = {
  id: 'g1',
  and: [
    { id: 'r1', target: { fieldId: 'orders.status' }, operator: FilterOperator.EQUALS, values: ['completed'] },
    { id: 'r2', target: { fieldId: 'orders.amount' }, operator: FilterOperator.GREATER_THAN, values: [100] },
  ],
};

// Nested: (status = 'completed' OR status = 'pending') AND amount > 100
const nested: FilterGroup = {
  id: 'root',
  and: [
    {
      id: 'status_group',
      or: [
        { id: 'r1', target: { fieldId: 'orders.status' }, operator: FilterOperator.EQUALS, values: ['completed'] },
        { id: 'r2', target: { fieldId: 'orders.status' }, operator: FilterOperator.EQUALS, values: ['pending'] },
      ],
    },
    { id: 'r3', target: { fieldId: 'orders.amount' }, operator: FilterOperator.GREATER_THAN, values: [100] },
  ],
};

Dashboard Filters

Flat arrays with tile targeting.

type DashboardFilters = {
  dimensions: DashboardFilterRule[];
  metrics: DashboardFilterRule[];
  tableCalculations: DashboardFilterRule[];
};

type DashboardFilterRule = FilterRule & {
  tileTargets?: Record<string, DashboardFieldTarget | false>;  // false disables filter for that tile
  label?: string;
  singleValue?: boolean;
};

type DashboardFieldTarget = {
  fieldId: string;
  tableName: string;
  isSqlColumn?: boolean;  // If true, fieldId is SQL column name (for SQL runner tiles)
};

// NOTE: MetricFilterRule uses fieldRef (dot notation "orders.amount") not fieldId (underscore "orders_amount")
interface MetricFilterRule extends FilterRule<FilterOperator, { fieldRef: string }> {}
// Used for metric-level filters or within AdditionalMetric definitions

function isDashboardFilterRule(filter: FilterRule): filter is DashboardFilterRule;

Example

const dashboardFilter: DashboardFilterRule = {
  id: 'df1',
  label: 'Date Range',
  target: { fieldId: 'orders.created_at', tableName: 'orders' },
  operator: FilterOperator.IN_THE_PAST,
  values: [30],
  settings: { unitOfTime: UnitOfTime.DAYS },
  tileTargets: {
    'tile-1': { fieldId: 'orders.created_at', tableName: 'orders' },
    'tile-2': { fieldId: 'revenue.date', tableName: 'revenue' },
    'tile-3': false,  // Exclude this tile
  },
};

Key Functions

Extracting Rules

function getFilterRulesFromGroup(filterGroup: FilterGroup | undefined): FilterRule[];
function getTotalFilterRules(filters: Filters): FilterRule[];
function countTotalFilterRules(filters: Filters): number;
function flattenFilterGroup(filterGroup: FilterGroup): FilterRule[];
// Recursively extracts all rules from nested AND/OR groups into flat array

Creating Filters

function createFilterRuleFromField(field: FilterableField, value?: unknown): FilterRule;

function createDashboardFilterRuleFromField(args: {
  field: FilterableField;
  availableTileFilters: Record<string, FilterableDimension[] | undefined>;
  isTemporary: boolean;
  value?: unknown;
}): DashboardFilterRule;

function addFilterRule(args: {
  filters: Filters;
  field: FilterableField;
  value?: unknown;
}): Filters;

Applying Filters

function addFiltersToMetricQuery(metricQuery: MetricQuery, filters: Filters): MetricQuery;

function addDashboardFiltersToMetricQuery(
  metricQuery: MetricQuery,
  dashboardFilters: DashboardFilters,
  explore?: Explore  // Used to identify time-based dimension overrides
): MetricQuery;
// Applies dashboard filters with time-based dimension override support

Type Guards

function isFilterGroup(value: FilterGroup | FilterRule): value is FilterGroup;
function isFilterRule(value: FilterGroup | FilterRule): value is FilterRule;
function isAndFilterGroup(filterGroup: FilterGroup): boolean;
function isOrFilterGroup(filterGroup: FilterGroup): boolean;
function isFilterTarget(target: any): target is FieldTarget;
function isMetricFilterTarget(target: unknown): boolean;
function isDashboardFilterTarget(target: unknown): target is DashboardFieldTarget;
function isDateFilterRule(filter: FilterRule): filter is DateFilterRule;
function isDashboardFilterRule(filter: FilterRule): filter is DashboardFilterRule;

Filter Group Manipulation

function hasNestedGroups(filters: Filters): boolean;
function getItemsFromFilterGroup(filterGroup: FilterGroup | undefined): FilterGroupItem[];
function getFilterGroupItemsPropertyName(filterGroup: FilterGroup | undefined): 'and' | 'or';
function getFiltersFromGroup(filterGroup: FilterGroup, fields: ItemsMap[string][]): Filters;
// Converts a single filter group into Filters by categorizing rules

Filter Type Detection & Validation

function getFilterTypeFromItem(item: FilterableField): FilterType;
function getFilterTypeFromItemType(type: DimensionType | MetricType | TableCalculationType): FilterType;
function timeframeToUnitOfTime(timeframe: TimeFrames): UnitOfTime | undefined;

function supportsSingleValue(filterType: FilterType, filterOperator: FilterOperator): boolean;
function isWithValueFilter(operator: FilterOperator): boolean;
function isDimensionValueInvalidDate(item: FilterableField, value: any): boolean;

Advanced Filter Creation

function getFilterRuleWithDefaultValue<T extends FilterRule>(
  filterType: FilterType, field: FilterableField | undefined, filterRule: T, values?: unknown[] | null
): T;

function getFilterRuleFromFieldWithDefaultValue<T extends FilterRule>(
  field: FilterableField, filterRule: T, values?: unknown[] | null
): T;

function createDashboardFilterRuleFromSqlColumn(args: {
  column: ResultColumn; availableTileColumns: Record<string, ResultColumn[]>; isTemporary: boolean; value?: unknown;
}): DashboardFilterRule;

function createFilterRuleFromModelRequiredFilterRule(filter: ModelRequiredFilterRule, tableName: string): FilterRule;

Dashboard Filter Utilities

// Get filters for specific tiles
function getDashboardFilterRulesForTile(tileUuid: string, rules: DashboardFilterRule[]): DashboardFilterRule[];
function getDashboardFiltersForTile(tileUuid: string, dashboardFilters: DashboardFilters, temporaryFilters?: DashboardFilters): DashboardFilters;
function getDashboardFilterRulesForTileAndTables(tileUuid: string, tables: string[], rules: DashboardFilterRule[]): DashboardFilterRule[];
function getDashboardFiltersForTileAndTables(tileUuid: string, tables: string[], dashboardFilters: DashboardFilters): DashboardFilters;

// Tile targeting & matching
function isTileFilterable(tile: DashboardTile): boolean;
function applyDefaultTileTargets(filterRule: DashboardFilterRule, field: FilterableDimension, availableTileFilters: Record<string, FilterableDimension[] | undefined>): DashboardFilterRule;
function matchFieldExact(a: Field): (b: Field) => boolean;
function matchFieldByTypeAndName(a: Field): (b: Field) => boolean;

// Conversion & serialization
function convertDashboardFiltersToFilters(dashboardFilters: DashboardFilters): Filters;
function compressDashboardFiltersToParam(dashboardFilters: DashboardFilters): DashboardFiltersFromSearchParam;
function convertDashboardFiltersParamToDashboardFilters(dashboardFilters: DashboardFiltersFromSearchParam): DashboardFilters;
function applyDimensionOverrides(dashboardFilters: DashboardFilters, overrides: DashboardFilters | DashboardFilterRule[]): DashboardFilterRule[];

Filter Overrides & Time-Based Dimensions

type TimeBasedOverrideMap = Record<string, { baseTimeDimensionName: string; fieldsToChange: string[] }>;

function overrideFilterGroupWithFilterRules(
  filterGroup: FilterGroup | undefined, filterRules: FilterRule[], timeBasedOverrideMap: TimeBasedOverrideMap | undefined
): FilterGroup;

function trackWhichTimeBasedMetricFiltersToOverride(
  metricQueryDimensionFilters: FilterGroup | undefined, dashboardFilterRule: DashboardFilterRule, explore?: Explore
): { filter: DashboardFilterRule; overrideData?: { baseTimeDimensionName: string; fieldsToChange: string[] } };
// Identifies time-based dimension filters for override when dashboard filter targets different granularity

Required Filters

function reduceRequiredDimensionFiltersToFilterRules(
  requiredFilters: ModelRequiredFilterRule[], filters: FilterGroup | undefined, explore: Explore
): FilterRule[];
// Converts model-defined required filters to filter rules, excluding any already in query

function resetRequiredFilterRules(filterGroup: FilterGroup, requiredFiltersRef: string[]): FilterGroup;

function isFilterRuleInQuery(dimension: Dimension, filterRule: FilterRule, dimensionsFilterGroup: FilterGroup | undefined): boolean | undefined;

Updating Filters

function deleteFilterRuleFromGroup(filterGroup: FilterGroup, id: string): FilterGroup;

function updateFieldIdInFilters(
  filterGroup: FilterGroup | undefined,
  previousName: string,
  newName: string
): void;

function removeFieldFromFilterGroup(
  filterGroup: FilterGroup | undefined,
  fieldId: string
): FilterGroup | undefined;
// Recursively removes all rules targeting fieldId, cleans up empty groups

function isFilterRuleDefinedForFieldId(
  filterGroup: FilterGroup,
  fieldId: string,
  isInterval?: boolean  // When true, matches field with interval suffixes (_day, _week, etc.)
): boolean;

Complete Example

import { type Filters, FilterOperator, UnitOfTime } from '@lightdash/common';

const filters: Filters = {
  dimensions: {
    id: 'root',
    and: [
      // Last 90 days
      {
        id: 'date',
        target: { fieldId: 'orders.created_at' },
        operator: FilterOperator.IN_THE_PAST,
        values: [90],
        settings: { unitOfTime: UnitOfTime.DAYS },
      },
      // Status: completed OR shipped
      {
        id: 'status_group',
        or: [
          { id: 's1', target: { fieldId: 'orders.status' }, operator: FilterOperator.EQUALS, values: ['completed'] },
          { id: 's2', target: { fieldId: 'orders.status' }, operator: FilterOperator.EQUALS, values: ['shipped'] },
        ],
      },
      // Customer name starts with A
      {
        id: 'customer',
        target: { fieldId: 'customers.name' },
        operator: FilterOperator.STARTS_WITH,
        values: ['A'],
      },
    ],
  },
  metrics: {
    id: 'metrics_root',
    and: [
      { id: 'm1', target: { fieldId: 'orders.total_revenue' }, operator: FilterOperator.GREATER_THAN, values: [1000] },
    ],
  },
};

Common Patterns

Build Filter Dynamically

import { FilterOperator, type FilterGroup, type FilterRule } from '@lightdash/common';

function buildMultiValueFilter(
  fieldId: string,
  values: string[],
  useAnd: boolean = false
): FilterGroup {
  const rules: FilterRule[] = values.map((value, idx) => ({
    id: `rule_${idx}`,
    target: { fieldId },
    operator: FilterOperator.EQUALS,
    values: [value],
  }));

  return {
    id: 'multi_value',
    ...(useAnd ? { and: rules } : { or: rules }),
  };
}

// Usage: Filter where status is one of multiple values
const statusFilter = buildMultiValueFilter('orders.status', ['completed', 'shipped', 'delivered']);

Combine Multiple Filter Groups

function combineFilters(...groups: FilterGroup[]): FilterGroup {
  return {
    id: 'combined',
    and: groups,
  };
}

// Usage
const dateFilter: FilterGroup = { id: 'date', and: [/* date rules */] };
const statusFilter: FilterGroup = { id: 'status', or: [/* status rules */] };
const combined = combineFilters(dateFilter, statusFilter);

Validate and Clean Filters

import { getTotalFilterRules, isFilterRuleDefinedForFieldId, removeFieldFromFilterGroup } from '@lightdash/common';

// Check if query has filters
function hasActiveFilters(filters: Filters): boolean {
  return getTotalFilterRules(filters).length > 0;
}

// Remove obsolete field from all filters
function cleanupFilters(filters: Filters, obsoleteFieldId: string): Filters {
  return {
    dimensions: removeFieldFromFilterGroup(filters.dimensions, obsoleteFieldId),
    metrics: removeFieldFromFilterGroup(filters.metrics, obsoleteFieldId),
    tableCalculations: removeFieldFromFilterGroup(filters.tableCalculations, obsoleteFieldId),
  };
}

// Check if field already has filter
if (isFilterRuleDefinedForFieldId(filters.dimensions, 'orders.created_at')) {
  console.log('Date filter already exists');
}

Type-Safe Filter Processing

import { isFilterGroup, isFilterRule, getItemsFromFilterGroup } from '@lightdash/common';

function processAllFilters(filterGroup: FilterGroup, callback: (rule: FilterRule) => void) {
  const items = getItemsFromFilterGroup(filterGroup);

  items.forEach(item => {
    if (isFilterRule(item)) {
      callback(item);
    } else if (isFilterGroup(item)) {
      processAllFilters(item, callback);  // Recursive processing
    }
  });
}

// Usage: Log all filtered fields
processAllFilters(filters.dimensions, rule => {
  console.log(`Filter on ${rule.target.fieldId} using ${rule.operator}`);
});

Dashboard Filter with Auto-Targeting

import { createDashboardFilterRuleFromField, applyDefaultTileTargets } from '@lightdash/common';

// Create dashboard filter with automatic tile targeting
function createCrossChartFilter(
  field: FilterableField,
  value: unknown,
  availableTileFilters: Record<string, FilterableDimension[] | undefined>
): DashboardFilterRule {
  const baseFilter = createDashboardFilterRuleFromField({
    field,
    availableTileFilters,
    isTemporary: false,
    value,
  });

  // Auto-detect matching fields in other tiles
  return applyDefaultTileTargets(baseFilter, field, availableTileFilters);
}

Edge Cases

Scenario	Behavior	Solution
Field ID format (FilterRule)	Must use underscore format	Use `fieldId: 'orders_total'` not `orders.total`
Field Ref format (MetricFilterRule)	Must use dot notation	Use `fieldRef: 'orders.total'` not `orders_total`
Empty filter group	Invalid, causes errors	Always provide at least one rule or use `{ id: 'root', and: [] }`
NULL/NOT_NULL with values	Values ignored for these operators	Don't provide `values` array for null checks
IN_BETWEEN with wrong value count	Expects exactly 2 values [min, max]	Always provide array with 2 elements
Date filters without settings	May default incorrectly	Always provide `settings: { unitOfTime: UnitOfTime.DAYS }`
completed setting usage	Only works with IN_THE_CURRENT, IN_THE_PAST	Excludes incomplete current period when true
disabled filters	Not applied to query	Use `disabled: true` to temporarily remove filter
Dashboard filter tile targeting	Filter not applied if tile missing from tileTargets	Either include tile or set to `false` explicitly
Mixing AND and OR in same group	Not allowed - one group = one operator	Nest groups: `{ and: [{ or: [...] }, ...] }`

Version

Tile

Files

filters.mddocs/

Filter System

Filter Operators

Filter Rules

Examples

Filter Groups

Examples

Dashboard Filters

Example

Key Functions

Extracting Rules

Creating Filters

Applying Filters

Type Guards

Filter Group Manipulation

Filter Type Detection & Validation

Advanced Filter Creation

Dashboard Filter Utilities

Filter Overrides & Time-Based Dimensions

Required Filters

Updating Filters

Complete Example

Common Patterns

Build Filter Dynamically

Combine Multiple Filter Groups

Validate and Clean Filters

Type-Safe Filter Processing

Dashboard Filter with Auto-Targeting

Edge Cases

Version

Tile

Files

filters.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Filter System

Filter Operators

Filter Rules

Examples

Filter Groups

Examples

Dashboard Filters

Example

Key Functions

Extracting Rules

Creating Filters

Applying Filters

Type Guards

Filter Group Manipulation

Filter Type Detection & Validation

Advanced Filter Creation

Dashboard Filter Utilities

Filter Overrides & Time-Based Dimensions

Required Filters

Updating Filters

Complete Example

Common Patterns

Build Filter Dynamically

Combine Multiple Filter Groups

Validate and Clean Filters

Type-Safe Filter Processing

Dashboard Filter with Auto-Targeting

Edge Cases

filters.mddocs/