Cypher Neo4J - CASE Expression with MERGE

Question

I'm trying to implement the logic in Cypher where, based on a particular condition (CASE Statement), I would create some nodes and relationships; the code is as below

MATCH (g:Game)-[:PLAYER]->(u:User)-[r1:AT]->(b1:Block)-[:NEXT]->(b2:Block) 
WHERE g.game_id='G222' and u.email_id = '[email protected]' and b1.block_id='16' 
SET r1.status='Skipped', r1.enddate=20141225
WITH u, b2,b1, g, r1
SET b1.test = CASE b2.fork 
WHEN 'y' THEN
     MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2     {fork:'fail'}) RETURN 1
ELSE 
     MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2)   RETURN 2
END
WITH u, g
MATCH (u)-[:TIME]->(h:Time)<-[:TIME]-(g)
SET h.after = 0
SET h.before = h.before + 1

In this query there is a merge statement within the WHEN 'y' THEN, this query throws an error:

Invalid input ']': expected whitespace or a relationship pattern (line 7, column 82) "MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2 {fork:'fail'}) RETURN 1"

Basically I'm trying to create a relationship based on a property i.e. a MERGE within a CASE statement, I tried different ways to get this working like doing a return so that case when returns some value etc. but nothing worked so far.

What could be the issue with this query?

Answer 1

To do conditional write operations you need to use the FOREACH trick. Using CASE you either return a one element array or a empty one. FOREACH iterates over the CASE expression and therefore conditionally executes the action. If you want an ELSE part as well you need to have a another FOREACH using the inverse condition in the CASE. As an example, instead of

WHEN 'y' THEN
   MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2     {fork:'fail'}) RETURN 1
ELSE 
    MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2)   RETURN 2
END

use

FOREACH(ignoreMe IN CASE WHEN 'y' THEN [1] ELSE [] END | 
    MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2 {fork:'fail'})
)
FOREACH(ignoreMe IN CASE WHEN NOT 'y' THEN [1] ELSE [] END | 
    MERGE (u)-[r2:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2)
)

See also Mark's blog post on this.

Answer 2

Fixed the issue as below

WITH u, b2,b1, g, r1, CASE  WHEN (b1.fork='y' and b2.fork='success') or (b1.fork='n') or   (b1.fork='success') THEN ['ok'] ELSE [] END as array1
FOREACH (el1 in array1 | MERGE (u)-[r2:STAGE {startdate:20141225, enddate:99999999, status:'InProgress'}]->(b2))

i.e. used CASE WHEN to create a dummy array that in a way has dummy elements matching the count of matches and then use FOREACH to iterate through the result.

Again, thanks Stefan for the idea...

Deepesh

Answer 3

The APOC plugin supports Conditional Cypher Execution, which now allows us to avoid the FOREACH workaround.

For example, you can do this:

MATCH (g:Game)-[:PLAYER]->(u:User)-[r1:AT]->(b1:Block)-[:NEXT]->(b2:Block) 
WHERE g.game_id='G222' AND u.email_id = '[email protected]' AND b1.block_id='16' 
SET r1.status='Skipped', r1.enddate=20141225
WITH u, b2, g
CALL apoc.do.when(
  b2.fork = 'y',
  "MERGE (u)-[:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2     {fork:'fail'})",
  "MERGE (u)-[:STAGE {startdate:20141225, enddate:'99999999', status:'InProgress'}]->(b2)",
  {u: u, b2: b2}) YIELD value
WITH u, g
MATCH (u)-[:TIME]->(h:Time)<-[:TIME]-(g)
SET h.after = 0
SET h.before = h.before + 1

Answer 4

Although this answer does help me, I found the syntax very hard to understand. So that's why I wrote my own answer. Here I read a tsv file and generate multiple types of edges.

LOAD CSV WITH HEADERS FROM 'file:///data.tsv' AS r FIELDTERMINATOR '\t'
WITH r.movie_id as movie_id, r.person_id as person_id, r.category as category
MATCH (p:Person {person_id:person_id})
MATCH (m:Movie {movie_id:movie_id})
FOREACH (_ IN CASE WHEN category='actress' THEN [1] ELSE [] END |
  MERGE (p)-[:ACTRESS {}]->(m)
)
FOREACH (_ IN CASE WHEN category='director' THEN [1] ELSE [] END |
  MERGE (p)-[:DIRECTOR {}]->(m)
)    
FOREACH (_ IN CASE WHEN category='cinematographer' THEN [1] ELSE [] END |
  MERGE (p)-[:CINEMATOGRAPHER {}]->(m)
)
FOREACH (_ IN CASE WHEN category='actor' THEN [1] ELSE [] END |
  MERGE (p)-[:ACTOR {}]->(m)
)

Here _ is some variable which is simply not used anywhere but a necessity for the syntax of cypher