5.8 Examples for SQL Graph Queries
This section contains a few examples for querying a SQL property graph with fixed-length and variable-length graph pattern matching queries.
All the queries shown in the examples are run on the SQL property graph,
students_graph
, created in Example 4-1:
Example 5-5 Query Using An Edge Pattern Directed Left-To-Right
The following example shows a GRAPH_TABLE
query containing an edge
pattern (-[e IS friends]->
) which is directed from
left-to-right:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[e IS friends]-> (b IS person WHERE b.name='Alice')
WHERE a.name='Mary'
COLUMNS (a.name AS person_a, b.name AS person_b)
);
The code produces the following output:
PERSON_A PERSON_B
---------- ----------
Mary Alice
Example 5-6 Query Using An Edge Pattern Directed Right-To-Left
The following example shows a query containing an edge pattern
(<-[e IS friends]-
) which is directed from
right-to-left:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) <-[e IS friends]- (b IS person WHERE b.name='Mary')
WHERE a.name='Alice'
COLUMNS (a.name AS person_a, b.name AS person_b)
);
The code produces the following output:
PERSON_A PERSON_B
---------- ----------
Alice Mary
Example 5-7 Query Using Any-Directed Edge Pattern
The following example shows a query which contains any-directed edge
pattern (-[e IS friends]-
):
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[e IS friends] - (b IS person WHERE b.name='Alice' OR b.name='Mary')
WHERE (a.name='Alice' OR a.name='Mary')
COLUMNS (a.name AS person_a, b.name AS person_b)
);
The code produces the following output:
PERSON_A PERSON_B
---------- ----------
Mary Alice
Alice Mary
Example 5-8 Query Using an Anonymous Edge Variable
The following example shows a query where the edge element variable is omitted:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[]-> (b IS person)
COLUMNS (a.name AS person_a, b.name AS person_b)
);
Alternatively, you can replace the bracketed syntax for the edge pattern
(-[]->
) in the preceding query with an abbreviated syntax
->
.
The code produces the following output:
PERSON_A PERSON_B
---------- ----------
Mary John
Bob Mary
John Bob
Mary Alice
Example 5-9 Query Using Multiple Path Patterns
The following example shows a query containing two path patterns
(a)->(b)
, (a)->(c)
) which have a common
vertex as shown:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person WHERE a.name = 'John') -> (b IS person),(a IS person WHERE a.name = 'John') -> (c IS university)
COLUMNS (a.name AS person_a, b.name AS person_b,c.name as university)
);
The preceding code produces the following output:
PERSON_A PERSON_B UNIVERSITY
---------- ---------- ----------
John Bob ABC
Example 5-10 Query Using Disjoint Path Patterns
The following example shows a query containing two disjoint path patterns:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a IS person WHERE a.name='John') -[IS student_of]-> (b IS university),
(x IS person) -[IS friends]-> (y IS person)
COLUMNS (a.name AS a, b.name as university, x.name AS x, y.name as y)
);
The resulting output is as shown:
A UNIVERSITY X Y
---------- ---------- ---------- ----------
John ABC Mary John
John ABC Bob Mary
John ABC John Bob
John ABC Mary Alice
Example 5-11 Query Using Cyclic Path Patterns
The following example uses a cyclic path pattern
(MATCH (a)-[]->(b)-[]->(c)-[]->(a)
) as shown. Note
that the example uses the same vertex pattern variable name a
(which is bound to person
) twice. Thus, this finds cycles in the
graph containing three edges that finally bind to a
itself.
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[IS friends]-> (b IS person) -[IS friends]->
(c IS person) -[IS friends]-> (a)
COLUMNS (a.name AS person_a, b.name AS person_b, c.name AS person_c)
);
The preceding code produces the following output:
PERSON_A PERSON_B PERSON_C
---------- ---------- ----------
Bob Mary John
John Bob Mary
Mary John Bob
Example 5-12 Query Using Label Disjunction
The following example uses label disjunction in the vertex label expression:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a is person|university)
COLUMNS (a.name, a.dob)
);
The code produces the following output:
NAME DOB
---------- ---------
John 13-JUN-63
Mary 25-SEP-82
Bob 11-MAR-66
Alice 01-FEB-87
ABC NULL
XYZ NULL
6 rows selected.
Example 5-13 Query Using Label Conjunction
The following example uses label conjunction in the vertex label expression:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person), (a IS person_ht)
COLUMNS (a.name as name, a.dob as dob, a.height as height )
);
The code produces the following output:
NAME DOB HEIGHT
---------- --------- ----------
John 13-JUN-63 1.8
Mary 25-SEP-82 1.65
Bob 11-MAR-66 1.75
Alice 01-FEB-87 1.7
Example 5-14 Queries Using Recursive Path Patterns with Bounded Quantifiers
The following example uses a recursive path pattern to retrieve all friends within two hops:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
The preceding code produces the following output:
A B
---------- ----------
Mary Bob
The following example uses a recursive path pattern to retrieve all friends between one and two hops (inclusive):
SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{1, 2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
The preceding code produces the following output:
A B
---------- ----------
Mary Alice
Mary John
Mary Bob
The following example uses a recursive path pattern to retrieve all friends by performing from zero to two iterations:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{,2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
The preceding code produces the following output:
A B
---------- ----------
Mary Mary
Mary Alice
Mary John
Mary Bob
Note that in the first line of the preceding output,
Mary
is bound to both the element pattern variables,
a
and b
. This is because the query includes a
zero hop iteration and therefore, the vertex pattern to the left and the vertex
pattern to the right must bind to the same graph element.
Example 5-15 Queries Using Aggregations
The following example finds all paths that have a length between two and
three edges ({2,3}
), starting from a person named
John
and following only outgoing edges labeled
friends
and vertices labeled person
. Vertices
along paths should not have the same person_id
as
John
(WHERE p.person_id <>
friend.person_id
). The example uses the following four aggregates in the
COLUMNS
clause:
LISTAGG:
The first one creates a comma-separated list of the person names along the path and the second one creates a comma-separated list of the person ages along the path.AVG:
This computes the average age of the person group in a path.COUNT:
This computes the length of each path.
SQL> SELECT * FROM GRAPH_TABLE ( students_graph
MATCH (p IS person) (-[e IS friends]-> (friend IS person)
WHERE p.person_id <> friend.person_id){2,3}
WHERE p.name = 'John'
COLUMNS (LISTAGG(friend.name, ',') as fnames,
LISTAGG(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from friend.dob), ',') AS age_list,
AVG(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from friend.dob)) AS avg_age_group,
COUNT(e.friendship_id) AS path));
The preceding code produces the following output:
FNAMES AGE_LIST AVG_AGE_GROUP PATH
------------------------------ --------------- ------------- ----------
Bob,Mary 57,41 49.00 2
Bob,Mary,Alice 57,41,36 44.67 3
The following example finds all paths between university
ABC
and university XYZ
such that paths have a
length of up to three edges ({,3}
). For each path, a JSON array is
returned such that the array contains the friendship_id
value for
edges labeled friends
, and the subject
value for
edges labeled student_of
. Note that the
friendship_id
property is casted to
VARCHAR(100)
to make it type-compatible with the
subject
property.
SELECT * FROM GRAPH_TABLE ( students_graph
MATCH (u1 IS university) -[e]-{,3} (u2 IS university)
WHERE u1.name = 'ABC' AND u2.name = 'XYZ'
COLUMNS (JSON_ARRAYAGG(CASE WHEN e.subject IS NOT NULL THEN e.subject
ELSE CAST(e.friendship_id AS VARCHAR(100)) END) AS path));
The preceding code produces the following output:
PATH
-----------------------------------------
["Arts","3","Math"]
["Music","4","Math"]
Example 5-16 Query Using Bind Variables
The example declares a bind variable, name
and assigns a
value as shown:
SQL> variable name VARCHAR2(10);
SQL> BEGIN
2 :name := 'Bob';
3 END;
4 /
PL/SQL procedure successfully completed.
Using this bind variable, the following query is performed:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[e IS friends]-> (b IS person WHERE b.name=:name)
WHERE a.name='John'
COLUMNS (a.name AS person_a,
b.name AS person_b,
e.meeting_date AS met_on)
);
The code produces the following output:
A B MET_ON
---------- ---------- ---------
John Bob 01-SEP-00
Example 5-17 Query Invoking a PL/SQL function
Inside an Expression and in the COLUMNS
Clause
The example declares a user defined function(UDF) as shown:
CREATE OR REPLACE FUNCTION get_age(
id NUMBER
)
RETURN NUMBER
AS
age NUMBER := 0;
BEGIN
-- get age
SELECT (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from birthdate))
INTO age
FROM persons
WHERE person_id=id;
-- return age
RETURN age;
END;
/
Function created.
The following query invokes the UDF inside an expression in the
WHERE
clause and again in the COLUMNS
clause:
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person) -[e IS friends]-> (b IS person)
WHERE (get_age(a.person_id) > 50)
COLUMNS (a.name AS a,
get_age(a.person_id) AS age,
b.name AS b,
e.meeting_date AS met_on)
);
The code produces the following output:
A AGE B MET_ON
---------- ---------- ---------- ---------
John 60 Bob 01-SEP-00
Bob 57 Mary 10-JUL-01
Example 5-18 Query Using
SCN
Determine the current SCN
value of the database as
shown:
SQL> SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
2117789
The following query using the preceding SCN
value as
shown:
SELECT * FROM GRAPH_TABLE (students_graph AS OF SCN 2117789
MATCH
(a IS person) -[e]-> (b IS person)
COLUMNS (a.name AS a, b.name AS b, e.meeting_date AS met_on)
);
The query produces the following output:
A B MET_ON
---------- ---------- ---------
Mary John 19-SEP-00
Bob Mary 10-JUL-01
John Bob 01-SEP-00
Mary Alice 19-SEP-00
Example 5-19 Query Using
TIMESTAMP
The following query uses a TIMESTAMP
value as
shown:
SQL> SELECT * FROM GRAPH_TABLE (students_graph AS OF TIMESTAMP SYSTIMESTAMP
MATCH
(a IS person WHERE a.name='John') -[e]-> (b IS person)
COLUMNS (a.name AS a, b.name AS b, e.meeting_date AS met_on)
);
The query produces the following output:
A B MET_ON
---------- ---------- ---------
John Bob 01-SEP-00
Example 5-20 Query Using the
VERTEX_ID
and EDGE_ID
Identifiers
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person ) -[e IS friends]-> (b IS person)
COLUMNS (JSON_SERIALIZE(VERTEX_ID(a)) AS id_a , JSON_SERIALIZE(EDGE_ID(e)) AS id_e)
);
The query produces a JSON data type output that includes the graph owner, graph name and graph element table name and the key value as shown:
ID_A ID_E
--------------------------- ------------------------------
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":1}} ":{"FRIENDSHIP_ID":1}}
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":2}} ":{"FRIENDSHIP_ID":2}}
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":2}} ":{"FRIENDSHIP_ID":3}}
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":3}} ":{"FRIENDSHIP_ID":4}}
Example 5-21 Query Using the
VERTEX_EQUAL
Predicate
SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person WHERE a.name='John') -[e IS friends]->{,1} (b IS person)
WHERE VERTEX_EQUAL(a,b)
COLUMNS (JSON_SERIALIZE(VERTEX_ID(a)) AS id_a , JSON_SERIALIZE(VERTEX_ID(b)) AS id_b)
);
The query produces a JSON data type output that includes the graph owner, graph name and graph element table name and the key value as shown:
ID_A ID_B
--------------------------- ---------------------------
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER",
"GRAPH_NAME":"STUDENTS_GRAP "GRAPH_NAME":"STUDENTS_GRAP
H","ELEM_TABLE":"PERSONS"," H","ELEM_TABLE":"PERSONS","
KEY_VALUE":{"PERSON_ID":1}} KEY_VALUE":{"PERSON_ID":1}}
Parent topic: SQL Graph Queries