Apache AGE

Install

PostgreSQL

sudo apt install postgresql

psql --version

Agache AGE

sudo apt update
sudo apt install postgresql-17-age

Create AGE database

sudo -i -u postgres

While logged into bash as postgres and create a user and database for the AGE graphs.

createuser age

psql

-- set age password
\password "age"

-- set user 'age' as superuser
alter user age WITH SUPERUSER;

-- create datagase
create database age;

-- connect to database
\c age

-- set age database owner to role 'age'
alter database age owner to age;

-- change current role to 'age'
set role age;

-- add AGE extension
create extension age;
load 'age';
set search_path = ag_catalog, "$user", public;

Tests

-- create graph
select create_graph('graph_name');

-- drop graph
select * from drop_graph('graph_name', true);

select * from ag_graph;
select * from ag_label;

Create a vertex label

select create_vlabel('new','Person');

Create vertexes and edge

select * from cypher('new', $$
CREATE (:Person {name: 'Daedalus'})-[:FATHER_OF]->(:Person {name: 'Icarus'})
$$) AS (a agtype)

select * from cypher('new', $$
match (v:Person)
return v.name
$$) as (result agtype);

select * from cypher('new', $$
match (e:FATHER_OF)
return e.name
$$) as (result agtype);

Maps

select *                      
from cypher('new', $$
with [0, {key: 'kval'}, 2, 3] as lst
return lst[1].key
$$) as (result agtype);

Create

select * from cypher('new', $$
create (:Person {name: 'John'}),
(:Person {name: 'Jeff'}),
(:Person {name: 'Joan'}),
(:Person {name: 'Bill'})
$$) as (result agtype);

Match

select * from cypher('new', $$
MATCH (v:Person)
WHERE v.name STARTS WITH "J"
return v.name
$$) as (names agtype);

Create

select * from cypher('new', $$
CREATE (a:Person {name: 'A', age: 13}),
        (b:Person {name: 'B', age: 33, eyes: "blue"}),
        (c:Person {name: 'C', age: 44, eyes: "blue"}),
        (d1:Person {name: 'D', eyes: "brown"}),
        (d2:Person {name: 'D'}),
        (a)-[:KNOWS]->(b),
        (a)-[:KNOWS]->(c),
        (a)-[:KNOWS]->(d1),
        (b)-[:KNOWS]->(d2),
        (c)-[:KNOWS]->(d2)
$$) as (a agtype);

select * from cypher('new', $$
match (v:Person)
return v.*                                            
$$) AS (names agtype);

Match properties

select * from cypher('new', $$
match (v:Person)
return v.age, v.name                                
$$) AS (id agtype, names agtype);

Return aggregate

select * from cypher('new', $$
match (v:Person)
return v.name, count(*)                          
$$) as (grouping_key agtype, count agtype);

Match regex

select * from 
cypher('new', $$
match (a:Person) where a.name =~ 'J.*'
return a                                   
$$) as (name agtype);

Match and return aggreage

select *      
from cypher('new', $$
match (me:Person)-[]->(friend:Person)
return count(friend), me.name
order by count(friend)
$$) as (friends agtype, me agtype);

Test relationship directionality

select *                                
from cypher('new', $$
create (a:Person {name:'X', age: 20}),
(b:Person {name:'Y', age:21}),
(a)<-[:KNOWS]-(b)
$$) as (a agtype);

select *                                
from cypher('new', $$
match (me:Person)-[]->(friend:Person)
return count(friend), me.name
order by count(friend)
$$) as (friends agtype, me agtype);

select *                                
from cypher('new', $$
create (a:Person {name:'X', age: 20}),
(b:Person {name:'Y', age:21}),
(a)-[:KNOWS]->(b)
$$) as (a agtype);

select *                                
from cypher('new', $$
match (me:Person)-[]->(friend:Person)
return count(friend), me.name
order by count(friend)
$$) as (friends agtype, me agtype);

Create and delete label

-- create label
select create_vlabel('new', 'memberof');
-- drop label
select drop_label('new', 'memberof');

Testing chained relationships

elect *                                
from cypher( 'new', $$
create_vlabel('user')
$$) as (a agtype);

select create_elabel('new', 'memberof');
select create_vlabel('new', 'user');
select create_vlabel('new', 'group');

Create users

-- create
select * from cypher( 'new', $$
create (a:user {name: 'apple'}),
(b:user {name: 'banana'}),
(c:user {name: 'carrot'})
$$) as (a agtype);

-- test
select * from cypher( 'new', $$
match (a:user)
return a                  
$$) as (u agtype);

Delete all users

-- create
select * from cypher( 'new', $$
match (a:user) delete a
$$) as (a agtype);      

-- check
select * from cypher( 'new', $$
match (a:user)         
return a        
$$) as (u agtype);

Create all users, groups, and relationships.

-- create
select * from cypher( 'new', $$
create (a:user {name: 'apple'}),
(b:user {name: 'banana'}),
(c:user {name: 'carrot'}),
(d:user {name: 'dykon'}),
(e:group {name: 'fruits'}),
(f:group {name: 'veggies'}),
(a)-[:memberof]->(e),
(b)-[:memberof]->(e),
(c)-[:memberof]->(f),
(d)-[:memberof]->(f);
$$) as (q agtype);

-- check
select * from cypher( 'new', $$
match (a)-[:memberof]->(b)
return a.name, b.name
$$) as (u agtype, g agtype);

Create new group

select * from cypher( 'new', $$ 
create (a:group {name:'foods'})
$$) as (a agtype);

Create new relationships

select * from cypher( 'new', $$ 
match (a:group), (b:group), (c:group)
where a.name = 'fruits' and b.name = 'veggies' and c.name = 'foods'
create (a)-[:memberof]->(c), (b)-[:memberof]->(c)
$$) as (a agtype);

-- check
select * from cypher('new', $$ 
match (a:user)-[:memberof]->(b:group)-[:memberof]->(c:group)
return a.name, b.name, c.name
$$) as (a agtype, b agtype, c agtype);

-- check apple -> foods
select * from cypher('new', $$ 
match (a:user)-[*]->(c:group) 
where a.name = 'apple' and c.name = 'foods'
return a.name, b.name, c.name
$$) as (a agtype, b agtype, c agtype);

-- check apple -> foods and output relationships
select * from cypher('new', $$
match p = (a:user)-[*]->(c:group) 
where a.name = 'apple' and c.name = 'foods'
return relationships(p)
$$) as (r agtype);

-- check apple -> foods and output nodes
select * from cypher('new', $$
match p = (a:user)-[*]->(c:group) 
where a.name = 'apple' and c.name = 'foods'
return nodes(p)
$$) as (r agtype);

-- check apple -> foods and output length
select * from cypher('new', $$
match p = (a:user)-[*]->(c:group) 
where a.name = 'apple' and c.name = 'foods'
return length(p)
$$) as (r agtype);

-- check apple -> foods and output nodes list
select * from cypher('new', $$
match p = (a:user)-[*]->(c:group) 
where a.name = 'apple' and c.name = 'foods'         
unwind nodes(p) as i 
return i.name
$$) as (r agtype) ;

-- as string

with path as (
  select * from cypher('new', $$
  match p = (a:user)-[*]->(c:group) 
  where a.name = 'apple' and c.name = 'foods'         
  unwind nodes(p) as i 
  return i.name             
  $$) as (r agtype) )
select  string_agg( r::text, ' - ') 
from path;

with path as (
  select * from cypher('new', $$
  match p = (a:user)-[*]->(c:group) 
  where a.name = 'apple' and c.name = 'foods'
  unwind nodes(p) as n
  return n.name
  $$) as (r agtype))
select string_agg( r::text, ' - ') 
from path;