Add More Flexibility To Your PostgreSQL Queries With Apache AGE

In this blog post, I will introduce some use cases and examples of hybrid cypher-sql queries using Apache AGE, an extension for PostgreSQL that enables users to leverage a graph database on top of the existing relational databases.

What is Apache AGE

Apache AGE is an acronym for A Graph Extension and is inspired by Bitnine's AgensGraph, a multi-model database fork of PostgreSQL. Apache AGE allows users to store, query and analyze graph data using the openCypher query language, which is a standard graph query language supported by multiple graph database vendors.

Hybrid Queries

One of the main benefits of Apache AGE is that it enables users to combine the power and flexibility of graph data with the scalability and reliability of relational data. Users can perform hybrid queries using SQL and cypher in a single query. This can be useful for various scenarios, such as:

• Exploring complex relationships and patterns among entities in a graph, while aggregating or filtering data from relational tables.
• Enriching graph data with additional attributes or metadata from relational tables, or vice versa.
• Creating or updating graph data based on relational data, or vice versa.

Example of Hybrid Query usage

To illustrate how hybrid queries work in Apache AGE, let's look at some examples using a sample dataset. The dataset consists of two tables: schema_name.sql_person and schema_name.sql_friendship, which store information about persons and their friendships, respectively. The dataset also contains a graph named graph_name, which stores the same information as nodes and edges with labels Person and FRIEND.

The following query shows how to join the graph data with the relational data using a single clause:

WITH graph_query as (
    SELECT * FROM cypher('graph_name', $$
        MATCH (n:Person)-[:FRIEND]->(m:Person)
        RETURN n.name as name1, m.name as name2, id(n) as id1, id(m) as id2
    $$) as (name1 agtype, name2 agtype, id1 agtype, id2 agtype)
 )
SELECT t1.name as name1, t2.name as name2, t1.age as age1, t2.age as age2
FROM schema_name.sql_person AS t1
JOIN graph_query ON t1.person_id = graph_query.id1
JOIN schema_name.sql_person AS t2 ON t2.person_id = graph_query.id2;

The query returns the names and ages of all pairs of friends in the graph:

name1  | name2  | age1 | age2
Andres | Tobias | 36   | 25
Andres | Peter  | 36   | 35
Tobias | Peter  | 25   | 35

Another example shows how to create new edges in the graph, based on the relational data using a subquery:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person), (m:Person)
    WHERE id(n) IN (
        SELECT person_id
        FROM schema_name.sql_friendship
        WHERE friend_id = 4
    )
    AND id(m) = 4
    CREATE (n)-[:FRIEND]->(m)
    RETURN n.name as name1, m.name as name2
$$) as (name1 agtype, name2 agtype);

The query creates new FRIEND edges from all persons who are friends with person_id 4 in the sql_friendship table to person_id 4 in the graph. The query returns the names of the newly created pairs of friends:

name1 | name2
Peter | John
Mary  | John

These are just some examples of how hybrid queries can be used in Apache AGE. For more information and documentation, please visit https://age.apache.org/ and https://github.com/apache/age.