0

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
cybersam
  • 63,203
  • 6
  • 53
  • 76
abbood
  • 23,101
  • 16
  • 132
  • 246

1 Answers1

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
Charchit Kapoor
  • 8,934
  • 2
  • 8
  • 24
  • 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 – abbood May 31 '23 at 11:13
  • 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. @abbood – Charchit Kapoor May 31 '23 at 11:34
  • 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](https://stackoverflow.com/questions/45212213/is-there-any-way-in-neo4j-using-contains-to-compare-case-insensitive-string#comment84321912_45222229) the index and thus make the above search prohibitively slow.. – abbood May 31 '23 at 12:06
  • 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 – abbood May 31 '23 at 12:15
  • 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. – Charchit Kapoor May 31 '23 at 12:32
  • What is the speed of this query `call db.index.fulltext.queryRelationships(“”, “CEO”) yield relationship return reltionship.title limit 10` without limit? – Charchit Kapoor May 31 '23 at 12:34