Hybrid Query Example in AgensGraph

Question

I am using agensgraph but I dont know how to write a hybrid query, any examples of hybrid query in agensgraph would help a lot.

Answer 1

In AgensGraph you can write hybrid queries in two ways:

Let's say you are creating the followings:

CREATE GRAPH AG;
CREATE VLABEL dev;
CREATE (:dev {name: 'someone', year: 2015});
CREATE (:dev {name: 'somebody', year: 2016});

CREATE TABLE history (year, event)
AS VALUES (1996, 'PostgreSQL'), (2016, 'AgensGraph');

1- Cypher in SQL

Syntax:
SELECT [column_name]
FROM ({table_name|SQL-query|CYPHERquery})
WHERE [column_name operator value];

Example:
SELECT n->>'name' as name 
FROM history, (MATCH (n:dev) RETURN n) as dev 
WHERE history.year > (n->>'year')::int;

Result: name ---- someone (1 row)

2- SQL in Cypher

Syntax:
MATCH [table_name]
WHERE (column_name operator {value|SQLquery|CYPHERquery})
RETURN [column_name];

Example:
MATCH (n:dev)
WHERE n.year < (SELECT year FROM history WHERE event = 
'AgensGraph')
RETURN properties(n) AS n;

Result: n ---- {"name": "someone", "year": 2015} (1 row)

You can find more information here

Answer 2

I found more info on the hybrid query language in these slides. Every other bit of information I have been able to find is just the same example that Eya posted, in different places.

I agree that more information about the hybrid queries in AgensGraph would be great, as it seems like a killer feature of software.

Answer 3

Let’s assume that we have a network management system and we are keeping our network topology in graph part of the AgensGraph (Graph Format) and our time-series data (such as date&time information regarding specific devices) in the relational part of the AgensGraph (Table Format). So, in this case, we know that we have a graph, tables and if we want, we can write a hybrid query to fetch data from both models.

In our graph, we have different devices that are connected to each other such as a modem, IoT sensors, etc. for each of these devices, we also have some information respectively stored in tables - related to those devices such as download speed, the upload speed or CPU usage.

In the following hybrid queries, our goal is to collect the information regarding specific devices by querying both from the graph and the tables simultaneously.

Cypher in SQL

In this hybrid query, we are looking to find modem devices which are having issues and their abnormality type is 2 (2 indicates that this device is having some issues regarding its download and upload speed) and after we find those devices, our goal is to return their id, download, and upload speed to investigate the issue. As you can see in the following query our inner query is Cypher and our outer query is SQL.

SELECT id,sysdnbps, sysupbps
from public.modemrdb where to_jsonb(id) in
(SELECT id FROM (MATCH(m:modem) where 
m.abnormaltype=2
return m.name)
AS s(id)); 

SQL in Cypher

In this hybrid query, we are looking to find modem devices which their CPU usages are more than 80 (not in range of threshold) which indicate there is an issue with these devices and after we find those devices, our goal is to return that modems and any IoT devices that are connected to them. As you can see in the following example our inner query is SQL and our outer query is Cypher.

MATCH p=(n:modem)-[r*1..2]->(iot)
WHERE n.name in
(SELECT to_jsonb(id)
FROM public.modemrdb
WHERE syscpuusage >= 80)
RETURN p;

This can be another example of a hybrid query.