Skip to main content

Using Filters

The Fynd Boltic Database SDK provides a powerful and flexible filtering system that supports multiple syntaxes and all field types. This guide covers every filtering capability available in the SDK.

Filter Syntax Options​

The SDK supports three different filter syntaxes to accommodate different use cases:

const { data: records } = await client.records.findAll("users", {
    filters: [
        { field: "status", operator: "=", values: ["active"] },
        { field: "age", operator: ">=", values: [18] },
        {
            field: "department",
            operator: "IN",
            values: ["engineering", "product"],
        },
    ],
});

2. Direct ApiFilter Format (For complex conditions)​

const { data: records } = await client.records.findAll("users", {
    filters: [
        { field: "status", operator: "=", values: ["active"] },
        { field: "age", operator: ">=", values: [18] },
        {
            field: "department",
            operator: "IN",
            values: ["engineering", "product"],
        },
    ],
});

3. FilterBuilder (For programmatic construction)​

import { createFilter } from "@boltic/sdk";

const filters = createFilter()
    .equals("status", "active")
    .greaterThanOrEqual("age", 18)
    .in("department", ["engineering", "product"])
    .build();

const { data: records } = await client.records.findAll("users", { filters });

Using Filters with Different Approaches​

Direct Approach Examples​

// Simple where clause
const { data: records } = await client.records.findAll("users", {
    filters: [
        { field: "status", operator: "=", values: ["active"] },
        { field: "age", operator: ">=", values: [18] },
    ],
});

// ApiFilter format
const { data: records } = await client.records.findAll("users", {
    filters: [{ field: "status", operator: "=", values: ["active"] }],
});

// FilterBuilder
const filters = createFilter().equals("status", "active").build();
const { data: records } = await client.records.findAll("users", { filters });

Supported Operators by Field Type​

Based on the Fynd Boltic Database filtering rules, here are all supported operators for each field type. The tables below show only the essential information needed for implementation:

Table Columns:

  • Operator: The filter operator to use
  • ApiFilter: The actual operator value for the API
  • Values Format: How to format the values array
  • Description: Brief explanation of what the operator does

Text Fields​

OperatorApiFilterValues FormatDescription
Is exactly=['value']Exact match
Not equals!=['value']Not equal to
Contains (case-sensitive)LIKE['value']Contains substring
Contains (case-insensitive)ILIKE['value']Case-insensitive contains
Is one ofIN['value1', 'value2']Value in array
Is emptyIS EMPTY[]Field is empty
Starts withSTARTS WITH['prefix']Begins with string

Array-Specific Operators (for Dropdown/Multi-select fields)​

OperatorApiFilterValues FormatDescription
Array contains@>[['value']]Array contains value
Array not containsNOT @>[['value']]Array doesn't contain
Any element matchesANY['value']Any array element equals
Array is one ofIS ONE OF['value1', 'value2']Array value in list

Text Field Examples​

// Exact match
const { data: users } = await client.records.findAll("users", {
    filters: [{ field: "name", operator: "=", values: ["John Doe"] }],
});

// Case-insensitive search
const { data: results } = await client.records.findAll("users", {
    filters: [
        { field: "name", operator: "ILIKE", values: ["john"] }, // Contains "john"
        { field: "email", operator: "ILIKE", values: ["@company.com"] }, // Ends with "@company.com"
    ],
});

// Exact string matching
const { data: results } = await client.records.findAll("users", {
    filters: [
        { field: "email", operator: "LIKE", values: ["gmail.com"] }, // Case-sensitive contains
        { field: "name", operator: "STARTS WITH", values: ["Dr."] }, // Starts with "Dr."
    ],
});

// Multiple text conditions
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "role", operator: "!=", values: ["admin"] },
        { field: "department", operator: "IN", values: ["sales", "marketing"] },
        { field: "bio", operator: "IS EMPTY", values: [] },
    ],
});

Email Fields​

Email fields support the same operators as text fields:

// Find specific email domains
const { data: users } = await client.records.findAll("users", {
    filters: [{ field: "email", operator: "ILIKE", values: ["@company.com"] }],
});

// Multiple email conditions
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "email", operator: "!=", values: ["[email protected]"] },
        { field: "backup_email", operator: "IS EMPTY", values: [] },
    ],
});

Long Text Fields​

Long Text fields support the same operators as text fields:

// Long text filtering
const { data: posts } = await client.records.findAll("blog_posts", {
    filters: [
        { field: "content", operator: "!=", values: [""] },
        { field: "summary", operator: "IS EMPTY", values: [] },
    ],
});

Number Fields​

OperatorApiFilterValues FormatDescription
Is exactly=[value]Exact match
Not equals!=[value]Not equal to
Greater than>[value]Greater than
Greater than or equal>=[value]Greater than or equal
Less than<[value]Less than
Less than or equal<=[value]Less than or equal
BetweenBETWEEN[min, max]Within range
Is one ofIN[value1, value2, value3]Value in array
Is emptyIS EMPTY[]Field is empty

Number Field Examples​

// Basic number comparisons
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "age", operator: ">=", values: [18] }, // Adults only
        { field: "experience_years", operator: "BETWEEN", values: [2, 10] }, // Mid-level experience
    ],
});

// Score ranges
const { data: candidates } = await client.records.findAll("candidates", {
    filters: [
        { field: "interview_score", operator: ">", values: [75] },
        { field: "technical_score", operator: ">=", values: [80] },
        { field: "years_experience", operator: "IN", values: [3, 5, 7, 10] },
    ],
});

// Using FilterBuilder
const experienceFilter = createFilter()
    .greaterThanOrEqual("age", 25)
    .lessThan("age", 50)
    .between("salary_expectation", 50000, 120000)
    .build();

Currency Fields​

Currency fields support the same operators as number fields:

// Salary filtering
const { data: employees } = await client.records.findAll("employees", {
    filters: [
        { field: "salary", operator: "BETWEEN", values: [50000, 100000] },
        { field: "bonus", operator: ">", values: [5000] },
    ],
});

// Product pricing
const { data: products } = await client.records.findAll("products", {
    filters: [
        { field: "price", operator: "<=", values: [999.99] },
        { field: "cost", operator: ">", values: [0] },
    ],
});

Date-Time Fields​

OperatorApiFilterValues FormatDescription
Is exactly=['2024-01-15T10:30:00Z']Exact match
Not equals!=[null]Not equal to
Is on or after>=['2024-01-01T00:00:00Z']On or after date
Is after>['2024-01-01T00:00:00Z']After date
Is before<['2024-12-31T23:59:59Z']Before date
Is on or before<=['2024-06-30T23:59:59Z']On or before date
Is betweenBETWEEN['start_date', 'end_date']Within date range
Is withinWITHIN['period_name']Predefined periods

Date Within Periods​

The WITHIN operator supports predefined time periods:

LabelValueDescription
YesterdayyesterdayYesterday's date
TodaytodayToday's date
TomorrowtomorrowTomorrow's date
The past weekthe-past-weekLast 7 days
The next weekthe-next-weekNext 7 days
The past monththe-past-monthLast 30 days
The next monththe-next-monthNext 30 days
The past yearthe-past-yearLast 365 days
The next yearthe-next-yearNext 365 days
This calendar weekthis-calendar-weekCurrent week (Mon-Sun)
This calendar monththis-calendar-monthCurrent month
This calendar yearthis-calendar-yearCurrent year
Year to dateyear-to-dateFrom Jan 1 to now
Month to datemonth-to-dateFrom month start to now
Week to dateweek-to-dateFrom week start to now
Last 7 dayslast-7-daysPast 7 days
Next 7 daysnext-7-daysNext 7 days
Last 30 dayslast-30-daysPast 30 days
Next 30 daysnext-30-daysNext 30 days
Last 365 dayslast-365-daysPast 365 days
Next 365 daysnext-365-daysNext 365 days

Date-Time Examples​

// Basic date filtering
const { data: posts } = await client.records.findAll("blog_posts", {
    filters: {
        published_at: { $gte: "2024-01-01T00:00:00Z" },
        created_at: { $lte: new Date().toISOString() },
    },
});

// Date ranges
const { data: orders } = await client.records.findAll("orders", {
    filters: {
        order_date: {
            $between: ["2024-01-01T00:00:00Z", "2024-03-31T23:59:59Z"],
        },
    },
});

// Using WITHIN operator with predefined periods
const { data: recentActivity } = await client.records.findAll("user_activity", {
    filters: [{ field: "last_active", operator: "WITHIN", values: ["last-7-days"] }],
});

// Multiple date conditions
const { data: events } = await client.records.findAll("events", {
    filters: {
        start_date: { $gte: new Date().toISOString() },
        end_date: { $lte: "2024-12-31T23:59:59Z" },
        created_at: { $within: "this-calendar-month" },
    },
});

Checkbox (Boolean) Fields​

OperatorApiFilterValues FormatDescription
True=[true]Field is true
False!=[false]Field is false
// Boolean filtering
const { data: users } = await client.records.findAll("users", {
    filters: {
        is_active: true,
        is_verified: true,
        is_admin: false,
    },
});

// Using ApiFilter format
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "is_active", operator: "=", values: [true] },
        { field: "is_deleted", operator: "=", values: [false] },
    ],
});

Dropdown fields are stored as arrays and require special array operators for proper filtering:

// Single selection dropdown (stored as ['value'])
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "status", operator: "@>", values: [["active"]] }, // Check if array contains 'active'
        { field: "priority", operator: "ANY", values: ["high"] }, // Check if any element equals 'high'
    ],
});

// Multiple selection dropdown filtering
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "skills", operator: "@>", values: [["JavaScript"]] }, // Has JavaScript skill
        {
            field: "departments",
            operator: "IS ONE OF",
            values: ["engineering", "product"],
        }, // Department is one of these
    ],
});

// Using ApiFilter format for arrays
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "skills", operator: "@>", values: [["JavaScript"]] }, // Array contains
        { field: "priority", operator: "ANY", values: ["high"] }, // Any element matches
        { field: "tags", operator: "IS ONE OF", values: ["urgent", "review"] }, // Is one of values
    ],
});

// FilterBuilder with array methods (currently supports arrayContains)
const filters = createFilter()
    .arrayContains("skills", "TypeScript")
    .arrayContains("departments", "engineering")
    .build();

const { data: users } = await client.records.findAll("users", { filters });

Important Notes for Dropdown Fields:

  • ✅ Use $arrayContains or @> for checking if array contains a value
  • ✅ Use $any or ANY for checking if any array element matches
  • ❌ Don't use regular $in with nested arrays like [['value']]
  • ❌ Don't use string operations like $like on array fields

Common Mistakes to Avoid:

// ❌ WRONG - This will cause "Malformed array literal" errors
const badFilters = [{ field: "dropdown_field", operator: "IN", values: ["Option 1", "Option 2"] }];

// ✅ CORRECT - Use array-specific operators
const goodFilters = [
    { field: "dropdown_field", operator: "@>", values: [["Option 1"]] }, // Contains
    { field: "dropdown_field", operator: "ANY", values: ["Option 2"] }, // Any element matches
];

Phone Number Fields​

Phone fields support the same operators as text fields:

// Phone number filtering
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "phone", operator: "STARTS WITH", values: ["+1"] },
        { field: "mobile", operator: "IS EMPTY", values: [] },
    ],
});

Link fields support the same operators as text fields:

// URL filtering
const { data: websites } = await client.records.findAll("websites", {
    filters: [
        { field: "url", operator: "LIKE", values: ["https://"] },
        { field: "backup_url", operator: "IS EMPTY", values: [] },
    ],
});

JSON Fields​

OperatorSDK SyntaxApiFilterDescription
Is exactlyvalue=Exact match
Not equals{ $ne: value }!=Not equal to
Is empty{ $isEmpty: true }IS EMPTYField is empty
// JSON field filtering
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "preferences", operator: "!=", values: [null] },
        { field: "metadata", operator: "IS EMPTY", values: [] },
    ],
});

// Exact JSON matching
const { data: configs } = await client.records.findAll("configurations", {
    filters: [
        {
            field: "settings",
            operator: "=",
            values: [{ theme: "dark", language: "en" }],
        },
    ],
});

Vector Fields (vector, halfvec, sparsevec)​

Vector fields support both standard comparison operators and specialized distance operators.

⚠️ Critical Note for Vector Operators: When using vector distance operators (<+>, <->, *, <#>, <=>), the vector values must be passed as strings wrapped in double quotes, not as raw arrays. The backend expects the vector in string format like "[0.1,0.2,0.3]".

Correct Format:

// ✅ CORRECT - Vector as string in double quotes
{ field: 'embedding', operator: '<->', values: ['[0.1,0.2,0.3]'] }

// ❌ WRONG - Raw array
{ field: 'embedding', operator: '<->', values: [[0.1,0.2,0.3]] }

Standard Operators​

OperatorApiFilterValues FormatDescription
Is exactly=[value]Exact match
Not equals!=[value]Not equal to
Greater than>[threshold]Greater than
Greater than or equal>=[threshold]Greater than or equal
Less than<[threshold]Less than
Less than or equal<=[threshold]Less than or equal
BetweenBETWEEN[min, max]Within range
Is one ofIN[vector1, vector2]Vector in array
Is emptyIS EMPTY[]Field is empty

Distance Operators​

OperatorApiFilterValues FormatDescription
Manhattan Distance (L1)<+>["[0.1,0.2,0.3]"]Sum of absolute differences
Euclidean Distance (L2)<->["[0.1,0.2,0.3]"]Straight-line distance
Inner Product*["[0.1,0.2,0.3]"]Dot product
Negative Inner Product<#>["[0.1,0.2,0.3]"]Negative dot product
Cosine Distance<=>["[0.1,0.2,0.3]"]Angular distance

⚠️ Important: Vector values must be strings wrapped in double quotes, not raw arrays. Use ["[0.1,0.2,0.3]"] format.

Vector Examples​

// Similarity search using Euclidean distance
// Note: Vector must be a string wrapped in double quotes
const queryVector = "[0.1, 0.2, 0.3, 0.4, 0.5]";

const { data: similar } = await client.records.findAll("documents", {
    filters: [
        {
            field: "content_vector",
            operator: "<->", // Euclidean distance
            values: [queryVector], // Vector as string in array
        },
    ],
    sort: [{ field: "content_vector", direction: "asc" }], // Closest first
    page: { page_no: 1, page_size: 10 },
});

// Different distance operators
const manhattanResults = await client.records.findAll("embeddings", {
    filters: [
        { field: "vector", operator: "<+>", values: [queryVector] }, // Manhattan
    ],
});

const cosineResults = await client.records.findAll("embeddings", {
    filters: [
        { field: "vector", operator: "<=>", values: [queryVector] }, // Cosine
    ],
});

// Standard vector operations
const { data: vectors } = await client.records.findAll("embeddings", {
    filters: [
        { field: "vector", operator: "!=", values: [null] },
        { field: "confidence_score", operator: ">=", values: [0.8] },
    ],
});

// Sparse vector filtering
const { data: sparse } = await client.records.findAll("sparse_embeddings", {
    filters: [{ field: "sparse_vector", operator: "=", values: ["{1:1,3:2,5:3}/5"] }],
});

⚠️ Important Note for Vector Operators: When using vector distance operators (<+>, <->, *, <#>, <=>), the vector values must be passed as strings wrapped in quotes, not as raw arrays. The backend expects the vector in string format like "[0.1,0.2,0.3]".

Encrypted Fields​

Encrypted fields have special filtering rules based on their encryption mode.

Filtering Rules​

OperatorApiFilterValues FormatDescription
Is exactly=['value']Exact match
Not equals!=['value']Not equal to
Starts withSTARTS WITH['prefix']Begins with string
Is emptyIS EMPTY[]Field is empty
Encrypted Column Filtering Constraints
  • Only deterministic encrypted columns can be filtered - Non-deterministic encrypted columns will throw an error if used in filters
  • Supported operators - Only =, !=, STARTS WITH, and IS EMPTY are supported
  • Encrypted values - Filter values are compared against decrypted data in the backend (for deterministic fields)

Encrypted Field Examples​

// ✅ CORRECT - Filtering on deterministic encrypted column
const { data: users } = await client.records.findAll("users", {
    filters: [
        { field: "ssn", operator: "=", values: ["123-45-6789"] }, // Deterministic encrypted
    ],
});

// ✅ CORRECT - Using STARTS WITH on deterministic encrypted column
const { data: customers } = await client.records.findAll("customers", {
    filters: [
        {
            field: "email_encrypted",
            operator: "STARTS WITH",
            values: ["user1"],
        },
    ],
});

// ✅ CORRECT - Using IS EMPTY on encrypted column
const { data: profiles } = await client.records.findAll("users", {
    filters: [
        {
            field: "api_key",
            operator: "IS EMPTY",
            values: [],
        },
    ],
});

// ✅ CORRECT - Combining encrypted and non-encrypted filters
const { data: employees } = await client.records.findAll("employees", {
    filters: [
        { field: "ssn", operator: "=", values: ["123-45-6789"] }, // Deterministic encrypted
        { field: "department", operator: "=", values: ["engineering"] }, // Regular field
        { field: "is_active", operator: "=", values: [true] }, // Regular field
    ],
});

// ❌ WRONG - Filtering on non-deterministic encrypted column
const { data: users, error } = await client.records.findAll("users", {
    filters: [
        { field: "api_key_non_det", operator: "=", values: ["some-key"] }, // Non-deterministic - will error
    ],
});
// Error: "Cannot filter on non-deterministic encrypted column 'api_key_non_det'"

// ❌ WRONG - Using unsupported operators on encrypted columns
const { data: users, error } = await client.records.findAll("users", {
    filters: [
        { field: "ssn", operator: "LIKE", values: ["123"] }, // LIKE not supported
    ],
});

Using FilterBuilder with Encrypted Columns​

import { createFilter } from "@boltic/sdk";

// Build filter with encrypted column
const filter = createFilter()
    .equals("ssn", "123-45-6789") // Deterministic encrypted
    .equals("status", "active") // Regular field
    .startsWith("email_encrypted", "user") // Supported for encrypted
    .build();

const { data: employees } = await client.records.findAll("employees", {
    filters: filter,
});

Complex Filter Combinations​

Multiple Field Filtering (AND Logic)​

All conditions in the same filter object are combined with AND logic:

const { data: users } = await client.records.findAll("users", {
    filters: {
        status: "active", // AND
        age: { $gte: 25 }, // AND
        department: "engineering", // AND
        is_verified: true, // AND
    },
});

Using FilterBuilder for Complex Conditions​

import { createFilter } from "@boltic/sdk";

// Complex multi-field filter
const complexFilter = createFilter()
    .equals("status", "active")
    .greaterThanOrEqual("age", 25)
    .lessThan("age", 65)
    .in("department", ["engineering", "product", "design"])
    .like("email", "%@company.com")
    .between("join_date", "2020-01-01T00:00:00Z", "2024-12-31T23:59:59Z")
    .equals("is_verified", true)
    .isNull("termination_date")
    .build();

const { data: employees } = await client.records.findAll("employees", {
    filters: complexFilter,
    sort: [{ field: "join_date", direction: "desc" }],
    page: { page_no: 1, page_size: 50 },
});

Dynamic Filter Building​

function buildUserFilter(criteria: {
    status?: string;
    ageRange?: { min: number; max: number };
    departments?: string[];
    verified?: boolean;
    searchTerm?: string;
}) {
    const filterBuilder = createFilter();

    if (criteria.status) {
        filterBuilder.equals("status", criteria.status);
    }

    if (criteria.ageRange) {
        filterBuilder.between("age", criteria.ageRange.min, criteria.ageRange.max);
    }

    if (criteria.departments && criteria.departments.length > 0) {
        filterBuilder.in("department", criteria.departments);
    }

    if (criteria.verified !== undefined) {
        filterBuilder.equals("is_verified", criteria.verified);
    }

    if (criteria.searchTerm) {
        filterBuilder.like("name", `%${criteria.searchTerm}%`);
    }

    return filterBuilder.build();
}

// Usage
const filters = buildUserFilter({
    status: "active",
    ageRange: { min: 25, max: 45 },
    departments: ["engineering", "product"],
    verified: true,
    searchTerm: "john",
});

const { data: users } = await client.records.findAll("users", { filters });

Advanced Filtering Patterns​

Null and Empty Value Handling​

// Find records with missing data
const { data: incomplete } = await client.records.findAll("users", {
    filters: [
        { field: "phone", operator: "IS EMPTY", values: [] },
        { field: "address", operator: "IS NULL", values: [] },
    ],
});

// Find complete profiles
const completeProfileFilter = createFilter()
    .notEquals("name", "")
    .notEquals("email", "")
    .isNotNull("phone")
    .isNotNull("address")
    .build();

Date Range Queries​

// This quarter's data
const thisQuarter = await client.records.findAll("sales", {
    filters: [{ field: "sale_date", operator: "WITHIN", values: ["this-calendar-month"] }],
});

// Custom date range
const customRange = await client.records.findAll("orders", {
    filters: [
        {
            field: "order_date",
            operator: "BETWEEN",
            values: ["2024-01-01T00:00:00Z", "2024-03-31T23:59:59Z"],
        },
    ],
});

// Recent activity
const recentActivity = await client.records.findAll("user_sessions", {
    filters: [{ field: "last_active", operator: "WITHIN", values: ["last-7-days"] }],
});

Filter Usage Patterns Summary​

Correct Values Format for Different Operators​

Based on the backend implementation, here's how to correctly format values for each operator:

1. Regular Field Operators (text, number, date, boolean)​

  • Values Format: ['value'] or ['value1', 'value2'] (flat arrays)
  • Examples:
    • { field: 'name', operator: '=', values: ['John'] }
    • { field: 'age', operator: 'IN', values: [25, 30, 35] }
    • { field: 'status', operator: '!=', values: ['inactive'] }

2. Array Field Operators (dropdown, multi-select)​

  • @> (Array Contains): values: [['value']] (nested array)
    • Example: { field: 'skills', operator: '@>', values: [['JavaScript']] }
  • ANY (Any Element): values: ['value'] (flat array)
    • Example: { field: 'tags', operator: 'ANY', values: ['urgent'] }
  • IS ONE OF (Array Overlap): values: ['value1', 'value2'] (flat array)
    • Example: { field: 'departments', operator: 'IS ONE OF', values: ['eng', 'sales'] }

3. Vector Field Operators​

  • Distance Operators: values: ["[0.1,0.2,0.3]"] (string representation of vector in array)
    • Example: { field: 'embedding', operator: '<->', values: ["[0.1,0.2,0.3]"] }
    • ⚠️ Important: Vector values must be strings wrapped in double quotes, not raw arrays
  • Standard Operators: values: [value] (flat array)
    • Example: { field: 'confidence', operator: '>', values: [0.8] }

Key Points to Remember:​

  • IN operator: Always use flat arrays ['value1', 'value2']
  • Array operators: Use nested arrays [['value']] for containment, flat arrays for overlap
  • All values: Must be wrapped in an array, even for single values
  • Null values: Use [null] not null
  • Vector operators: Vector values must be strings wrapped in double quotes like ["[0.1,0.2,0.3]"], not raw arrays

Best Practices​

1. Use Indexed Fields for Primary Filters​

// Good - use indexed fields first
const { data: results } = await client.records.findAll("users", {
    filters: [
        { field: "status", operator: "=", values: ["active"] }, // Indexed field
        { field: "department_id", operator: "=", values: [123] }, // Indexed field
        { field: "name", operator: "ILIKE", values: ["%john%"] }, // Secondary filter
    ],
});

2. Combine Filters Efficiently​

// Good - specific filters first
const efficientFilter = createFilter()
    .equals("status", "active") // Most selective first
    .in("department", ["eng", "product"]) // Moderately selective
    .greaterThan("age", 25) // Less selective
    .like("bio", "%developer%") // Least selective last
    .build();

3. Use Appropriate Page Sizes​

// Good - reasonable page size with filters
const { data: records } = await client.records.findAll("large_table", {
    filters: [{ field: "status", operator: "=", values: ["active"] }],
    page: { page_no: 1, page_size: 100 }, // Manageable size
});