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 = 'xyz@example.com' 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?

Stefan Armbruster · Accepted Answer · 2014-12-20T12:34:01.990

27

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.

edited Dec 20 '14 at 12:34

answered Dec 20 '14 at 09:21

Stefan Armbruster

39,465
6
87
97

1

Thanks Stefan, yes you are correct, it seems we need to use FOREACH, I also referred to one of your earlier posts in StackOverflow, the logic finally is like this 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)) – deepesh Dec 20 '14 at 10:20
1

Nice trick, but can you explain what is the meaning of `THEN [1] ELSE []`, and what is `ignoreMe` *(sorry, I couldn't ignore you)*? – ADTC May 17 '16 at 03:37
1

No update in Cypher allowing us to forget about this hack yet? I would really like to read that this response is outdated now in 2018. – HerrIvan Oct 17 '18 at 14:24
@HerrIvan is this still the only way to conditionally merge? – bigmadwolf Nov 03 '19 at 16:23
@bigmadwolf: Yep. That was my question one year ago. But no answer since then... Maybe Stefan Armbruster knows better. – HerrIvan Nov 05 '19 at 08:04
this works but it is a horrible hack for such a common use case "if I can find this node, join it to this other node" – Michael Dausmann Mar 26 '21 at 02:23

score 3 · Answer 2 · answered Jun 04 '20 at 18:58

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 = 'xyz@example.com' 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

score 2 · Answer 3 · answered Dec 20 '14 at 10:24

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

it would be neater if you don't put your long business logic inside CQL. — canbax, Apr 17 '20 at 14:36

score 0 · Answer 4 · answered Apr 17 '20 at 15:33

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

Cypher Neo4J - CASE Expression with MERGE

4 Answers4

Linked