Cypher Neo4J - CASE Expression with MERGE
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?
Solution 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.
Solution 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