Using case statement

[ri8ika]

Let's say we have Post node on which test property exists with the value match. So,

// when searching for 'match'
MATCH(n:Post)
WHERE n.test = 'match'
RETURN n.test AS Match

Result:

Match
-----------------------------
'match'
'match'
'match'

// when searching for 'nomatch'
MATCH(n:Post)
WHERE n.test = 'nomatch'
RETURN n.test AS Match

Result:

(No changes, no records)

Now, what I want get result is like this:

1. When string matches in that property value, then return last match:

Match
-----------------------------
'match'

2. When string doesn't match in that property value, then return string:

Match
-----------------------------
'nomatch'

Instead of (No changes, no records)

How can we achieve this? I have tried several hours to achieve this. Please help me.

What I have fiddled out like this for more hours, but not getting the concept how to do this:

MATCH (n:Post)
  WHERE n.test = 'match'
  RETURN n.test
  CASE n.test
    WHEN ...
  END
  ORDER BY n.created_at DESC
  LIMIT 1

I hope you understand my question.

[andrew_bowman]

Something like this should work:

MATCH (n:Post)
WHERE n.test = 'match' 
WITH n
ORDER BY n.created_at DESC
LIMIT 1
WITH collect(n) as nodesMatching
RETURN coalesce(nodesMatching[0].test, 'nomatch') as result

The key here is that when there are no matches found and thus no rows, when we do a collect(), it will emit a row with an empty collection, so we can continue operations on that row from that point (otherwise we have no row upon which to execute subsequent operations). So the collection will either contain a single node (from the above LIMIT) or it will be an empty list. We use coalesce() to supply a default value of 'nomatch' if nodesMatching[0].test is null (which it will be if the list is empty).

[ri8ika]

Sorry, but I have another question. (Thanks for your reply)

I'm using starts with command to match

WHERE n.test STARTS WITH 'match'

Which can result: match, match2, match3, etc.

Now, if result matches. If it's match, the result should be match2. If it's match5, the result should be match6.

And if no matches, return match So, how can we replace the result?

I guess, it should be added after the following line?

WITH collect(n) as nodesMatching

Thanks again.

---

Something like this?

replace(coalesce(nodesMatching[0].test, 'match'), /(\d+)$/, /(\d+)$/+1) as result

I know I'm doing it incorrectly. Please help on this. Also, I'm pretty sure that I'm using replace function in wrong line. Because, if I use in the preceding line, I have set 'match' as a default value. So, if no match, the result will be match2 if we do replace in that line which is not expected behavior. The reason is there's no match, we're just setting a default value 'match' to return as a result and the replace function will return it as match2. In this case, it should return match.