Run merge query if relationship does not exist?

[mikecrittenden]

Our neo4j DB has Device nodes and User nodes, and OWNS relationships between them (a user can own one or multiple devices).

I need a query that does this:

• If the device exists and there's an existing OWNS relationship with an existing user, return that user.
• If the device exists but there's no associated user, create the relationship and the user, and return the user.
• If the device doesn't exist, create the device AND the user AND the relationship, and return the user.

In other words, if the user that owns the device already exists, then we need that user ID, otherwise we need to create a user with an arbitrary user ID.

This covers 2nd and 3rd points:

MERGE (device:Device {id: 1234})<-[r:OWNS]-(user:User {id: 5678})
RETURN user

But I don't know how to work in the first point.

I know that ON CREATE and ON MATCH are supported for MERGE queries but those only seem to allow for setting properties, not running subqueries.

Any thoughts on if this is possible without needing 2 separate queries?

[andrew_bowman]

MERGE is a powerful but intricate operation, you may want to review some of the finer points here:

Neo4j Graph Database Platform

Understanding how MERGE works - Neo4j Graph Database Platform

What is MERGE, and how does it work? The MERGE clause ensures that a pattern exists in the graph. Either the pattern already exists, or it needs to be created. In this way, it’s helpful to think of MERGE as… Read more →

After review, you might see that this approach may work for you:

MERGE (device:Device {id: 1234})
MERGE (user:User {id: 5678})
MERGE (device)<-[r:OWNS]-(user)
RETURN user

First the device will be created (if it doesn't exist already).
Then the user will be created (if it doesn't exist already).
Then the relationship between the user and the device will be created (if it doesn't exist already).
Then the user is returned.

Will that do what you want, or is there something I overlooked?

[mikecrittenden]

Hey there, thanks for responding! The problem I'm trying to solve is actually that if the user DOES exist, then the user's ID will be unknown.

In your snippet, it assumed that the existing user would have an ID of 5678, but the 5678 just represents what ID we would give the user of creating it new.

If it already existed, we'd need to find the user by the relationship to the device rather than by ID.

Does that make sense?

[ameyasoft]

Try this:

//Find the user that owns the selected device....
MATCH (device:Device {id: 1234})<-[r:OWNS]-(user:User)
RETURN user.id

//Find the device that is being owned by selected user.....
MATCH (device:Device)<-[r:OWNS]-(user:User {id: 1234})
RETURN device.id, user.id

[andrew_bowman]

Thanks for the clarification. In that case since on create we would set the id, maybe this may work better:

MERGE (device:Device {id: 1234})
MERGE (device)<-[:OWNS]-(user:User)
ON CREATE SET user.id = 5678
RETURN user

This first creates the device (if it doesn't exist already), then it will try to MATCH to a user who owns the device. If no such pattern exists, it will create the pattern (including the :User node, which will be connected), and you can set the id accordingly (maybe usage of randomUUID() would work here)

[mikecrittenden]

@andrew.bowman That works perfectly! Thank you so much!