Querying for repeated patterns in a database is a common practice. In a bank database,for example, it is common to identify network of accounts that transfer money from one account to another account in the network, when the balance of receiving account is always greater than balance of the sending account. These can be a multi-hop transactions, and are a common pattern in money laundering.
Here we can have account nodes and transfer edges. Account and Transfer are labels; account nodes have
properties such as owner and balance, while transfer edges have have properties such as amount and timestamp (ts).
With this example, consider the query: “Return two accounts such that there is a transfer chain between them in which account balances increase.” GQL makes this easy. Let start with creating some sample data:
CREATE OR REPLACE TABLE graph_db.account (
owner STRING NOT NULL,
balance int,
PRIMARY KEY (owner) NOT ENFORCED
);
CREATE OR REPLACE TABLE graph_db.transfer (
trannum int,
from_owner STRING NOT NULL,
to_owner STRING NOT NULL,
amount int,
trandate date,
PRIMARY KEY (trannum) NOT ENFORCED
);
insert into graph_db.account values
('Megan', 100)
, ('Jay', 150)
, ('Robbin', 160)
, ('Rebecca', 200)
, ('Scott', 150)
, ('Mike', 180);
insert into graph_db.transfer values
(1, 'Megan', 'Jay', 10, '2026-01-01')
, (2, 'Jay', 'Robbin', 20, '2026-01-02')
, (3, 'Robbin', 'Rebecca', 3, '2026-01-03')
, (4, 'Rebecca', 'Scott', 4, '2026-01-04')
, (5, 'Scott', 'Mike', 50, '2026-01-05')
;Next create a Property Graph to represent the Accounts (Nodes) and Transfers (Edges)
CREATE or replace PROPERTY GRAPH graph_db.bank_transfer_graph
NODE TABLES (
graph_db.account
KEY (owner)
LABEL account
PROPERTIES (owner, balance),
)
EDGE TABLES(
graph_db.transfer
KEY (trannum)
SOURCE KEY (from_owner) REFERENCES account (owner)
DESTINATION KEY (to_owner) REFERENCES account (owner)
LABEL transfer
PROPERTIES (from_owner, to_owner, amount, trandate),
);Ask: “Return two accounts such that there is a transfer chain between them in which account balances increase.”
We can use the following GQL Query:
GRAPH graph_db.bank_transfer_graph
MATCH (x) ((a:account)->(b:account) WHERE a.balance < b.balance){1,20} (y)
return x.owner as acc_a, y.owner as acc_b;Here we use {1,20} to indicate the sub-pattern repeats one or more times. Up to 20 times in this case.
Note that this pattern repetition query only works for Nodes. We can not use the same query for Edges. For example the following Query will return incorrect results
GRAPH graph_db.bank_transfer_graph
MATCH (x) (()-[t1]->()-[t2]->() WHERE t1.amount < t2.amount){1,30} (y)
return x.owner as acc_a, y.owner as acc_b;This is a limitation of GQL.
Leave a Reply