12.2. Merge

Introduction

MERGE either matches existing nodes and binds them, or it creates new data and binds that. It’s like a combination of MATCH and CREATE that additionally allows you to specify what happens if the data was matched or created.

For example, you can specify that the graph must contain a node for a user with a certain name. If there isn’t a node with the correct name, a new node will be created and its name property set.

When using MERGE on full patterns, the behavior is that either the whole pattern matches, or the whole pattern is created. MERGE will not partially use existing patterns — it’s all or nothing. If partial matches are needed, this can be accomplished by splitting a pattern up into multiple MERGE clauses.

As with MATCH, MERGE can match multiple occurrences of a pattern. If there are multiple matches, they will all be passed on to later stages of the query.

The last part of MERGE is the ON CREATE and ON MATCH. These allow a query to express additional changes to the properties of a node or relationship, depending on if the element was MATCHed in the database or if it was CREATEd.

The rule planner (see Section 15.1, “How are queries executed?”) expands a MERGE pattern from the end point that has the variable with the lowest lexicographical order. This means that it might choose a suboptimal expansion path, expanding from a node with a higher degree. The pattern MERGE (a:A)-[:R]->(b:B) will always expand from a to b, so if it is known that b nodes are a better choice for start point, renaming variables could improve performance.

The following graph is used for the examples below:

Figure 12.1. Graph

Merge nodes

Merge single node with a label

Merging a single node with a given label.

Query

MERGE (robert:Critic)
RETURN robert, labels(robert)

A new node is created because there are no nodes labeled Critic in the database.

Result

robert	labels(robert)
1 row
Nodes created: 1
Labels added: 1
`Node[7]{}`	`["Critic"]`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (robert:Critic) return robert, labels(robert)

Merge single node with properties

Merging a single node with properties where not all properties match any existing node.

Query

MERGE (charlie { name:'Charlie Sheen', age:10 })
RETURN charlie

A new node with the name Charlie Sheen will be created since not all properties matched the existing Charlie Sheen node.

Result

charlie
1 row
Nodes created: 1
Properties set: 2
`Node[7]{name:"Charlie Sheen",age:10}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (charlie {name:'Charlie Sheen', age:10}) return charlie

Merge single node specifying both label and property

Merging a single node with both label and property matching an existing node.

Query

MERGE (michael:Person { name:'Michael Douglas' })
RETURN michael.name, michael.bornIn

Michael Douglas will be matched and the name and bornIn properties returned.

Result

michael.name	michael.bornIn
1 row
`"Michael Douglas"`	`"New Jersey"`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (michael:Person {name:'Michael Douglas'}) return michael.name, michael.bornIn

Merge single node derived from an existing node property

For some property p in each bound node in a set of nodes, a single new node is created for each unique value for p.

Query

MATCH (person:Person)
MERGE (city:City { name: person.bornIn })
RETURN person.name, person.bornIn, city

Three nodes labeled City are created, each of which contains a name property with the value of New York, Ohio, and New Jersey, respectively. Note that even though the MATCH clause results in three bound nodes having the value New York for the bornIn property, only a single New York node (i.e. a City node with a name of New York) is created. As the New York node is not matched for the first bound node, it is created. However, the newly-created New York node is matched and bound for the second and third bound nodes.

Result

person.name	person.bornIn	city
5 rows
Nodes created: 3
Properties set: 3
Labels added: 3
`"Rob Reiner"`	`"New York"`	`Node[7]{name:"New York"}`
`"Oliver Stone"`	`"New York"`	`Node[7]{name:"New York"}`
`"Charlie Sheen"`	`"New York"`	`Node[7]{name:"New York"}`
`"Michael Douglas"`	`"New Jersey"`	`Node[8]{name:"New Jersey"}`
`"Martin Sheen"`	`"Ohio"`	`Node[9]{name:"Ohio"}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (person:Person) merge (city:City {name: person.bornIn}) return person.name, person.bornIn, city

Use ON CREATE and ON MATCH

Merge with ON CREATE

Merge a node and set properties if the node needs to be created.

Query

MERGE (keanu:Person { name:'Keanu Reeves' })
ON CREATE SET keanu.created = timestamp()
RETURN keanu.name, keanu.created

The query creates the keanu node and sets a timestamp on creation time.

Result

keanu.name	keanu.created
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`"Keanu Reeves"`	`1465371255976`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (keanu:Person {name:'Keanu Reeves'}) on create set keanu.created = timestamp() return keanu.name, keanu.created

Merge with ON MATCH

Merging nodes and setting properties on found nodes.

Query

MERGE (person:Person)
ON MATCH SET person.found = TRUE RETURN person.name, person.found

The query finds all the Person nodes, sets a property on them, and returns them.

Result

person.name	person.found
5 rows
Properties set: 5
`"Rob Reiner"`	`true`
`"Oliver Stone"`	`true`
`"Charlie Sheen"`	`true`
`"Michael Douglas"`	`true`
`"Martin Sheen"`	`true`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (person:Person) on match set person.found = true return person.name, person.found

Merge with ON CREATE and ON MATCH

Merge a node and set properties if the node needs to be created.

Query

MERGE (keanu:Person { name:'Keanu Reeves' })
ON CREATE SET keanu.created = timestamp()
ON MATCH SET keanu.lastSeen = timestamp()
RETURN keanu.name, keanu.created, keanu.lastSeen

The query creates the keanu node, and sets a timestamp on creation time. If keanu had already existed, a different property would have been set.

Result

keanu.name	keanu.created	keanu.lastSeen
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`"Keanu Reeves"`	`1465371258598`	`<null>`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (keanu:Person {name:'Keanu Reeves'}) on create set keanu.created = timestamp() on match set keanu.lastSeen = timestamp() return keanu.name, keanu.created, keanu.lastSeen

Merge with ON MATCH setting multiple properties

If multiple properties should be set, simply separate them with commas.

Query

MERGE (person:Person)
ON MATCH SET person.found = TRUE , person.lastAccessed = timestamp()
RETURN person.name, person.found, person.lastAccessed

Result

person.name	person.found	person.lastAccessed
5 rows
Properties set: 10
`"Rob Reiner"`	`true`	`1465371257672`
`"Oliver Stone"`	`true`	`1465371257672`
`"Charlie Sheen"`	`true`	`1465371257672`
`"Michael Douglas"`	`true`	`1465371257672`
`"Martin Sheen"`	`true`	`1465371257672`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (person:Person) on match set person.found = true, person.lastAccessed = timestamp() return person.name, person.found, person.lastAccessed

Merge relationships

Merge on a relationship

MERGE can be used to match or create a relationship.

Query

MATCH (charlie:Person { name:'Charlie Sheen' }),(wallStreet:Movie { title:'Wall Street' })
MERGE (charlie)-[r:ACTED_IN]->(wallStreet)
RETURN charlie.name, type(r), wallStreet.title

Charlie Sheen had already been marked as acting in Wall Street, so the existing relationship is found and returned. Note that in order to match or create a relationship when using MERGE, at least one bound node must be specified, which is done via the MATCH clause in the above example.

Result

charlie.name	type(r)	wallStreet.title
1 row
`"Charlie Sheen"`	`"ACTED_IN"`	`"Wall Street"`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (charlie:Person {name:'Charlie Sheen'}), (wallStreet:Movie {title:'Wall Street'}) merge (charlie)-[r:ACTED_IN]->(wallStreet) return charlie.name, type(r), wallStreet.title

Merge on multiple relationships

When MERGE is used on a whole pattern, either everything matches, or everything is created.

Query

MATCH (oliver:Person { name:'Oliver Stone' }),(reiner:Person { name:'Rob Reiner' })
MERGE (oliver)-[:DIRECTED]->(movie:Movie)<-[:ACTED_IN]-(reiner)
RETURN movie

In our example graph, Oliver Stone and Rob Reiner have never worked together. When we try to MERGE a movie between them, Neo4j will not use any of the existing movies already connected to either person. Instead, a new movie node is created.

Result

movie
1 row
Nodes created: 1
Relationships created: 2
Labels added: 1
`Node[7]{}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (oliver:Person {name:'Oliver Stone'}), (reiner:Person {name:'Rob Reiner'}) merge (oliver)-[:DIRECTED]->(movie:Movie)<-[:ACTED_IN]-(reiner) return movie

Merge on an undirected relationship

MERGE can also be used with an undirected relationship. When it needs to create a new one, it will pick a direction.

Query

MATCH (charlie:Person { name:'Charlie Sheen' }),(oliver:Person { name:'Oliver Stone' })
MERGE (charlie)-[r:KNOWS]-(oliver)
RETURN r

As Charlie Sheen and Oliver Stone do not know each other, this MERGE query will create a :KNOWS relationship between them. The direction of the created relationship is arbitrary.

Result

r
1 row
Relationships created: 1
`:KNOWS[8]{}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (charlie:Person {name:'Charlie Sheen'}), (oliver:Person {name:'Oliver Stone'}) merge (charlie)-[r:KNOWS]-(oliver) return r

Merge on a relationship between two existing nodes

MERGE can be used in conjunction with preceding MATCH and MERGE clauses to create a relationship between two bound nodes m and n, where m is returned by MATCH and n is created or matched by the earlier MERGE.

Query

MATCH (person:Person)
MERGE (city:City { name: person.bornIn })
MERGE (person)-[r:BORN_IN]->(city)
RETURN person.name, person.bornIn, city

This builds on the example from the section called “Merge single node derived from an existing node property”. The second MERGE creates a BORN_IN relationship between each person and a city corresponding to the value of the person’s bornIn property. Charlie Sheen, Rob Reiner and Oliver Stone all have a BORN_IN relationship to the same City node (New York).

Result

person.name	person.bornIn	city
5 rows
Nodes created: 3
Relationships created: 5
Properties set: 3
Labels added: 3
`"Rob Reiner"`	`"New York"`	`Node[7]{name:"New York"}`
`"Oliver Stone"`	`"New York"`	`Node[7]{name:"New York"}`
`"Charlie Sheen"`	`"New York"`	`Node[7]{name:"New York"}`
`"Michael Douglas"`	`"New Jersey"`	`Node[8]{name:"New Jersey"}`
`"Martin Sheen"`	`"Ohio"`	`Node[9]{name:"Ohio"}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (person:Person) merge (city:City {name: person.bornIn}) merge (person)-[r:BORN_IN]->(city) return person.name, person.bornIn, city

Merge on a relationship between an existing node and a merged node derived from a node property

MERGE can be used to simultaneously create both a new node n and a relationship between a bound node m and n.

Query

MATCH (person:Person)
MERGE (person)-[r:HAS_CHAUFFEUR]->(chauffeur:Chauffeur { name: person.chauffeurName })
RETURN person.name, person.chauffeurName, chauffeur

As MERGE found no matches — in our example graph, there are no nodes labeled with Chauffeur and no HAS_CHAUFFEUR relationships — MERGE creates five nodes labeled with Chauffeur, each of which contains a name property whose value corresponds to each matched Person node’s chauffeurName property value. MERGE also creates a HAS_CHAUFFEUR relationship between each Person node and the newly-created corresponding Chauffeur node. As Charlie Sheen and Michael Douglas both have a chauffeur with the same name — John Brown — a new node is created in each case, resulting in two Chauffeur nodes having a name of John Brown, correctly denoting the fact that even though the name property may be identical, these are two separate people. This is in contrast to the example shown above in the section called “Merge on a relationship between two existing nodes”, where we used the first MERGE to bind the City nodes to prevent them from being recreated (and thus duplicated) in the second MERGE.

Result

person.name	person.chauffeurName	chauffeur
5 rows
Nodes created: 5
Relationships created: 5
Properties set: 5
Labels added: 5
`"Rob Reiner"`	`"Ted Green"`	`Node[7]{name:"Ted Green"}`
`"Oliver Stone"`	`"Bill White"`	`Node[8]{name:"Bill White"}`
`"Charlie Sheen"`	`"John Brown"`	`Node[9]{name:"John Brown"}`
`"Michael Douglas"`	`"John Brown"`	`Node[10]{name:"John Brown"}`
`"Martin Sheen"`	`"Bob Brown"`	`Node[11]{name:"Bob Brown"}`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; match (person:Person) merge (person)-[r:HAS_CHAUFFEUR]->(chauffeur:Chauffeur {name: person.chauffeurName}) return person.name, person.chauffeurName, chauffeur

Using unique constraints with MERGE

Cypher prevents getting conflicting results from MERGE when using patterns that involve uniqueness constrains. In this case, there must be at most one node that matches that pattern.

For example, given two uniqueness constraints on :Person(id) and :Person(ssn): then a query such as MERGE (n:Person {id: 12, ssn: 437}) will fail, if there are two different nodes (one with id 12 and one with ssn 437) or if there is only one node with only one of the properties. In other words, there must be exactly one node that matches the pattern, or no matching nodes.

Note that the following examples assume the existence of uniqueness constraints that have been created using:

CREATE CONSTRAINT ON (n:Person) ASSERT n.name IS UNIQUE;
CREATE CONSTRAINT ON (n:Person) ASSERT n.role IS UNIQUE;

Merge using unique constraints creates a new node if no node is found

Merge using unique constraints creates a new node if no node is found.

Query

MERGE (laurence:Person { name: 'Laurence Fishburne' })
RETURN laurence.name

The query creates the laurence node. If laurence had already existed, MERGE would just match the existing node.

Result

laurence.name
1 row
Nodes created: 1
Properties set: 1
Labels added: 1
`"Laurence Fishburne"`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (laurence:Person {name: 'Laurence Fishburne'}) return laurence.name

Merge using unique constraints matches an existing node

Merge using unique constraints matches an existing node.

Query

MERGE (oliver:Person { name:'Oliver Stone' })
RETURN oliver.name, oliver.bornIn

The oliver node already exists, so MERGE just matches it.

Result

oliver.name	oliver.bornIn
1 row
`"Oliver Stone"`	`"New York"`

Try this query live create constraint on (n:`Person`) assert n.`name` is unique; create constraint on (n:`Person`) assert n.`role` is unique; create (_0:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_1:`Person` {`bornIn`:"New York", `chauffeurName`:"Ted Green", `name`:"Rob Reiner"}) create (_2:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_3:`Person` {`bornIn`:"New York", `chauffeurName`:"Bill White", `name`:"Oliver Stone"}) create (_4:`Person` {`bornIn`:"New York", `chauffeurName`:"John Brown", `name`:"Charlie Sheen"}) create (_5:`Person` {`bornIn`:"New Jersey", `chauffeurName`:"John Brown", `name`:"Michael Douglas"}) create (_6:`Person` {`bornIn`:"Ohio", `chauffeurName`:"Bob Brown", `name`:"Martin Sheen"}) create (_1)-[:`DIRECTED`]->(_2) create (_3)-[:`DIRECTED`]->(_0) create (_4)-[:`FATHER`]->(_6) create (_4)-[:`ACTED_IN`]->(_0) create (_5)-[:`ACTED_IN`]->(_2) create (_5)-[:`ACTED_IN`]->(_0) create (_6)-[:`ACTED_IN`]->(_2) create (_6)-[:`ACTED_IN`]->(_0) ; merge (oliver:Person {name:'Oliver Stone'}) return oliver.name, oliver.bornIn

Merge with unique constraints and partial matches

Merge using unique constraints fails when finding partial matches.

Query

MERGE (michael:Person { name:'Michael Douglas', role:'Gordon Gekko' })
RETURN michael

While there is a matching unique michael node with the name Michael Douglas, there is no unique node with the role of Gordon Gekko and MERGE fails to match.

Error message

Merge did not find a matching node and can not create a new node due to conflicts
with both existing and missing unique nodes. The conflicting constraints are on:
:Person.name and :Person.role

Merge with unique constraints and conflicting matches

Merge using unique constraints fails when finding conflicting matches.

Query

MERGE (oliver:Person { name:'Oliver Stone', role:'Gordon Gekko' })
RETURN oliver

While there is a matching unique oliver node with the name Oliver Stone, there is also another unique node with the role of Gordon Gekko and MERGE fails to match.

Error message

Merge did not find a matching node and can not create a new node due to conflicts
with both existing and missing unique nodes. The conflicting constraints are on:
:Person.name and :Person.role

Using map parameters with MERGE

MERGE does not support map parameters like for example CREATE does. To use map parameters with MERGE, it is necessary to explicitly use the expected properties, like in the following example. For more information on parameters, see Section 8.5, “Parameters”.

Parameters

{
  "param" : {
    "name" : "Keanu Reeves",
    "role" : "Neo"
  }
}

Query

MERGE (person:Person { name: { param }.name, role: { param }.role })
RETURN person.name, person.role

Result

person.name	person.role
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`"Keanu Reeves"`	`"Neo"`

The Neo4j Manual v3.1.0-SNAPSHOT12.2. Merge

Introduction

Merge nodes

Merge single node with a label

Merge single node with properties

Merge single node specifying both label and property

Merge single node derived from an existing node property

Use ON CREATE and ON MATCH

Merge with ON CREATE

Merge with ON MATCH

Merge with ON CREATE and ON MATCH

Merge with ON MATCH setting multiple properties

Merge relationships

Merge on a relationship

Merge on multiple relationships

Merge on an undirected relationship

Merge on a relationship between two existing nodes

Merge on a relationship between an existing node and a merged node derived from a node property

Using unique constraints with MERGE

Merge using unique constraints creates a new node if no node is found

Merge using unique constraints matches an existing node

Merge with unique constraints and partial matches

Merge with unique constraints and conflicting matches

Using map parameters with MERGE