Mastering the SET Clause in Apache AGE: A Comprehensive Guide
Tags: apache, apacheage, database, postgres
Published: 2023-05-25 21:25:55

Apache AGE is a powerful graph extension for PostgreSQL that allows you to work with property graphs using standard SQL syntax. One of the essential features in AGE is the `SET` clause, which enables you to modify and update properties of vertices and edges within the graph. In this blog post, we will delve into the details of the SET clause in Apache AGE and explore its various capabilities and use cases.

### The SET Clause
The `SET` clause in Apache AGE is used to modify the properties of vertices and edges within a property graph. It provides a flexible and intuitive way to update the graph data without having to rewrite the entire graph structure.

A set clause that does not have any subsequent clause is referred to as a terminal clause. If a Cypher query concludes with a terminal clause, the cypher function call will not yield any results. Nevertheless, it is still necessary to provide a column list definition for the cypher function call. In the case of a terminal node in Cypher, the column list definition should consist of a single value, resulting in no data being returned in that particular variable.

---

### How to use the SET Clause
Given a graph with properties;

```
demo=# SELECT * FROM cypher('persons', $$ CREATE (: Person {name: 'Peter'}),
demo$# (:Person {name: 'Paul'}) $$) as (persons agtype);
 persons
---------
(0 rows)

demo=# SELECT * FROM cypher('persons', $$ MATCH (u) RETURN u $$) as (persons agtype);
                                       persons
-------------------------------------------------------------------------------------
 {"id": 844424930131974, "label": "Person", "properties": {"name": "Peter"}}::vertex
 {"id": 844424930131975, "label": "Person", "properties": {"name": "Paul"}}::vertex
(2 rows)
```
We can set a property on a node or relationship, using `SET`.

**QUERY**

```
demo=# SELECT * FROM cypher('persons',
demo(# $$ MATCH (peter:Person {name: 'Peter'})
demo$# SET peter.age = 20 RETURN peter $$)
demo-# as (peter agtype);
                                             peter
------------------------------------------------------------------------------------------------
 {"id": 844424930131974, "label": "Person", "properties": {"age": 20, "name": "Peter"}}::vertex
(1 row)
```
You can see that the newly updated vertex is returned by the query.

---

### Removing a Property using SET
Typically, the `REMOVE` clause _[see REMOVE](https://dev.to/dukeofhazardz/understanding-the-remove-clause-in-apache-age-3hbg)_ is used to eliminate a property. However, there are situations where utilizing the `SET` clause can be advantageous for property removal. One such scenario arises when the property is derived from a parameter. In such cases, using the `SET` clause proves useful as an alternative method to remove the property.

**QUERY**
```
demo=# SELECT * FROM cypher('persons',
$$ MATCH (peter:Person {name: 'Peter'})
SET peter.name = NULL RETURN peter $$)
as (peter agtype);
                                     peter
-------------------------------------------------------------------------------
 {"id": 844424930131974, "label": "Person", "properties": {"age": 20}}::vertex
(1 row)
```
I removed the the `name` property 'Peter' using the `SET` clause by setting `peter.name` to `NULL`.
And now the node is returned by the query, and the `name` property is now missing.

---

### Using one SET Clause for Multiple Properties
In other to achieve setting multiple properties using one `SET` clause, you have to simply separate the properties using a comma (,).

**QUERY**
```
demo=# SELECT * FROM cypher('persons',
$$ MATCH (peter:Person {age: 20})
SET peter.name = 'Peter', peter.surname = 'Francis' RETURN peter $$)
as (peter agtype);
                                                        peter
----------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131974, "label": "Person", "properties": {"age": 20, "name": "Peter", "surname": "Francis"}}::vertex
(1 row)

```

---

### Conclusion
The SET clause in Apache AGE is a powerful tool for modifying property graphs efficiently. Its flexibility and compatibility with standard SQL make it a valuable asset when working with graph data. Understanding its syntax and capabilities opens up a range of possibilities for updating and managing graph properties in a seamless and intuitive manner.

---

### References
- [The SET Clause Apache Age Docs](https://age.apache.org/age-manual/master/clauses/set.html)
- [The REMOVE Clause Blog post](https://dev.to/dukeofhazardz/understanding-the-remove-clause-in-apache-age-3hbg)
- 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