Neo4j Cypher extend the query to return supplemental info

Question

There is the following Cypher query:

MATCH (root:Location) 
WHERE root.id IN $locationIds
WITH root 
OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location) 
OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root) 
WITH COLLECT(root.id) AS listRoot, COLLECT(DISTINCT ascendant.id) AS listAscendant, COLLECT(DISTINCT descendant.id) AS listDescendant 
WITH listDescendant + listRoot + listAscendant AS dadLocationIds
WITH dadLocationIds 
WITH apoc.coll.intersection(dadLocationIds, [60, 58]) as output
UNWIND output AS locationId
RETURN locationId

Is it possible to extend this query in order to not only return the intersected locationId, but also for each locationId return the corresponding ID from $locationIds and also the mark(how it was matched) - direct, ascendant, descendant.

For example:

locationId | id from $locationIds | mark
-------------------------------------------------
1          | 12                   | ascendant
5          | 4                    | descendant
8          | 8                    | direct

UPDATED

I test the query provided in the answer.

As an input I use the following Locations:

╒══════╤════════╕
│"l.id"│"l.name"│
╞══════╪════════╡
│110   │"USA"   │
├──────┼────────┤
│111   │"Europe"│
├──────┼────────┤
│112   │"Poland"│
├──────┼────────┤
│113   │"Warsaw"│
└──────┴────────┘

were there is the following hierarchy:

USA
Europe->Poland->Warsaw

I execute the following query:

MATCH (root:Location) 
WHERE root.id IN[111,112]
WITH root 
OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location) 
OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root) 
WITH COLLECT({id: root.id, rootId: root.id, mark: 'direct'}) AS listRoot, COLLECT(DISTINCT {id: ascendant.id, rootId: root.id, mark: 'ascendant'}) AS listAscendant, COLLECT(DISTINCT {id: descendant.id, rootId: root.id, mark: 'descendant'}) AS listDescendant 
WITH listDescendant + listRoot + listAscendant AS dadLocationIds
WITH [locId in dadLocationIds where locId.id in [110, 112] | locId]  as output
UNWIND output as out  
WITH DISTINCT out.id AS locationId, out.rootId as rootId, out.mark as mark
RETURN locationId, rootId, mark

and the result is:

╒════════════╤════════╤════════════╕
│"locationId"│"rootId"│"mark"      │
╞════════════╪════════╪════════════╡
│112         │111     │"descendant"│
├────────────┼────────┼────────────┤
│112         │112     │"descendant"│
├────────────┼────────┼────────────┤
│112         │112     │"direct"    │
├────────────┼────────┼────────────┤
│112         │112     │"ascendant" │
└────────────┴────────┴────────────┘

looks like something wrong for 112:112

user_7371893 · Answer

This is the query that will work for you.

MATCH (root:Location) 
WHERE root.id IN $locationIds
WITH root 
OPTIONAL MATCH (root)-[:CONTAINS*1..]->(descendant:Location) 
OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*1..]->(root) 
// create a map of rootId, ascendant and descendant ids and marking
WITH COLLECT({id: root.id, rootId: root.id, mark: 'direct'}) AS listRoot, COLLECT(DISTINCT {id: ascendant.id, rootId: root.id, mark: 'ascendant'}) AS listAscendant, COLLECT(DISTINCT {id: descendant.id, rootId: root.id, mark: 'descendant'}) AS listDescendant 
WITH listDescendant + listRoot + listAscendant AS dadLocationIds
// filter dadlocationids instead of using APOC intersection
WITH [locId in dadLocationIds where locId.id in [60, 58] | locId]  as output
// unwind each record
UNWIND output as out 
// parse the mapping per column  
WITH out.id AS locationId, out.rootId as rootId, out.mark as mark
RETURN locationId, rootId, mark

Below is my sample output:

╒════════════╤════════╤════════════╕
│"locationId"│"rootId"│"mark"      │
╞════════════╪════════╪════════════╡
│1           │8       │"ascendant" │
├────────────┼────────┼────────────┤
│2           │9       │"descendant"│
├────────────┼────────┼────────────┤
│7           │7       │"root"      │
└────────────┴────────┴────────────┘

Neo4j Cypher extend the query to return supplemental info

1 Answers