Postgres - partial text search

Postgres Partial Text Search

Given the three documents below, we want to find the animals with name like 'frank'

{ 'species': 'cat', 'name': 'frank' }
{ 'species': 'dog', 'name': 'frankie' }
{ 'species': 'snake', 'name': 'francine' }

If this were sql we would search name like %frank% we'd expect to get the first two results. Postgres & Massive gives us a way to search for equality in a document so:

db.animals.findDoc({name: 'frank'}, (err, res) => { // do things with results })

Would only return the first result. (Note that Massive creates an index for your jsonb document if you let it create the table).

To search for partial results we need to add a Trigram Index - this breaks up text into three character chunks and gives a weighting to the search term match, rather than giving us an exact match or nothing. For the example above, we would break out the name property into another column (Massive creates a 'search' column which you could populate, or just make your own) - at this point we lose some of the ability to use Massive findDoc functions, but can still use the where function.

Creating the record would now look like

db.saveDoc("animals", {body: jsonDocument, name: jsonDocument.name}, function(err,res){});

And to query:

db.animals.where("name like $1", ['%frank%''], (err, result) => { // do something with the result });