Contains, startsWith and endsWith (LIKE queries)

There's been a long-time desire to support LIKE queries in Ghost.

From a user perspective, this would allow us to do "contains", "startsWith" and "endsWith" type filters.

Reference implementations

• validate that mingo can convert $regex and $not correctly
• validate that we could handle regex in our knex query builder
• very old implementation of LIKE by a contributor

NQL Syntax

• ~ contains
• ~^ starts with
• ~$ ends with

All 3 can be prefixed with - for not

Examples

• members.email:~@gmail - contains "@gmail"
• members.email:~^fred@ - starts with "fred@"
• members.email:~$@gmail.com - ends with "@gmail.com"
• members.email:-~@gmail - does not contain "@gmail"

Mongo Syntax

Under the hood, NQL gets converted into Mongo's JSON query representation.

To check “contains” in mongo you need to do one of the below ref:

{ email: /fred@/i }

{ email: { $regex: /fred@/i } }

Not contains is one of:

{ email: { $not: /fred@/i } }

{ email: { $not: { $regex: /fred@/i } } }

The i is used to make the query case insensitive.

To make this easy to parse and process, we'll use the following two patterns:

{ email: { $regex: /fred@/i } }
{ email: { $not: /fred@/i } }

Starts with and ends with will become modifications on the regex:

{ email: { $regex: /^fred@/i } }
{ email: { $regex: /@gmail.com$/i } }

Have confirmed this works in mingo (the tool we use to query JSON directly).

A gotcha here is it will treat characters as regex characters, so we probably have to escape regex chars in the transform from NQL → mongo and then unescape them in the transform from mongo → SQL 🤔

SQL Syntax

SQL has two operators we could use here - LIKE or REGEXP:

Neither of these is case insensitive by default. LIKE only does wildcard matches, REGEXP allows for a wider range of queries & is therefore slower. We don’t need that power, complexity or risk right now, nor has there ever been a request for it, so we will stick to LIKE.

In order to use LIKE and case insensitive matching the SQL queries we need are:

select * from members.email where LOWER(email) LIKE LOWER('%fred@%'); // contains
select * from members.email where LOWER(email) LIKE LOWER('fred@%'); // starts with
select * from members.email where LOWER(email) LIKE LOWER('%@gmail.com'); //ends with

Start by tracing the NQL-to-Mongo and Mongo-to-SQL transformations described in the issue, then review the linked mingo and Knex reference implementations. Confirm how the three operators and their negated forms should map to regex and SQL LIKE syntax, including escaping and case-insensitive matching; done means the syntax works consistently across both query representations.