Returning extra null line

Neo4j Graph Platform Cypher
1

I am returning an extra line whereby the result is null and I dont know what is causing it. I have attached my query and result below. Thanks in advance.

CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
OPTIONAL MATCH (group:Group)-[:GROUPS]->(asset:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
WHERE asset.asset_id = node.asset_id
WITH DISTINCT collect(d{.sigfox_id,asset,group}) as assetdata,node
OPTIONAL MATCH (gg:Group)-[:GROUPS]->(ga:Asset)<-[:ON]-(:Deployment)<-[:SCHEDULED]-(gd:Device)
WHERE gg.group_id = node.group_id
RETURN DISTINCT gg as groupdata, assetdata

return
return784×373 51.6 KB

2

Hello @tarendran.vivekanand :slight_smile:

It's because of the OPTIONAL MATCH, replace it by MATCH :slight_smile:

Regards,
Cobra

3

Hello @cobra :slight_smile: ,
I dont get anything returned if I use MATCH only

4

What is the aim of your request?

Can you show a quick example about what you would like to get?

5

Basically i need 2 list that is returned from the search (1. assets 2. asset groups) but in the asset list I require the device and the asset group properties returned also

6

Why don't use node in the MATCH?

CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
OPTIONAL MATCH (group:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
WITH DISTINCT collect(d{.sigfox_id, node, group}) as assetdata, node
OPTIONAL MATCH (node)-[:GROUPS]->(ga:Asset)<-[:ON]-(:Deployment)<-[:SCHEDULED]-(gd:Device)
RETURN DISTINCT node, assetdata

Did you check if there are GROUPS relations or Group nodes?

You can also use the properties() function to get all the properties of a node or a relation :slight_smile:

1 Like
8

Hello @tarendran.vivekanand :slight_smile:

You want to search on Asset and Group nodes right?
What is not right on the result of my request?

Regards,
Cobra

9

:slight_smile:
Yes, I want to search Asset and Group nodes only. However my results show that I am getting extra results. As you can see in the above images, when I run my old query I only get 2 results, however, I am getting more results when I run yours.
Based on the results I get, it seems like it only matches the first part for assets and completely ignores the matching for groups and thus returns other groups that are not even connected to devices. I am not sure why this is the cause.

10

Did you already try UNION?

CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
OPTIONAL MATCH (group:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
RETURN DISTINCT collect(d{.sigfox_id, node, group}) AS assetdata, node
UNION ALL
CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
OPTIONAL MATCH (node)-[:GROUPS]->(ga:Asset)<-[:ON]-(:Deployment)<-[:SCHEDULED]-(gd:Device)
RETURN DISTINCT collect(d{.sigfox_id, ga, node}) AS assetdata, node
12

What is it returning by the last query? And what is not correct in the result? :slight_smile:

14

What should be returned for Group ?

15

Like how it is shown in the picture above when I use the old query. It should only return 1 asset and 1 group.

16

Why don't you returned directly each type like this: Group, Asset, Device?*
The OPTIONAL MATCH will return all nodes even if they didn't match all relationships :slight_smile:

17

Because i'm only interested for searching those assets and groups that have a device attached to it, the rest are super useless. Is there a way to delete the null row, I feel like that it would be faster this way? :slight_smile:

18

So in that case you must use MATCH instead of OPTIONAL MATCH and EXISTS in a WHERE clause to check if they are linked to a Device :slight_smile:

19

The problem with that, is sometimes what is searched will only return Asset or only Group. So cant use MATCH :frowning:

20

You could use UNION ALL in that case, first you return all Asset nodes and after all Group nodes :slight_smile:

21

Not sure how that will look like. Sorry still fairly new to neo4j :slightly_frowning_face:

22

No problem, we are here to help :slight_smile:

CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
MATCH (g:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
RETURN DISTINCT collect(d{.sigfox_id, node, g}) AS assetdata, node
UNION ALL
CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
MATCH (node)-[:GROUPS]->(a:Asset)<-[:ON]-(:Deployment)<-[:SCHEDULED]-(d:Device)
RETURN DISTINCT collect(d{.sigfox_id, a, node}) AS assetdata, node

This request has two parts:

  • the first will return all Asset nodes
  • the second will return all Group nodes

You need to use MATCH clause to make sure there is a path between Asset and Device for the first part and Group and Device for the second part :slight_smile:

24 
CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
MATCH (g:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
RETURN DISTINCT collect({device:d.sigfox_id, asset:node, group:g}) AS assetdata, node
UNION
CALL db.index.fulltext.queryNodes('livesearch', '*t*')
YIELD node
MATCH (node)-[:GROUPS]->(a:Asset)<-[:ON]-(:Deployment)<-[:SCHEDULED]-(d:Device)
RETURN DISTINCT collect({device:d.sigfox_id, asset:a, group:node}) AS assetdata, node