# How Subscriber Segmentation Works in Listmonk: PostgreSQL-Powered Filtering

> Discover how Listmonk leverages PostgreSQL for powerful subscriber segmentation. Learn how to filter subscribers using SQL expressions and JSON attributes to refine your campaigns.

- Repository: [Kailash Nadh/listmonk](https://github.com/knadh/listmonk)
- Tags: deep-dive
- Published: 2026-05-19

---

**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`](https://github.com/knadh/listmonk/blob/main/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.

```http
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`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/models/queries.go) within the `compileSubscriberQueryTpl` function. This function interpolates your validated expression into the `QuerySubscribersTpl` template by replacing the `%query%` placeholder:

```go
// 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`](https://github.com/knadh/listmonk/blob/main/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:

```go
// 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:

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

```

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

```sql
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`](https://github.com/knadh/listmonk/blob/main/models/queries.go) serves as the central compilation engine. It performs placeholder substitution and validation:

```go
// 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`](https://github.com/knadh/listmonk/blob/main/models/queries.go), this function takes the compiled filter expression and inserts it into templates like `AddSubscribersToListsByQuery`:

```go
// 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`](https://github.com/knadh/listmonk/blob/main/internal/core/subscriptions.go), methods like `AddSubscriptionsByQuery` orchestrate this process by calling `ExecSubQueryTpl` with the appropriate template and parameters:

```go
// 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:

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

```

This request reaches [`cmd/subscribers.go`](https://github.com/knadh/listmonk/blob/main/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:

```http
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`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/internal/core/subscribers.go) restricts queries to an allowlist of safe tables (`subscribers`, `lists`, `campaigns`), and `compileSubscriberQueryTpl` in [`models/queries.go`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/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.