How to Perform MERGE in Apache AGE

One of the key features that sets Apache AGE apart is its support for the MERGE clause, which allows users to efficiently combine data from different sources or update existing data. In this blog post, we will explore the MERGE clause in Apache AGE and understand how it can be leveraged to simplify complex data operations.

The MERGE Clause

The MERGE clause is a powerful SQL statement that combines the functionality of MATCH and CREATE operations into a single statement. It enables users to perform conditional insertions or updates based on the existence of specific records within a graph database.

The MERGE clause ensures that a pattern exists in the graph. Either the pattern already exists, or it needs to be created.
MERGE either matches existing nodes, or creates new data. It’s a combination of MATCH and CREATE.

For instance, it is possible to specify that the graph should include a node representing a user with a specific name. If there is no node with the correct name, a new node will be generated, and its name property will be assigned. When utilizing the MERGE clause with complete patterns, the behavior is such that either the entire pattern is matched, or the entire pattern is created. It does not selectively utilize existing patterns. If there is a need for partial matches, one can achieve it by dividing a pattern into multiple MERGE clauses.

Similar to the MATCH operation, MERGE can identify multiple occurrences of a pattern. If there are multiple matches, all of them will be passed on to subsequent stages of the query.

MERGE Clause Syntax

Given a graph with nodes

demo=# SELECT * FROM cypher('demo', $$ CREATE (a:worker {name: 'Pete', city: 'New York'}),
demo$# (b:worker {name: 'Mike', city: 'Toronto'}),
demo$# (c:worker {name: 'Clint', city: 'Vancouver'}),
demo$# (d:worker {name: 'Jane', city: 'Dallas'}),
demo$# (e:worker {name: 'Tom', city: 'San Francisco'})
demo$# $$) as (a agtype);
 a
---
(0 rows)

If we return the contents of the graph;

demo=# SELECT * FROM cypher('demo', $$ MATCH (n) RETURN n$$)
as (n agtype);
                                                      n
-------------------------------------------------------------------------------------------------------------
 {"id": 2251799813685249, "label": "worker", "properties": {"city": "New York", "name": "Pete"}}::vertex
 {"id": 2251799813685250, "label": "worker", "properties": {"city": "Toronto", "name": "Mike"}}::vertex
 {"id": 2251799813685251, "label": "worker", "properties": {"city": "Vancouver", "name": "Clint"}}::vertex
 {"id": 2251799813685252, "label": "worker", "properties": {"city": "Dallas", "name": "Jane"}}::vertex
 {"id": 2251799813685253, "label": "worker", "properties": {"city": "San Francisco", "name": "Tom"}}::vertex
(5 rows)

We can see that the only label present in the graph is the worker label.

Merging a Node with a Label

If we perform a query using the MERGE clause and specifying a label which is not present in the graph;

demo=# SELECT * FROM cypher('demo', $$ MERGE (e:employer)
demo$# RETURN e $$) as (e agtype);
                                    e
-------------------------------------------------------------------------
 {"id": 2533274790395905, "label": "employer", "properties": {}}::vertex
(1 row)

From the output we can see that the employer label was created, since it's not already present in our graph.

Merging Single Vertex with Properties

You can equally search for a node with particular properties;

demo=# SELECT * FROM cypher('demo', $$ MERGE (jane {name: 'Jane', city: 'Dallas'})
demo$# RETURN jane $$) as (jane agtype);
                                                 jane
-------------------------------------------------------------------------------------------------------
 {"id": 2251799813685252, "label": "worker", "properties": {"city": "Dallas", "name": "Jane"}}::vertex
(1 row)

The MERGE clause simply returned the 'Jane' node from earlier in our worker label.

Merging a Single Vertex Specifying Both Label and Property

We can also return the specified properties from a specific node using the MERGE clause.

demo=# SELECT * FROM cypher('demo', $$ MERGE (pete {name: 'Pete'})
demo$# RETURN pete.name, pete.city $$)
demo-# as (Name agtype, City agtype);
  name  |    city
--------+------------
 "Pete" | "New York"
(1 row)

pete will match and return the existing vertex and the vertex’s name and city properties.

References

• Official Apache AGE Documentation
• MERGE clause Apache AGE
• Visit Apache AGE Website: https://age.apache.org/
• Visit Apache AGE GitHub: https://github.com/apache/age
• Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer