Graph-SQL

A graph consists of nodes (vertices) and edges (links). Nodes are the entities, such as a person (person) or a car (car). Edges connect nodes to each other. There are two types of edges in the example below: friends and owner.

Nodes and edges (links) are stored in the tables. The "owner" relationship is presented as a simple Foreign Key field (many-to-one relationship). The "friends" many-to-many relationship is presented as a table of a special type EDGE(See «Demo Database»).

Presentation of a graph as the relational tables

Queries

Graph-SQL is a language for running graph queries. It is a simple extension of SQL. Graph-SQL adds a new MATCH expression.

Let's start with a simple example. We want to find all people who have a car and who have friends having a car of the same model.

SELECT p1.name, p2.name as friendname, c1.model 
MATCH (car c1)-[owner]->(person p1)-[friends]->(person p2)<-[owner]-(car c2)
WHERE c1.model = c2.model

MATCH expression is a pattern describing what objects and relationships we need to perform the query. MATCH replaces the FROM clause in SQL queries. The remaining clauses: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, INTO are regular SQL expressions.

In the example below the MATCH expression describes:

  • there is a node c1 of type car
  • c1 by the link owner refers to the node p1 of type person
  • p1 by the link friends refers to the node p2 of type person
  • c2 by the link owner refers to the node p2

WHERE expression adds the condition c1.model = c2.model

The picture below shows a subgraph satisfying this query:

The query result looks like a regular SQL SELECT query result.

name friendname model
John Sally Toyota

Simple experssion MATCH presents a chain of the nodes and links. But what to do if the pattern cannot be represented as a chain? For example, we need to specify several links (more than two) from the node p1. In this case, we can add another MATCH expression.

SELECT p1.name, p2.name as friendname, c1.model 
MATCH (car c1)-[owner]->(person p1)-[friends]->(person p2)<-[owner]-(car c2)
MATCH (p1)-[friends]->(person p3)<-[owner]-(car c3)
WHERE c1.model = c2.model and c3.model = c1.model

Please note that the second MATCH does not specify the p1 type since it has already been described earlier.

For your convenience, instead of a set of MATCH expressions (as in the example above), you can specify all the patterns under one MATCH expression separating them by commas (as in the example below).

SELECT p1.name, p2.name as friendname, c1.model 
MATCH (car c1)-[owner]->(person p1)-[friends]->(person p2)<-[owner]-(car c2),
      (p1)-[friends]->(person p3)<-[owner]-(car c3)
WHERE c1.model = c2.model and c3.model = c1.model

When searching for friends, you may need to limit the search depth (friends, friends of friends, friends of friends of friends, etc.).

The arrow -[friends]-> in the MATCH expression, representing a link, may contain the additional parameters for represintation of depth.

expression comment
-[friends]-> depth equals 1 (friends)
-[friends 2]-> depth equals 2 (friends of friends)
-[friends 3]-> depth equals 3 (friends of friends of friends)

An additional depth parameter can indicate that you need all intermediate nodes that are reachable by the link from one depth to another:

expression comment
-[friends 2,3]-> depth range from 2 to 3 (friends of friends + friends of friends of friends)
-[friends 2,*]-> depth is 2 and higher (friends of friends + friends of friends of friends + ...)
-[friends *]-> the same as -[friends 1,*]->

Find all friends for John (as well as friends of friends and friends of friends of friends) living in the same city with him.

SELECT p1.name, p2.name as friendname
MATCH (person p1)-[friends 1..3]->(person p2)
WHERE p1.name = 'John' and p1.city = p2.city

Graph data modification

Creaing graph structure

Data Definition Language (DDL) - basic graph expressions

Query Comment
CREATE TABLE
DROP TABLE
Creating, deleting tables. Tables for graph edges are created with the extension AS EDGE.
ALTER TABLE Table schema modification (adding, deleting the columns etc.).
CREATE INDEX
DROP INDEX
Creating, deleting indexes.
You can also create an index to describe the many-to-one relationship for one or more fields (to create a foreign key relationship but without a constrain declaration)

Data adding and deleting

Data Manipulation Language (DML)

Query             Comment
INSERT
INSERT INTO
SELECT INTO
BULK INSERT
Adding nodes and edges to graph is similar to adding records to relational tables.
DELETE Deleting nodes and edges from the graph.
UPDATE Modification of the values for individual columns of records.