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 interface for running graph queries from SQL. It is exposed through the akasicdb.cypher() function, which accepts AkasicDB's custom graph-query dialect that combines openCypher-style MATCH syntax with selected PostgreSQL expressions, types, aggregates, and operators.

SQL/GQL draws on syntax from ISO GQL and openCypher but does not implement either as-is. Therefore, the name SQL/GQL should not be interpreted as a declaration of ISO GQL conformance. AkasicDB does not claim ISO GQL conformance or guarantee compatibility with a particular openCypher version. SQL/PGQ features such as GRAPH_TABLE and property-graph DDL are not supported. Rely on the syntax and behavior explicitly documented on this page when writing AkasicDB graph queries.

Because akasicdb.cypher() is a PostgreSQL table source, you can post-process its scalar results with SQL, join them to relational tables or other graph queries, and use them in outer SQL set operations. The vector operators described in Vector Features - Querying can also be used in a graph query when PostgreSQL can resolve compatible operand types and an installed operator.

Function Signature and SQL Call Requirements¶

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
);

A complete SQL call must also provide the outer FROM clause and a column definition list, as shown below.

SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)
RETURN c.first_name
$$) AS (
    first_name varchar
);

The call has the following SQL-level requirements:

Call akasicdb.cypher() as a table source in the FROM clause. Using it as a scalar expression, such as SELECT akasicdb.cypher(...), is not supported.
Write query_string as a PostgreSQL dollar-quoted literal. Both $$...$$ and tagged forms such as $query$...$query$ are accepted; an ordinary single-quoted string is not.
Add an AS (name type, ...) column definition list to every call. The function returns record, so the number of declared columns must match the number of columns produced by the graph query's RETURN clause. Produced columns are mapped to the AS columns by position.
AkasicDB explicitly casts each returned value to its declared AS type using PostgreSQL's explicit-cast rules. AkasicDB does not provide SQL result types representing node, edge, or path objects; return scalar properties instead.

The optional third argument is a JSONB object containing graph-query parameters. Extracted parameter values are exposed as TEXT by default and can be converted with PostgreSQL-style casts such as ::integer. JSON objects and arrays are serialized as JSON text.

Query Support Overview¶

The following table is a non-exhaustive overview of commonly used supported constructs. See the examples and "Notes on Using SQL/GQL" below for detailed constraints. Syntax not documented on this page should not be assumed to be supported.

Area	Supported behavior
Patterns	Exactly one label on the first occurrence of a node variable; later occurrences of that variable may omit the label. Directed forward (`->`) and reverse (`<-`) traversal and paths containing multiple fixed relationships are supported.
Reading	`MATCH`, property predicates in `WHERE`, and additional `MATCH` clauses after `WITH`
Projection	Scalar properties and PostgreSQL scalar expressions in `WITH` and `RETURN`
Modifiers	`DISTINCT`, `ORDER BY` on resolvable expressions with `ASC`, `DESC`, `ASCENDING`, or `DESCENDING`, `SKIP`, and `LIMIT`; `WITH` also supports projection aliases and these modifiers
Aggregation	`count(*)`, `count(DISTINCT ...)`, `array_agg()`, and PostgreSQL aggregates such as `sum()`, `avg()`, `min()`, and `max()` when PostgreSQL can resolve compatible input and result types
Variable-length traversal	Bounded and open-ended VLE ranges, with the traversal semantics described in VLE (Variable-Length Edge) Pattern
Parameters and types	`$parameter` values from the optional `JSONB` argument and PostgreSQL-style `::type` casts
SQL composition	Outer SQL joins, including `LEFT JOIN`, and outer SQL set operations such as `UNION`
Vector expressions	PostgreSQL operators such as `<->`, `<=>`, and `<#>` when compatible operand types and operators are installed

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 varchar 
);

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 varchar,
    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 accepts *, *n, *m..n, *..n, *m.., and *... Zero-hop ranges are also supported. Bounds cannot be negative, and a lower bound cannot be greater than the upper bound.

VLE and TC (Transitive Closure) patterns have the following behavior and limitations:

Only one edge label is allowed, and the edge must connect vertices with the same label.
Within one VLE, the same physical edge cannot be reused, but a vertex can be revisited. This is trail, or edge-simple, traversal rather than simple-path traversal.
Edge uniqueness is not enforced globally across separate relationship patterns in a composed path.
SHORTEST uses breadth-first traversal and emits the first qualifying path discovered for each endpoint within the hop bounds. Do not assume that its behavior is identical to an ISO GQL or openCypher shortest-path implementation.
shortestPath() maps to the same traversal mode and is restricted to an argument containing one variable-length relationship.
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. Use SHORTEST only when the first qualifying path discovered for each endpoint is the intended result.

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¶

AkasicDB's graph-query dialect resembles openCypher but has its own supported syntax and semantics. Do not assume that an openCypher construct is supported unless it is documented here. Other PostgreSQL functions may work when PostgreSQL can resolve compatible types, but they are not openCypher compatibility guarantees. Unsupported syntax may be added in future updates.

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

When a new vertex variable first appears in a graph pattern, it must specify exactly one label. A reused variable can omit the label. Use (c:v_customer) for the first occurrence of c; unlabeled and multi-label first occurrences are not supported.
An edge pattern must specify a single edge label. Use patterns such as (c:v_customer)-[r:buy]->(i:v_item).
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, edges, or paths as graph objects. A bare vertex or edge may expose underlying record columns, but it does not produce a graph object; return individual scalar properties explicitly.
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 uses PostgreSQL arrays and 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.
OFFSET is not part of the graph-query grammar. Use SKIP inside akasicdb.cypher(), or apply OFFSET in the outer SQL query. A LIMIT argument is coerced to BIGINT and cannot reference current-row variables.
Cypher mutation clauses CREATE, MERGE, SET, and DELETE are not supported. Use the SQL functions and SQL UPDATE operations described in Graph DML.

Graph Query¶

Prepare the Graph¶

SQL/GQL Query Interface¶

What is SQL/GQL?¶

Function Signature and SQL Call Requirements¶

Query Support Overview¶

Basic Traversal¶

WITH Clause¶

Aggregation¶

VLE (Variable-Length Edge) Pattern¶

Using Parameters in Graph Queries¶

Notes on Using SQL/GQL¶

`WITH` Clause¶