Returning extra null line

[tarendran_vivek]

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

[Cobra]

Hello @tarendran.vivekanand

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

Regards,
Cobra

[tarendran_vivek]

Hello @Cobra ,
I dont get anything returned if I use MATCH only

[Cobra]

What is the aim of your request?

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

[tarendran_vivek]

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

[Cobra]

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

[Cobra]

Hello @tarendran.vivekanand

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

Regards,
Cobra

[tarendran_vivek]

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.

[Cobra]

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

[Cobra]

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

[Cobra]

What should be returned for Group ?

[tarendran_vivek]

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

[Cobra]

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

[tarendran_vivek]

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?

[Cobra]

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

[tarendran_vivek]

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

[Cobra]

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

[tarendran_vivek]

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

[Cobra]

No problem, we are here to help

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

[Cobra]

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

[tarendran_vivek]

Sorry 1 more question is it possible to put it all in one list?

[Cobra]

You mean the result of both part?

[tarendran_vivek]

Yea basically merge the result into one?

[Cobra]

What do you get currently?

[tarendran_vivek]

Right now it is two separate, so is it possible to use some map projection.

[Cobra]

Show me a screenshot of what you have at the moment and write a short example of what you expect please

[Cobra]

You have already the node in assetdata so you can remove the node in the return?

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

Otherwise you can get a list of dict like this:

CALL {
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
} RETURN collect({assetdata: assetdata, node: node})