# Full text search on nodes that have existing relationships efficiently

**URL:** <https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476>\
**Category:** Cypher\
**Created:** [June 23, 2020, 11:55am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476 "2020-06-23T11:55:46Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 23, 2020, 11:55am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/1 "2020-06-23T11:55:46Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 23, 2020, 3:45pm UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/2 "2020-06-23T15:45:47Z")

</div>

Hello @tarendran.vivekanand 🙂

Can you provide the unefficient Cypher query?

Regards,  
Cobra

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 24, 2020, 1:51am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/3 "2020-06-24T01:51:33Z")

</div>

Hello @Cobra 🙂 . Sorry should have provided it.

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 24, 2020, 9:26am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/4 "2020-06-24T09:26:53Z")

</div>

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

Moreover you can do this:

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

```

Regards,  
Cobra

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 5:41am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/5 "2020-06-25T05:41:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 7:45am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/6 "2020-06-25T07:45:25Z")

</div>

Hello @tarendran.vivekanand 🙂

The cypher request I showed you works on my database 🤔

 ![image](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/b/bddd645313b1f9032531a2dc15e2fe088725d024.png)

Which version of Neo4j are you using?

Regards,  
Cobra

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:06am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/7 "2020-06-25T08:06:26Z")

</div>

Hello @Cobra :) 😃  
Im using the latest one Neo4j 4.0  
When I use the match on the outside  
 ![Screenshot from 2020-06-25 16-09-20](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/c/ced49c330c42d4ba55a2f00682b0b97b62a273b1.png)

```auto
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

 ![WHERE_EXISTS](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/d/d9244b51da85ae30684989fc2ad4fcb8a2a38783.png)

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

```

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 8:10am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/8 "2020-06-25T08:10:02Z")

</div>

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 😕

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

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:13am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/9 "2020-06-25T08:13:44Z")

</div>

I just edited my comment with my query and results

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 8:16am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/10 "2020-06-25T08:16:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:19am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/11 "2020-06-25T08:19:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 8:19am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/12 "2020-06-25T08:19:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:31am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/13 "2020-06-25T08:31:28Z")

</div>

I put the queries I made in the attach .txt file  
[Query.txt](https://community.neo4j.com/uploads/short-url/fF9nRyLdAQhBmkvnsati4yEolKR.txt) (1.4 KB)

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 8:34am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/14 "2020-06-25T08:34:44Z")

</div>

You must use the `node` return by the `db.index.fulltext.queryNodes` in the `WHERE` clause 🙂

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

```

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:40am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/15 "2020-06-25T08:40:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [June 25, 2020, 8:43am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/16 "2020-06-25T08:43:18Z")

</div>

```auto
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

```

---

<div class="post-metadata">

**Author:** ![tarendran.vivekanand](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tarendran.vivekanand/32/3909_2.png) [@tarendran.vivekanand](https://community.neo4j.com/u/tarendran.vivekanand)\
**Post date:** [June 25, 2020, 8:45am UTC](https://community.neo4j.com/t/full-text-search-on-nodes-that-have-existing-relationships-efficiently/20476/17 "2020-06-25T08:45:27Z")

</div>

@Cobra 💖 💖 💖 💖 💖 💖 💖 💖 💖 💖 💖 💖 💖