neo4j driver UNION not working

[mohit_geeky]

I have this Query

match p=(n:ASSET{uniqueID: "UNIQUE_ID"})<-[TRANSFORMS_INTO*]-()-[:HAS_EVENT]->() unwind nodes(p) as asset unwind relationships(p) as relationsp optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT) unwind relationships(p2) as relationsp2 return collect(distinct asset)+collect(DISTINCT e) as distinctNodes, collect(DISTINCT relationsp)+collect(DISTINCT relationsp2) as distinctRelationships
union
match p=(n:ASSET{uniqueID: "UNIQUE_ID"})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT]->() unwind nodes(p) as asset unwind relationships(p) as relationsp optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT) unwind relationships(p2) as relationsp2 return collect(distinct asset)+collect(DISTINCT e) as distinctNodes, collect(DISTINCT relationsp)+collect(DISTINCT relationsp2) as distinctRelationships


This query is working fine in neo4j desktop
but 
UNION not working in neo4j driver in js

I am getting only result of query that is written before UNION

[glilienfield]

Can you post the js code?

[mohit_geeky]

this.queryResponseFilter = ' unwind nodes(p) as asset' +

' unwind relationships(p) as relationsp' +

' optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT)' +

' unwind relationships(p2) as relationsp2' +

' return collect(distinct asset)+collect(DISTINCT e) as distinctNodes, collect(DISTINCT relationsp)+collect(DISTINCT relationsp2) as distinctRelationships'

this.queryStrBack = 'match p=(n:ASSET{uniqueID: $uniqueID})<-[TRANSFORMS_INTO*]-()-[:HAS_EVENT]->()' + this.queryResponseFilter

this.queryStrForward = 'match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT]->()' + this.queryResponseFilter

this.finalQueryForward = this.queryStrForward + ' UNION ' + this.queryStrBack

let result = await session.run( this.finalQueryForward , { uniqueID }, {timeout:0});


here result i am only getting from query before UNION

here is the response
{
records: [
Record {
keys: [Array],
length: 2,
_fields: [Array],
_fieldLookup: [Object]
},
Record {
keys: [Array],
length: 2,
_fields: [Array],
_fieldLookup: [Object]
}
],
summary: ResultSummary {
query: {
text: 'match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT]->() unwind nodes(p) as asset unwind relationships(p) as relationsp optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT) unwind relationships(p2) as relationsp2 return collect(distinct asset)+collect(DISTINCT e) as distinctNodes, collect(DISTINCT relationsp)+collect(DISTINCT relationsp2) as distinctRelationships UNION match p=(n:ASSET{uniqueID: $uniqueID})<-[TRANSFORMS_INTO*]-()-[:HAS_EVENT]->() unwind nodes(p) as asset unwind relationships(p) as relationsp optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT) unwind relationships(p2) as relationsp2 return collect(distinct asset)+collect(DISTINCT e) as distinctNodes, collect(DISTINCT relationsp)+collect(DISTINCT relationsp2) as distinctRelationships',
parameters: [Object]
},
queryType: 'r',
counters: QueryStatistics { _stats: [Object], _systemUpdates: 0 },
updateStatistics: QueryStatistics { _stats: [Object], _systemUpdates: 0 },
plan: false,
profile: false,
notifications: [],
server: ServerInfo {
address: 'localhost:7687',
agent: 'Neo4j/5.2.0',
protocolVersion: 5
},
resultConsumedAfter: Integer { low: 9, high: 0 },
resultAvailableAfter: Integer { low: 59, high: 0 },
database: { name: 'neo4j' }
}
}



when i try to hit in neo4j desktop
its working fine
but from code only getting response query written before UNION

[glilienfield]

Sorry, I don't see an obvious reason it is not working.  I would switch the order of the 'union' and see if it is still the second result missing, or now it is the first result missing. 

Just a note, when you use the following match, as you did in your query, I believe you will miss some of the result. This query specifies the pattern ends on a node that has an incoming HAS_EVENT relationship. I recall from the diagram in your other issue that not all ASSET nodes will have a HAS_EVENT related to an Event node. As such, those paths will not match; thereby, missing the nodes unique to those left out paths. 

match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT]->()

You could either, use the following match:

match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()

and the include the optional match to get the events (as you did):

 optional match p2=(asset)-[:HAS_EVENT]->(e:EVENT) 

Using two match clauses, requires you to collect and nodes/relationships for both paths and add them. An alternative is to use one path that has a HAS_EVENT relations with a length of zero or one. This will account for the paths that do not end with an Event node.

match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT*0..1]->()

If you do this, this one path will have the nodes and relationships for both the ASSETS and Events, so you don't have to use the optional match to get the Events. 

Even so, I believe using the apoc.path.subgraphAll procedure is much easier, as it will give you the collection of nodes and relationships along all that paths starting from your too node in one call. No need to do all the 'unwinding' to accumulate the nodes and relationships from all the individual paths. 

Just some thoughts. 

[glilienfield]

I think this would work the same:

match p=(n:ASSET{uniqueID: $uniqueID})-[TRANSFORMS_INTO*]->()-[:HAS_EVENT*0..1]->() 
unwind nodes(p) as node
unwind relationships(p) as relationship 
return collect(distinct node) as nodes, collect(distinct relationship) as relationships

UNION 

match p=(n:ASSET{uniqueID: $uniqueID})<-[TRANSFORMS_INTO*]-()-[:HAS_EVENT*0..1]->() 
unwind nodes(p) as node
unwind relationships(p) as relationship 
return collect(distinct node) as nodes, collect(distinct relationship) as relationships

[glilienfield]

By the way, when you 'double' unwind, you create a Cartesian product of the two lists. This can create a lot of extra rows and duplicate elements when your intent is to collect each. See the following as an example:

So, instead of getting the distinct list of elements from each list by doing a 'double' unwind like this:

with [1,2] as xx, [10,20] as yy
unwind xx as x
unwind yy as y
return collect(distinct x), collect(distinct y)

Perform the collections one at a time:

with [1,2] as xx, [10,20] as yy
unwind xx as x
with collect(distinct x) as xColl, yy
unwind yy as y
return xColl, collect(distinct y) as yColl

This will be much more efficient if the 'xx' and 'yy' lists are large, as it avoids creating the Cartesian product of the two lists, just to reduce them into two distinct lists. 

[anthapu]

Actually it would be much more efficient to leverage the apoc methods here. 

match p=(n:ASSET{uniqueID: $uniqueID})<-[TRANSFORMS_INTO*]-()-[:HAS_EVENT*0..1]->()
RETURN apoc.coll.toSet(nodes(p)) as nodes, apoc.coll.relationships(nodes(p)) as relationships

This reduces the amount of times we iterate through the list.