How Subscriber Segmentation Works in Listmonk: PostgreSQL-Powered Filtering

Listmonk enables subscriber segmentation by accepting partial PostgreSQL SQL expressions that filter the subscribers table and JSON attribs column, validating them against allowed tables, compiling them into query templates, and executing them in read-only transactions before any data mutation occurs.

Listmonk's subscriber segmentation feature allows you to target specific audiences by writing SQL fragments that operate on subscriber data stored in PostgreSQL. This powerful approach lets you filter by standard columns like email and status, or drill into JSON-encoded attributes using PostgreSQL's native JSON operators. The system compiles these expressions into safe, templated queries while preventing unauthorized table access through strict validation layers.

The Segmentation Pipeline

The subscriber segmentation flow in Listmonk follows a strict five-step process that balances flexibility with database security. Each query passes through validation, compilation, and dry-run phases before touching production data.

Input Handling and API Entry Points

Segmentation begins when a client sends a request containing a search string (search) and a query expression (query). In cmd/subscribers.go, handlers like the subscriber API endpoint parse these parameters and forward them to the core layer. The query expression must be a valid PostgreSQL WHERE clause fragment that can reference columns from the subscribers table, including JSON operators on the attribs field.

GET /api/subscribers?query=subscribers.attribs->>'city' = 'Bengaluru'

Table Validation and Security

Before compilation, Listmonk validates that your expression only references permitted tables. The function validateQueryTables in internal/core/subscribers.go checks the parsed SQL against an allowlist (allowedSubQueryTables) that includes subscribers, lists, and campaigns. This prevents arbitrary table access or potential data exfiltration through malicious SQL fragments.

If your query references tables outside this allowlist, the validation fails immediately and returns an error before any database execution occurs.

Template Compilation with Dry-Run Protection

The core compilation logic resides in models/queries.go within the compileSubscriberQueryTpl function. This function interpolates your validated expression into the QuerySubscribersTpl template by replacing the %query% placeholder:

// models/queries.go
stmt := strings.ReplaceAll(q.QuerySubscribersTpl, "%query%", cond)

Before returning the final statement, Listmonk executes the compiled query inside a read-only transaction as a dry-run. This catches syntax errors and ensures the expression is semantically valid without risking data modification. Only after successful dry-run completion does the system return the compiled statement for actual use.

Execution and Result Retrieval

For subscriber fetching, internal/core/subscribers.go executes the compiled query through QuerySubscribers. The function first obtains a total count via getSubscriberCount, then runs the paginated query against the database, scanning results into a models.Subscribers slice:

// internal/core/subscribers.go
stmt := strings.ReplaceAll(c.q.QuerySubscribers, "%query%", cond)
stmt = strings.ReplaceAll(stmt, "%order%", orderBy+" "+order)
tx.Select(&out, stmt, pq.Array(listIDs), subStatus, searchStr, offset, limit)

For bulk mutations, the same compiled filter gets embedded into action-specific templates like AddSubscribersToListsByQuery or DeleteSubscriptionsByQuery.

Writing Segmentation Queries

Listmonk's query language supports standard SQL operators on table columns and specialized PostgreSQL JSON syntax for the attribs column. This dual approach allows both simple email filtering and complex attribute-based targeting.

Standard Column Filters

Filter subscribers using direct column comparisons on the subscribers table:

• Email pattern matching: subscribers.email LIKE '%@example.com'
• Status filtering: subscribers.status = 'blocklisted'
• Date ranges: subscribers.created_at > '2024-01-01'

JSON Attribute Queries

The attribs column stores JSONB data, enabling deep filtering on custom subscriber metadata using PostgreSQL JSON operators:

Expression	Description
subscribers.attribs->>'city' = 'Bengaluru'	Exact match on string attribute
(subscribers.attribs->>'projects')::INT > 3	Numeric comparison with type casting
subscribers.attribs->'stack'->'languages' ? 'python'	Array containment check for nested JSON
subscribers.attribs ? 'likes_tea'	Existence test for attribute key

Complex Conditions and Table Joins

Advanced segmentation can combine multiple conditions using boolean operators or join with related tables:

subscribers.attribs->>'city' = 'Bengaluru' 
  AND (subscribers.attribs->>'projects')::INT > 3

You can also filter by campaign engagement using subqueries or joins:

EXISTS (
  SELECT 1 FROM campaign_views 
  WHERE campaign_views.subscriber_id = subscribers.id 
  AND campaign_views.campaign_id = 42
)

Implementation Details

Query Compilation Logic

The compileSubscriberQueryTpl function in models/queries.go serves as the central compilation engine. It performs placeholder substitution and validation:

// models/queries.go
stmt := strings.ReplaceAll(q.QuerySubscribersTpl, "%query%", cond)
if _, err := tx.Exec(stmt, true, pq.Int64Array{}, subStatus, searchStr); err != nil {
    return "", err
}

The dry-run execution uses tx.Exec with a true flag indicating read-only mode, ensuring the query parses correctly without side effects.

Bulk Operations Execution

For bulk actions, ExecSubQueryTpl handles the injection of segmentation filters into mutating queries. Located in models/queries.go, this function takes the compiled filter expression and inserts it into templates like AddSubscribersToListsByQuery:

// models/queries.go
stmt := strings.ReplaceAll(baseQueryTpl, "%query%", filterExp)
a := append([]any{false, pq.Array(listIDs), subStatus, searchStr}, args...)
_, err := db.Exec(stmt, a...)

In internal/core/subscriptions.go, methods like AddSubscriptionsByQuery orchestrate this process by calling ExecSubQueryTpl with the appropriate template and parameters:

// internal/core/subscriptions.go
err := c.q.ExecSubQueryTpl(searchStr, queryExp,
    c.q.AddSubscribersToListsByQuery, sourceListIDs, c.db, subStatus,
    pq.Array(targetListIDs), status)

Practical Examples

API-Based Subscriber Segmentation

To retrieve subscribers from Bengaluru with more than three projects:

GET /api/subscribers?search=&query=subscribers.attribs->>'city' = 'Bengaluru' AND (subscribers.attribs->>'projects')::INT > 3

This request reaches cmd/subscribers.go, which validates the query string and forwards it to core.QuerySubscribers for execution against the database.

Bulk Subscription by Segment

Add all tea-loving subscribers from source list 12 to target list 34:

POST /api/subscribers/subscriptions
Content-Type: application/json

{
  "search": "",
  "query": "subscribers.attribs->>'likes_tea' = 'true'",
  "source_list_ids": [12],
  "target_list_ids": [34],
  "status": "subscribed",
  "subscription_status": "enabled"
}

Internally, cmd/subscribers.go invokes core.AddSubscriptionsByQuery, which uses ExecSubQueryTpl to embed the filter into the AddSubscribersToListsByQuery template and apply the subscription changes atomically to all matching records.

Summary

• SQL-Based Filtering: Listmonk subscriber segmentation uses partial PostgreSQL expressions that filter the subscribers table and JSON attribs column.
• Security Layers: Queries undergo table validation (validateQueryTables) and read-only dry-run execution before compilation completes.
• Template Compilation: The compileSubscriberQueryTpl function in models/queries.go safely interpolates user expressions into predefined SQL templates using placeholder replacement.
• Bulk Operations: The ExecSubQueryTpl function enables bulk mutations by injecting validated filters into action-specific templates like AddSubscribersToListsByQuery.
• JSON Support: Advanced targeting leverages PostgreSQL JSON operators (->>, ?, type casting) on the attribs column for flexible metadata filtering.

Frequently Asked Questions

What query syntax does Listmonk use for subscriber segmentation?

Listmonk accepts standard PostgreSQL WHERE clause fragments that reference the subscribers table. You can filter by direct columns like subscribers.email or subscribers.status, and use PostgreSQL JSON operators such as ->> and ? to query the JSON attribs column. The syntax supports boolean operators (AND, OR), comparison operators, and subqueries for complex segmentation.

How does Listmonk prevent SQL injection in segmentation queries?

Listmonk implements multiple security measures: the validateQueryTables function in internal/core/subscribers.go restricts queries to an allowlist of safe tables (subscribers, lists, campaigns), and compileSubscriberQueryTpl in models/queries.go executes every compiled query in a read-only transaction as a dry-run before returning the statement. This ensures syntactic validity while preventing data modification through injection attacks.

Can I segment subscribers by their campaign engagement history?

Yes, you can filter subscribers based on campaign interactions using subqueries or joins with the campaign_views table. For example, the expression EXISTS (SELECT 1 FROM campaign_views WHERE campaign_views.subscriber_id = subscribers.id AND campaign_views.campaign_id = 42) returns only subscribers who viewed campaign ID 42. This works because campaign_views is included in the allowed tables for segmentation queries.

How do I perform bulk actions on segmented subscribers?

Use the_bulk subscription API endpoints at /api/subscribers/subscriptions with a query parameter containing your segmentation expression. The AddSubscriptionsByQuery function in internal/core/subscriptions.go processes these requests by calling ExecSubQueryTpl, which injects your validated filter into templates like AddSubscribersToListsByQuery to atomically update all matching subscribers' list memberships or subscription statuses.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}