Usage of TSVECTOR and to_tsquery to filter records in Sequelize

Question

I've been trying to get full search text to work for a while now without any success. The current documentation has this example:

[Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // match text search for strings 'fat' and 'rat' (PG only)

So I've built the following query:

Title.findAll({
      where: {
        keywords: {
          [Op.match]: Sequelize.fn('to_tsquery', 'test')
        }
      }
    })

And keywords is defined as a TSVECTOR field.

keywords: {
      type: DataTypes.TSVECTOR,
    },

It seems like it's generating the query properly, but I'm not getting the expected results. This is the query that it's being generated by Sequelize:

Executing (default): SELECT "id" FROM "Tests" AS "Test" WHERE "Test"."keywords" @@ to_tsquery('test');

And I know that there are multiple records in the database that have 'test' in their vector, such as the following one:

{
        "id": 3,
        "keywords": "'keyword' 'this' 'test' 'is' 'a'",
    }

so I'm unsure as to what's going on. What would be the proper way to search for matches based on a TSVECTOR field?

Answer 1

It's funny, but these days I am also working on the same thing and getting the same problem.

I think part of the solution is here (How to implement PostgresQL tsvector for full-text search using Sequelize?), but I haven't been able to get it to work yet.

If you find examples, I'm interested. Otherwise as soon as I find the solution that works 100% I will update this answer.

What I also notice is when I add data (seeds) from sequelize, it doesn't add the lexemes number after the data of the field in question. Do you have the same behavior ?

last thing, did you create the index ?

CREATE INDEX tsv_idx ON data USING gin(column);