Skip to main content

Querying and segmenting subscribers

listmonk allows the writing of partial Postgres SQL expressions to query, filter, and segment subscribers.

Database fields

These are the fields in the subscriber database that can be queried.

FieldDescription
subscribers.uuidThe randomly generated unique ID of the subscriber
subscribers.emailE-mail ID of the subscriber
subscribers.nameName of the subscriber
subscribers.statusStatus of the subscriber (enabled, disabled, blocklisted)
subscribers.attribsMap of arbitrary attributes represented as JSON. Accessed via the -> and ->> Postgres operator.
subscribers.created_atTimestamp when the subscriber was first added
subscribers.updated_atTimestamp when the subscriber was modified

Sample attributes

Here's a sample JSON map of attributes assigned to an imaginary subscriber.

{
"city": "Bengaluru",
"likes_tea": true,
"spoken_languages": ["English", "Malayalam"],
"projects": 3,
"stack": {
"frameworks": ["echo", "go"],
"languages": ["go", "python"],
"preferred_language": "go"
}
}

listmonk screenshot

Sample SQL query expressions

listmonk

Find a subscriber by e-mail

-- Exact match
subscribers.email = 'some@domain.com'

-- Partial match to find e-mails that end in @domain.com.
subscribers.email LIKE '%@domain.com'

Find a subscriber by name

-- Find all subscribers whose name start with John.
subscribers.email LIKE 'John%'

Multiple conditions

-- Find all Johns who have been blocklisted.
subscribers.email LIKE 'John%' AND status = 'blocklisted'

Querying attributes

-- The ->> operator returns the value as text. Find all subscribers
-- who live in Bengaluru and have done more than 3 projects.
-- Here 'projects' is cast into an integer so that we can apply the
-- numerical operator >
subscribers.attribs->>'city' = 'Bengaluru' AND
(subscribers.attribs->>'projects')::INT > 3

Querying nested attributes

-- Find all blocklisted subscribers who like to drink tea, can code Python
-- and prefer coding Go.
--
-- The -> operator returns the value as a structure. Here, the "languages" field
-- The ? operator checks for the existence of a value in a list.
subscribers.status = 'blocklisted' AND
(subscribers.attribs->>'likes_tea')::BOOLEAN = true AND
subscribers.attribs->'stack'->'languages' ? 'python' AND
subscribers.attribs->'stack'->>'preferred_language' = 'go'

To learn how to write SQL expressions to do advancd querying on JSON attributes, refer to the Postgres JSONB documentation.