11.3. Merge

Prev		Next

11.3.1. Introduction

	Provisional Feature
	`MERGE` is a new addition and a provisional feature of Cypher. Its scope and syntax are subject to change. Therefore, please be aware that you may need to change queries that use `MERGE` in the future.

MERGE ensures that a pattern exists in the graph. Either the pattern already exists, or it needs to be created.

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 following graph is used for the examples below:

Figure 11.1. Graph

11.3.2. Merge nodes

Merge single node with a label

Merging a single node with a given label.

Query.

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

Because there are no nodes labeled Critic in the database, a new node is created.

Result

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

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 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[8]{name:"Charlie Sheen",age:10}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 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

Michael Douglas will be matched and returned.

Result

michael
1 row
`Node[6]{name:"Michael Douglas"}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (michael:Person {name:'Michael Douglas'}) return michael

11.3.3. 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

Creates the Keanu node, and sets a timestamp on creation time.

Result

keanu
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`Node[8]{name:"Keanu Reeves",created:1386514444417}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (keanu:Person {name:'Keanu Reeves'}) on create set keanu.created = timestamp() return keanu

Merge with ON MATCH

Merging nodes and setting properties on found nodes.

Query.

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

Finds all the Person nodes, sets a property on them, and returns them.

Result

person
5 rows
Properties set: 5
`Node[0]{name:"Oliver Stone",found:true}`
`Node[1]{name:"Charlie Sheen",found:true}`
`Node[2]{name:"Martin Sheen",found:true}`
`Node[5]{name:"Rob Reiner",found:true}`
`Node[6]{name:"Michael Douglas",found:true}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (person:Person) on match set person.found = true return person

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

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

Result

keanu
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`Node[8]{created:1386514447501,name:"Keanu Reeves"}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (keanu:Person {name:'Keanu Reeves'}) on create set keanu.created = timestamp() on match set keanu.lastSeen = timestamp() return keanu

11.3.4. 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 r

Charlie Sheen had already been marked as acting on 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

r
1 row
`:ACTED_IN[0]{}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 match (charlie:Person {name:'Charlie Sheen'}), (wallStreet:Movie {title:'Wall Street'}) merge (charlie)-[r:ACTED_IN]->(wallStreet) return r

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, Cypher 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[8]{}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 match (oliver:Person {name:'Oliver Stone'}), (reiner:Person {name:'Rob Reiner'}) merge (oliver)-[:DIRECTED]->(movie:Movie)<-[:ACTED_IN]-(reiner) return movie

11.3.5. 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

The query creates the laurence node. If laurence already existed, merge would just return the existing node.

Result

laurence
1 row
Nodes created: 1
Properties set: 1
Labels added: 1
`Node[8]{name:"Laurence Fishburne"}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (laurence:Person {name: 'Laurence Fishburne'}) return laurence

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

The oliver node already exists, so merge just returns it.

Result

oliver
1 row
`Node[0]{name:"Oliver Stone"}`

Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 merge (oliver:Person {name:'Oliver Stone'}) return oliver

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

11.3.6. 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 7.5, “Parameters”.

Parameters.

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

Query.

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

Result

oliver
1 row
Nodes created: 1
Properties set: 2
Labels added: 1
`Node[8]{name:"Keanu Reeves",role:"Neo"}`