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
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:[email protected]
- 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