CODEWITHSUNDEEP
neo4jcyphercase-insensitive
Triet Doan
Triet Doan

Reputation: 12085

How to use case-insensitive match in MERGE clause?

I have a question as specified in the title. For example, here is my current query:

MERGE (n:Person { name: 'John' })
ON CREATE SET n.age = 30
ON MATCH SET n.age = 30

I want to have a case-insensitive search. So, the name maybe john, JOHN, joHN, doesn't matter, it should still match.

As far as I know, we can achieve case-insensitive search by using regular expression in the WHERE clause. For example:

MATCH (n:Person)
WHERE n.name =~ '(?i)John'

However, MERGE clause doesn't support WHERE clause. It would be invalid to say:

MERGE (n:Person)
WHERE n.name =~ '(?i)John'
ON CREATE SET n.age = 30
ON MATCH SET n.age = 30

So, what is the solution in this case? One suggestion I found uses OPTIONAL MATCH. The query will be:

OPTIONAL MATCH (n:Person) WHERE n.name =~ '(?i)John'
WITH n
WHERE n IS NOT NULL SET n.age = 30

OPTIONAL MATCH (n:Person) WHERE n.name =~ '(?i)John'
WITH n
WHERE n IS NULL MERGE (n:Person { name: 'John', age: 30 })

What do you think about this solution? Do you have any suggestion? Thank you so much for your help.

Upvotes: 0

Views: 417

Answers (1)

cybersam
cybersam

Reputation: 66967

[EDITED]

Option 1.

If it is OK to add a new property to each Person node that contains the name in a consistent case (say, uppercase), there is a workaround.

The workaround would require that you first add the new property to all Person nodes. For example:

MATCH (p:Person)
SET p.uName = TOUPPER(p.name);

After the new property is added, you can use MERGE directly. In the following query, I assume that the age and the name you are looking for are passed to the query as the parameters $age and $name.

MERGE (p:Person {uName: TOUPPER($name)})
ON CREATE SET p.name = $name
SET p.age = $age;

This query sets the name property if the node was just created. Also, since you always want to set the age, this query always does that.

Option 2.

If you do not want to add a new property, you can use the APOC procedure apoc.do.when to optionally create the Person node and get back the optionally created node (or the existing node) to do further processing on it. For example:

OPTIONAL MATCH (n:Person)
WHERE n.name =~ ('(?i)' + $name)
CALL apoc.do.when(
  n IS NULL,
  'CREATE (p:Person {name: $name}) RETURN p',
  'RETURN $existing AS p',
  {name:$name, existing: n}) YIELD value
WITH value.p AS p
SET p.age = $age
RETURN p;

Upvotes: 1

Related Questions