How to efficiently use a fulltext index to search based on prior MATCH results in same query

Question

Intro

This what an existing query of mine looks like:

MATCH
(p:Person { id: $p_id })-[k1:`KNOWS`]->(person:Person)
WHERE (// some criteria)
MATCH
(person)-[work:`WORKED_AT`]->(company:Company)
WHERE (work.title contains “Product Manager" and work.start_date is not null)
WITH person, work
RETURN
DISTINCT person.full_name, work.title

The problem in the above query is that it is not case insensitive. So the above query fails if the actual title is product manager in the database.

So we are trying to use full text search index. We created the index on the :work relationship, and we verified that it works, for example the search of:

call db.index.fulltext.queryRelationships(“<indexName>”, “CEO”) 
yield relationship return reltionship.title limit 10

would return stuff like:

CEO
Ceo
..etc

Question

How can I apply the above index search on the node search? For example:

MATCH
(p:Person { id: $p_id })-[k1:`KNOWS`]->(person:Person)
WHERE (// some criteria)
MATCH
(person)-[work:`WORKED_AT`]->(company:Company)
WHERE //apply the db.index.fulltext.queryRelationships here on :work somehow
WITH person, work
RETURN
DISTINCT person.full_name, work.title

Kalie

If you're correcting for case sensitivity, why not just use toLower(work.title) contains "product manager"? – Passionless

Answer 1

I guess you'll first have to fetch the relationships using the index and then apply the MATCH. Try this:

MATCH (p:Person { id: $p_id })-[k1:`KNOWS`]->(person:Person)
WHERE (// some criteria)
CALL db.index.fulltext.queryRelationships("indexName", "searchString") 
YIELD relationship AS work
MATCH (person)-[work]->(company:Company)
WITH person, work
RETURN DISTINCT person.full_name, work.title

Alternatively, you can perform a case-insensitive search using regex. Like this:

MATCH (p:Person { id: $p_id })-[k1:`KNOWS`]->(person:Person)
WHERE (// some criteria)
MATCH (person)-[work:`WORKED_AT`]->(company:Company)
WHERE (work.title =~ '(?i).*Product Manager.*' and work.start_date is not null)
WITH person, work
RETURN DISTINCT person.full_name, work.title

Diapause

I already tried that version, the problem is that it searches through all indexes which takes forever. I want it to only search through the relationships that exist in the original query – Kalie

Got it. I have updated the answer to perform a case-insensitive search using regex. I don't think we can restrict the number of relationships to be searched in the full-text index. A workaround needs to be created. @Kalie – Diapause

I thought of regex too. The problem is that we have millions of titles and so using the index on the title is very important. Using regex will invalidate the index and thus make the above search prohibitively slow.. – Kalie

actually your regex answer seems to work pretty fast, that's strange, does it really invalidate the index or not? it cannot be that fast if it's not using the index 🤔 – Kalie

The index will not be used as far as I know. You can confirm it by adding PROFILE in front of the query and checking the execution stats. FULLTEXT index is only used by the db.index.fulltext.query* functions. It's fast because the total number of relationships being fetched and checked is too small. – Diapause

What is the speed of this query call db.index.fulltext.queryRelationships(“<indexName>”, “CEO”) yield relationship return reltionship.title limit 10 without limit? – Diapause