Full text search on nodes that have existing relationships efficiently

I want to search nodes that only have a specific relationship efficiently.
Example

CREATE (a:Asset {asset_name:'John', asset_id:'001'})
CREATE (as:Asset {asset_name:'John', asset_id:'002'})
CREATE (g:Group {group_name:'local_employee'})
MERGE (g)-[:GROUP]-(as)

call db.index.fulltext.createNodeIndex('search', ['Asset'], ['asset_name'], {analyzer:'standard-no-stop-words'})

call db.index.fulltext.queryNodes('search', 'john')

However this will return both nodes. I just want to return the one asset node that has the relationship.
I understand that you can just add a match statement at the bottom and then return the results. However this method is inefficient when the nodes I have to search through are in the tens of thousands.
Basically I require a more efficient method.

Hello @tarendran.vivekanand :slight_smile:

Can you provide the unefficient Cypher query?

Regards,
Cobra

Hello @Cobra :slight_smile: . Sorry should have provided it.

WITH ("*" +$usersearchtext+ "*") as txt
WITH split(txt,' ') as stxt
WITH apoc.text.join(stxt, '* AND *') as jtxt
CALL db.index.fulltext.queryNodes('livesearch', jtxt)
YIELD node
MATCH (g:Group)-[:GROUP]->(a:Asset)-[:OWNS]->(d:Device)
WHERE a.asset_name = node.asset_name
RETURN (a)

First, I think you can remove all the WITH clauses and build your string directly in the CALL :slight_smile:

Moreover you can do this:

CALL db.index.fulltext.queryNodes('search', 'john')
YIELD node WHERE exists((node)<-[:GROUP]-(:Group))
RETURN node

Regards,
Cobra

I tried doing what you said however it didnt work. It still returned all without filtering. Only if I put the match then it will filter.

Hello @tarendran.vivekanand :slight_smile:

The cypher request I showed you works on my database :thinking:
image

Which version of Neo4j are you using?

Regards,
Cobra

Hello @Cobra :) :smiley:
Im using the latest one Neo4j 4.0
When I use the match on the outside
Screenshot from 2020-06-25 16-09-20

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node
OPTIONAL MATCH (g:Group)-[:GROUPS]->(a:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
WHERE a.asset_id = node.asset_id
WITH DISTINCT collect(d{.sigfox_id,a}) as assetdata

When I use yours

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node WHERE EXISTS ((:Group)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
return node

I'm using the version 4.0.4 of Neo4j, otherwise I don't know why it works on my database and not on yours :confused:

The WHERE clause checks if there is a relationship linked to the node or not.

I just edited my comment with my query and results

@tarendran.vivekanand can you try to return only the node id on my cypher request ?

Still doesnt give me the same result as my query. @Cobra :frowning:

Can you give me all the queries to build the same database as yours?

I put the queries I made in the attach .txt file
Query.txt (1.4 KB)

You must use the node return by the db.index.fulltext.queryNodes in the WHERE clause :slight_smile:

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node WHERE EXISTS ((:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
return node

Then what if I want to return all so the asset,group and device?

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node
WHERE EXISTS ((:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
OR EXISTS ((node)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
OR EXISTS ((:Group)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(node))
RETURN node
3 Likes

@Cobra :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart: :sparkling_heart:

1 Like