Graph Query¶

This section describes how to run graph queries in AkasicDB.

Prepare the Graph¶

For instructions on preparing the tables, see Prepare Tables.

-- Define a graph named 'retail_graph'
SELECT akasicdb.define_graph('retail_graph');

-- Add vertex definitions named 'v_item', 'v_customer', and 'v_store'
-- to the 'retail_graph' graph definition
SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_item', 
  ARRAY['i_no integer', 'name varchar(50)', 'price decimal(7,2)'],
  'item'
);

SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_customer', 
  ARRAY['c_id integer', 'first_name varchar(20)', 'last_name varchar(30)'],
  'customer'
);

SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_store', 
  ARRAY['s_id integer', 'name varchar(50)'], 
  'store',
  'SELECT s_id, name FROM store'
);

-- Add edge definitions named 'buy' and 'sell'
-- to the 'retail_graph' graph definition
SELECT akasicdb.define_edge(
  'retail_graph', 
  'buy',
  'v_customer', 'v_item',
  null,
  'SELECT null FROM customer c, orders o, item i '
  'WHERE c.c_id = o.c_id AND i.i_no = o.i_no',
  'customer c', 'item i'
);

SELECT akasicdb.define_edge(
  'retail_graph', 
  'sell',
  'v_store', 'v_item',
  null,
  'SELECT null FROM store s, orders o, item i '
  'WHERE s.s_id = o.s_id AND i.i_no = o.i_no',
  'store s', 'item i'
);

-- Add an edge definition named 'co_purchase'
-- to the 'retail_graph' graph definition
SELECT akasicdb.define_edge(
  'retail_graph', 
  'co_purchase',
  'v_customer', 'v_customer',
  null,
  'SELECT null '
  'FROM customer c1, orders o1, item i, orders o2, customer c2 '
  'WHERE c1.c_id = o1.c_id AND i.i_no = o1.i_no '
  'AND c2.c_id = o2.c_id AND i.i_no = o2.i_no',
  'customer c1', 'customer c2'
);

-- Create a graph based on the 'retail_graph' graph definition
SELECT akasicdb.create_graph('retail_graph');

SQL/GQL Query Interface¶

What is SQL/GQL?¶

SQL/GQL is AkasicDB’s query language for graph, vector, and relational data. The akasicdb.cypher() function allows you to run GQL, the ISO-standard graph query language. You can also post-process graph query results as relational tables or combine them with other SQL queries. The vector operators described in Vector Features - Querying can also be used directly in GQL queries.

Function arguments

Basic usage of akasicdb.cypher():

akasicdb.cypher(
    graph_name   NAME, -- Name of the graph to query
    query_string TEXT  -- Graph query to execute
);

akasicdb.cypher() with parameters:

akasicdb.cypher(
    graph_name   NAME, -- Name of the graph to query
    query_string TEXT,  -- Graph query to execute
    parameters   JSONB   -- Parameter values in JSON format
);

Graph queries use GQL/openCypher-style syntax with the SQL/GQL limitations described below. The query_string must be enclosed in dollar quotes ($$). After calling akasicdb.cypher(), you must add an AS clause so that the graph query results can be handled as tuples in the SQL query.

Basic Traversal¶

-- Retrieve the full name of the customer whose first name is Margaret
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)
WHERE c.first_name = 'Margaret'
RETURN 
    c.first_name as first_name,
    c.last_name as last_name
$$) as (
    first_name varchar,
    last_name varchar
);

-- Find other customers who purchased items that Margaret Farias also purchased
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c1:v_customer)-[:buy]->(i:v_item)<-[:buy]-(c2:v_customer)
WHERE c1.first_name = 'Margaret' 
  AND c1.last_name = 'Farias'
  AND c1.vertex_id <> c2.vertex_id
RETURN 
    c2.first_name as first_name,
    c2.last_name as last_name
$$) as (
    first_name varchar,
    last_name varchar
);

`WITH` Clause¶

-- Find items sold by stores where Margaret Farias made purchases
SELECT item_name 
FROM akasicdb.cypher('retail_graph', $$ 
MATCH (c:v_customer)-[:buy]->(i1:v_item)<-[:sell]-(s1:v_store) 
WHERE c.first_name = 'Margaret' 
  AND c.last_name = 'Farias'
WITH DISTINCT s1.vertex_id AS store_id 
MATCH (s2:v_store)-[:sell]->(i2:v_item) 
WHERE s2.vertex_id = store_id 
RETURN 
    DISTINCT i2.name AS item_name 
$$) as ( 
    item_name char 
);

Aggregation¶

-- Find the names and quantities of items purchased by Margaret Farias at a specific store
SELECT item_name, cnt
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)-[:buy]->(i:v_item)<-[:sell]-(s:v_store)
WHERE s.s_id = 1 
  AND c.first_name = 'Margaret' 
  AND c.last_name = 'Farias'
RETURN
    i.name AS item_name,
    count(i.name) AS cnt 
$$) as (
    item_name char,
    cnt integer
)

VLE (Variable-Length Edge) Pattern¶

-- Find customers within 1 to 3 hops from Latisha Hamilton
-- in a customer network built with 'co_purchase' edges
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c1:v_customer)-[:co_purchase*1..3 SHORTEST]->(c2:v_customer)
WHERE c1.first_name = 'Latisha' AND c1.last_name = 'Hamilton'
RETURN
    c2.first_name as first_name,
    c2.last_name as last_name
$$) as (
    first_name varchar,
    last_name varchar
);

VLE (Variable-Length Edge) and TC (Transitive Closure) patterns have the following limitations:
Only one edge label is allowed, and the edge must connect vertices with the same label.
Traversal can be very slow if the starting vertex, such as c1 in the example above, is not filtered.
Traversal performance may degrade as the hop count increases. Specifying SHORTEST can help maintain good performance even with larger hop counts.

Using Parameters in Graph Queries¶

-- Retrieve the full name of the customer whose name matches the parameter
-- 'customer_name', with the value 'Margaret'.
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)
WHERE c.first_name = $customer_name
RETURN 
    c.first_name as first_name,
    c.last_name as last_name
$$, '{"customer_name":"Margaret"}') as (
    first_name varchar,
    last_name varchar
);

In a graph query, parameters are represented as symbols that start with $, such as $customer_name in the example above.
Parameter values are passed to the function in JSON format.

-- Retrieve the full name of the customer whose c_id matches the parameter
-- 'customer_id', with the value '1'.
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)
WHERE c.c_id = $customer_id::integer
RETURN 
    c.first_name as first_name,
    c.last_name as last_name
$$, '{"customer_id":1}') as (
    first_name varchar,
    last_name varchar
);

Parameter values are treated as TEXT by default within the query
To use another type, such as an integer, explicitly cast the parameter in the query.
SQL/GQL uses SQL-style casts, such as ::integer, instead of openCypher-style functions like toInteger(). If the type name contains special characters, it should be enclosed in single quotes, such as ::'int[]'.

Notes on Using SQL/GQL¶

Graph queries in SQL/GQL are similar to openCypher, but there are some differences in the supported syntax and usage. Unsupported syntax may be added in future updates.

Before using the examples below, keep these pattern rules in mind:

When a vertex or edge first appears in a graph pattern, it must specify exactly one label. Use patterns such as (c:v_customer)-[r:buy]->(i:v_item) instead of unlabeled or multi-label patterns.
Every edge is directed. Traversals must choose either the forward direction (->) or the reverse direction (<-); undirected edge patterns such as -- are not supported.

OPTIONAL MATCH is currently not supported. Similar behavior can be achieved in SQL/GQL by using SQL outer joins.

openCypher SQL/GQL

MATCH (a:v1)-[e1:r1]->(b:v2)
OPTIONAL MATCH (b)-[e2:r2]->(c:v3)
WHERE a.vertex_id = 1
RETURN 
  a.vertex_id, 
  b.vertex_id, 
  c.vertex_id;

SELECT q1.a_id, q1.b_id, q2.c_id 
FROM akasicdb.cypher('graph', $$
  MATCH (a:v1)-[e1:r1]->(b:v2)
  WHERE a.vertex_id = 1
  RETURN a.vertex_id as a_id, b.vertex_id as b_id
$$) AS q1(
  a_id integer,
  b_id integer
) LEFT JOIN akasicdb.cypher('graph', $$
  MATCH (b:v2)-[e2:r2]->(c:v3)
  RETURN b.vertex_id as b_id, c.vertex_id as c_id
$$) AS q2(
  b_id integer,
  c_id integer
) ON q1.b_id = q2.b_id;

Set operations such as UNION are currently not supported within akasicdb.cypher(). To achieve similar behavior, use SQL set operations outside the akasicdb.cypher() call.

openCypher SQL/GQL

MATCH (a:v1)-[e1:r1]->(b:v2)
WHERE a.vertex_id = 1
RETURN b.vertex_id
UNION ALL
MATCH (a:v1)-[e2:r2]->(c:v3)
WHERE a.vertex_id = 1
RETURN c.vertex_id;

SELECT *
FROM akasicdb.cypher('graph', $$
  MATCH (a:v1)-[e1:r1]->(b:v2)
  WHERE a.vertex_id = 1
  RETURN b.vertex_id as v_id
$$) as (v_id integer)
UNION ALL
SELECT *
FROM akasicdb.cypher('graph', $$
  MATCH (a:v1)-[e2:r2]->(c:v3)
  WHERE a.vertex_id = 1
  RETURN c.vertex_id as v_id
$$) as (v_id integer);

Property maps such as (v:vertex_label {key: "Value"}) are not supported. Use property conditions in the WHERE clause to express the same logic.

openCypher SQL/GQL

MATCH (a:v1 {vertex_id: 1})-[e1:r1]->(b:v2)
RETURN b.vertex_id;

SELECT *
FROM akasicdb.cypher('graph', $$
  MATCH (a:v1)-[e1:r1]->(b:v2)
  WHERE a.vertex_id = 1
  RETURN b.vertex_id as v_id
$$) as (v_id integer);

The exists() function and path variables are currently not supported.
WITH and RETURN clauses do not support returning vertices or edges directly.
IN is currently not supported within akasicdb.cypher().

The openCypher collect() function is not supported. Use PostgreSQL’s array_agg() function instead.

openCypher SQL/GQL

MATCH (a:v1)
RETURN collect(a.vertex_id);

SELECT *
FROM akasicdb.cypher('graph', $$
  MATCH (a:v1)
  RETURN array_agg(a.vertex_id) as v_ids
$$) as (v_ids integer[]);

Array indexing starts from 1.
List comprehensions are currently not supported.
CALL subqueries are currently not supported.
openCypher helper functions such as labels(n), type(r), and properties(n) are currently not supported. Specify labels in patterns and access properties directly by name.
The openCypher id() function is currently not supported. Use the internal ID properties directly, such as n.vertex_id for vertices and r.edge_id for edges.
ORDER BY cannot reference an alias created in the same RETURN clause. Order by the original expression or property inside the graph query, or sort by the returned alias in the outer SQL query.

Graph Query¶

Prepare the Graph¶

SQL/GQL Query Interface¶

What is SQL/GQL?¶

Basic Traversal¶

WITH Clause¶

Aggregation¶

VLE (Variable-Length Edge) Pattern¶

Using Parameters in Graph Queries¶

Notes on Using SQL/GQL¶

`WITH` Clause¶