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: Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement:
Example: 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 AliceExample: 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 MaryExample: 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 MaryExample: 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 AliceExample: 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 ABCExample: 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 AliceExample: 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 BobExample: 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: 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.7Example: Query Using IS LABELED Predicate
The following query finds all outgoing FRIENDS and STUDENT_OF edges from a person named Bob. It returns the label of the destination vertex along with the ID and NAME properties of person and university labels. The IS LABELED predicate is used to construct the output label as well as to combine the person’s PERSON_ID property and the university’s ID property into a single ID column.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[IS friends|student_of]-> (x IS person|university)
WHERE p1.name = 'Bob'
COLUMNS (CASE
WHEN x IS LABELED person THEN 'PERSON'
ELSE 'UNIVERSITY'
END AS label,
CASE
WHEN x IS LABELED person THEN x.person_id
ELSE x.id
END AS id,
x.name))
ORDER BY label, id;The query returns the following output:
LABEL ID NAME
---------- ---------- ----------
PERSON 2 Mary
UNIVERSITY 1 ABCExample: Query Using PROPERTY_EXISTS Predicate
The following example matches vertices labeled PERSON or UNIVERSITY and for each type of vertex returns whether it has properties - DOB, HEIGHT, NAME, and ID.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (x IS person|university)
COLUMNS (CASE
WHEN x IS LABELED person THEN 'PERSON'
ELSE 'UNIVERSITY'
END AS label,
PROPERTY_EXISTS(x, dob) AS has_dob,
PROPERTY_EXISTS(x, height) AS has_height,
PROPERTY_EXISTS(x, name) AS has_name,
PROPERTY_EXISTS(x, id) AS has_id))
GROUP BY label, has_dob, has_height, has_name, has_id
ORDER BY label;The query returns the following output:
LABEL HAS_DOB HAS_HEIGHT HAS_NAME HAS_ID
---------- ----------- ----------- ----------- -----------
PERSON TRUE TRUE TRUE FALSE
UNIVERSITY FALSE FALSE TRUE TRUEExample: 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 BobThe 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 BobThe 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 BobNote 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: 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 3The 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"]Using binding_count() Aggregate:
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. The example uses the binding_count aggregate to count the number of bindings on the friend element variable in the COLUMNS clause:
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 friends,
binding_count(friend) AS cnt,
LISTAGG(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from friend.dob),
',') AS age_list,
COUNT(e.friendship_id) AS path));The preceding code produces the following output:
FRIENDS CNT AGE_LIST PATH
-------------- --- -------------------- ----
Bob,Mary 2 58,42 2
Bob,Mary,Alice 3 58,42,37 3Example: Query Using ONE ROW PER MATCH Clause
The following example query uses ONE ROW PER MATCH and finds all friends path with length between 0 and 3 starting from a person named John.
SELECT * FROM GRAPH_TABLE ( students_graph
MATCH (n1 IS person) -[e1 IS friends]->{0,3} (n2 IS person)
WHERE n1.name = 'John'
ONE ROW PER MATCH
COLUMNS (LISTAGG(e1.friendship_id, ', ') AS friendship_ids, n2.name)
);The example shows the following output from five paths that were matched, and the corresponding name of the person in that specific path. Note that the empty path (zero friendship_ids) is bound to a single person named John.
FRIENDSHIP_IDS NAME
-------------------- ----------
John
1 Bob
1, 4 Mary
1, 4, 2 Alice
1, 4, 3 JohnExample: Query Using ONE ROW PER VERTEX Clause
The following example query uses ONE ROW PER VERTEX and finds all friends path with length between 0 and 3 starting from a person named John. The query outputs one row per vertex.
SELECT * FROM GRAPH_TABLE ( students_graph
MATCH (n IS person) -[e1 IS friends]->{0,3} (IS person)
WHERE n.name = 'John'
ONE ROW PER VERTEX (v)
COLUMNS (LISTAGG(e1.friendship_id, ', ') AS friendship_ids,
v.name)
);The example produces the following output:
FRIENDSHIP_IDS NAME
--------------- ------
John
1 John
1 Bob
1, 4 John
1, 4 Bob
1, 4 Mary
1, 4, 2 John
1, 4, 2 Bob
1, 4, 2 Mary
1, 4, 2 Alice
1, 4, 3 John
1, 4, 3 Bob
1, 4, 3 Mary
1, 4, 3 John
14 rows selected.The preceding output shows data from five paths that were matched:
-
The empty path (zero
friendship_ids) contains a single person (John). -
The path with
friendship_ids1 contains two persons (John and Bob). -
The path with
friendship_ids1, 4 contains three persons (John, Bob, and Mary). -
The path with
friendship_ids1, 4, 2 contains four persons (John, Bob, Mary, and Alice). -
The path with
friendship_ids1, 4, 3 contains four persons and this forms a cycle (John, Bob, Mary, and John).
The following example query matches paths between universities ABC and XYZ such that the paths consist of an incoming student_of edge, followed by one or two friends edges, and finally followed by an outgoing student_of edge. The query returns one row per vertex and for each row it returns the match number, the element number, the type of the vertex (either person or university), as well as the name of the university or the person.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (u1 IS university)
<-[IS student_of]- (p1 IS person)
-[IS friends]-{1,2} (p2 IS person)
-[IS student_of]-> (u2 IS university)
WHERE u1.name = 'ABC' AND u2.name = 'XYZ'
ONE ROW PER VERTEX (v)
COLUMNS (MATCHNUM() AS matchnum,
ELEMENT_NUMBER(v) AS element_number,
CASE WHEN v.person_id IS NOT NULL
THEN 'person'
ELSE 'university'
END AS label,
v.name))
ORDER BY matchnum, element_number;The example produces the following output. Note that a total of 6 paths were matched with match numbers 1, 2, 3, 4, 6 and 8. Each path has university ABC as the first vertex and university XYZ as the last vertex. Furthermore, paths with match numbers 1 and 3 contain two person vertices while the other paths (match numbers 2, 4, 6 and 8) contain three person vertices.
MATCHNUM ELEMENT_NUMBER LABEL NAME
---------- -------------- ---------- ----------
1 1 university ABC
1 3 person John
1 5 person Mary
1 7 university XYZ
2 1 university ABC
2 3 person Bob
2 5 person John
2 7 person Mary
2 9 university XYZ
3 1 university ABC
3 3 person Bob
3 5 person Mary
3 7 university XYZ
4 1 university ABC
4 3 person John
4 5 person Mary
4 7 person Alice
4 9 university XYZ
6 1 university ABC
6 3 person John
6 5 person Bob
6 7 person Mary
6 9 university XYZ
8 1 university ABC
8 3 person Bob
8 5 person Mary
8 7 person Alice
8 9 university XYZ
28 rows selected.Example: Query Using ONE ROW PER STEP Clause
The following example query uses ONE ROW PER STEP and finds all friends path with length between 0 and 3 starting from a person named John. The query outputs one row per step.
SELECT * FROM GRAPH_TABLE ( students_graph
MATCH (n IS person) -[e1 IS friends]->{0,3} (IS person)
WHERE n.name = 'John'
ONE ROW PER STEP (src, e2, dst)
COLUMNS (LISTAGG(e1.friendship_id, ', ') AS friendship_ids,
src.name AS src_name,
e2.friendship_id,
dst.name AS dst_name)
);The example produced the following output:
FRIENDSHIP_IDS SRC_NAME FRIENDSHIP_ID DST_NAME
--------------- ---------- ------------- ----------
John
1 John 1 Bob
1, 4 John 1 Bob
1, 4 Bob 4 Mary
1, 4, 2 John 1 Bob
1, 4, 2 Bob 4 Mary
1, 4, 2 Mary 2 Alice
1, 4, 3 John 1 Bob
1, 4, 3 Bob 4 Mary
1, 4, 3 Mary 3 John
10 rows selected.The preceding output shows data from five paths that were matched:
-
The empty path (zero
friendship_ids) has a single step in which iterator vertex variablesrcis bound to the vertex corresponding to the person named John, while iterator edge variablee2and iterator vertex variabledstare not bound, resulting in NULL values forFRIENDSHIP_IDSandDST_NAME. -
The path with
friendship_ids1 has a single step since it has a single edge. In this step, iterator vertex variablesrcis bound to the vertex corresponding to John, iterator edge variablee2is bound to the edge withfriendship_id1, and iterator vertex variabledstis bound to the vertex corresponding to Bob. -
The path with
friendship_ids1, 4 has two steps since it has two edges. -
The path with
friendship_ids1, 4, 3 has three steps since it has three edges. -
The path with
friendship_ids1, 4, 2 again has three steps since it has three edges.
Example: Query Using IN Path Clause within ONE ROW PER VERTEX
The following query matches paths between universities ABC and XYZ, and contains three path patterns. The first path pattern matches an incoming student_of edge, the second path pattern match one or two friends edges, and the third path pattern matches again a student_of edge. The query returns one row per vertex in the second path. This path contains only person vertices. For each vertex, the query returns the match number, the path name, the element number, and all the vertex properties.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH path1 = (u1 IS university) <-[IS student_of]- (p1 IS person),
path2 = (p1) -[IS friends]-{1,2} (p2 IS person),
path3 = (p2) -[IS student_of]-> (u2 IS university)
WHERE u1.name = 'ABC' AND u2.name = 'XYZ'
ONE ROW PER VERTEX (v) IN (path2)
COLUMNS (MATCHNUM() AS matchnum,
PATH_NAME() AS path_name,
ELEMENT_NUMBER(v) AS element_number,
v.*))
ORDER BY matchnum, element_numberThe preceding query output is as follows:
MATCHNUM PATH_NAME ELEMENT_NUMBER PERSON_ID NAME DOB HEIGHT ID
-------- --------- -------------- --------- ----- --------- --------- --
1 PATH2 1 1 John 13-JUN-63 1.8
1 PATH2 3 2 Mary 25-SEP-82 1.65
2 PATH2 1 3 Bob 11-MAR-66 1.75
2 PATH2 3 1 John 13-JUN-63 1.8
2 PATH2 5 2 Mary 25-SEP-82 1.65
3 PATH2 1 3 Bob 11-MAR-66 1.75
3 PATH2 3 2 Mary 25-SEP-82 1.65
4 PATH2 1 1 John 13-JUN-63 1.8
4 PATH2 3 2 Mary 25-SEP-82 1.65
4 PATH2 5 4 Alice 01-FEB-87 1.7
6 PATH2 1 1 John 13-JUN-63 1.8
6 PATH2 3 3 Bob 11-MAR-66 1.75
6 PATH2 5 2 Mary 25-SEP-82 1.65
8 PATH2 1 3 Bob 11-MAR-66 1.75
8 PATH2 3 2 Mary 25-SEP-82 1.65
8 PATH2 5 4 Alice 01-FEB-87 1.7As seen in the preceding output, a total of 6 paths are matched with match numbers 1, 2, 3, 4, 6 and 8. Paths with match numbers 1 and 3 contain two person vertices while the other paths (match numbers 2, 4, 6 and 8) contain three person vertices. The all properties reference v.* expands to properties PERSON_ID, NAME, DOB, HEIGHT and ID. Thus, even though person vertices do not have property ID (only university vertices do), the expansion still includes property ID because an all properties reference with an iterator variable always expands to either all vertex properties or all edge properties in the graph based on the iterator variable type.
Example: Query Using MATCHNUM Function
The following query finds paths connecting John and Mary either directly or indirectly through a common friend. For each match, the query returns one row per vertex, which means one row per person along the friendship path. Each result contains a match number, the element number of the person vertex, and the name of the person.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[IS friends]-{1,2} (p2 IS person)
WHERE p1.name = 'John' AND p2.name = 'Mary'
ONE ROW PER VERTEX (v)
COLUMNS (MATCHNUM() AS matchnum,
ELEMENT_NUMBER(v) AS element_number,
v.name))
ORDER BY matchnum, element_numberThe output for the preceding query is as shown:
MATCHNUM ELEMENT_NUMBER NAME
---------- -------------- ----------
1 1 John
1 3 Mary
2 1 John
2 3 Bob
2 5 MaryExample: Query Using ELEMENT_NUMBER Function
The following query finds paths connecting John and Mary either directly or indirectly through a common friend. For each match, the query returns one row per step. Each result contains a match number, the element number of the friends edge in the step, the friendship_id, and the names of the two persons in the step.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[IS friends]-{1,2} (p2 IS person)
WHERE p1.name = 'John' AND p2.name = 'Mary'
ONE ROW PER STEP (v1, e, v2)
COLUMNS (MATCHNUM() AS matchnum,
ELEMENT_NUMBER(e) AS element_number,
v1.name AS name1,
e.friendship_id,
v2.name AS name2))
ORDER BY matchnum, element_number;The output for the preceding query is as shown:
MATCHNUM ELEMENT_NUMBER NAME1 FRIENDSHIP_ID NAME2
---------- -------------- ---------- ------------- ----------
1 2 John 3 Mary
2 2 John 1 Bob
2 4 Bob 4 MaryThe following query finds all people connected to John through 0 or 1 friends edges. For each match, the query returns one row per step. Each result contains a match number, the element number of the friends edge in the step, the friendship_id, and the names of the two persons in the step.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[IS friends]-{0,1} (p2 IS person)
WHERE p1.name = 'John'
ONE ROW PER STEP (v1, e, v2)
COLUMNS (MATCHNUM() AS matchnum,
ELEMENT_NUMBER(e) AS element_number,
v1.name AS name1,
e.friendship_id,
v2.name AS name2))
ORDER BY matchnum, element_number;The query produced the following output:
MATCHNUM ELEMENT_NUMBER NAME1 FRIENDSHIP_ID NAME2
---------- -------------- ---------- ------------- ----------
1 John
2 2 John 3 Mary
4 2 John 1 BobAs seen in the preceding output, three paths were matched. The path with match number 1 has one vertex and zero edges. Thus, there is a single step in which the iterator vertex variable v1 is bound but the iterator edge variable e and iterator vertex variable v2 are not bound, leading to the NULL values in the ELEMENT_NUMBER, FRIENDSHIP_ID, and NAME2 columns. The other two paths (with match numbers 2 and 4) also have a single step, but since these paths do contain an edge as well as a second vertex, all three iterator variables are bound, and no NULL values are returned.
Example: Query Using PATH_NAME Function
The following query finds friends of friends of Bob and the universities they attend. The graph pattern is split into two path patterns named PATH1 and PATH2. For each match, PATH1 always matches 3 vertices while PATH2 always matches 2 vertices. Therefore, since the query specifies ONE ROW PER VERTEX, a total of 5 rows are returned for each match. Each row includes the match number, the path name, the element number and the NAME property of the vertices.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH path1 = (p1 IS person) -[IS friends]-{2} (p2 IS person),
path2 = (p2) -[IS student_of]-> (u2 IS university)
WHERE p1.name = 'Bob'
ONE ROW PER VERTEX (v)
COLUMNS (MATCHNUM() AS matchnum,
PATH_NAME() AS path_name,
ELEMENT_NUMBER(v) AS element_number,
v.name))
ORDER BY matchnum, path_name, element_number;The preceding query output is as follows:
MATCHNUM PATH_NAME ELEMENT_NUMBER NAME
---------- --------- -------------- ----------
1 PATH1 1 Bob
1 PATH1 3 John
1 PATH1 5 Mary
1 PATH2 1 Mary
1 PATH2 3 XYZ
2 PATH1 1 Bob
2 PATH1 3 John
2 PATH1 5 Bob
2 PATH2 1 Bob
2 PATH2 3 ABC
3 PATH1 1 Bob
3 PATH1 3 Mary
3 PATH1 5 Bob
3 PATH2 1 Bob
3 PATH2 3 ABC
4 PATH1 1 Bob
4 PATH1 3 Mary
4 PATH1 5 John
4 PATH2 1 John
4 PATH2 3 ABC
5 PATH1 1 Bob
5 PATH1 3 Mary
5 PATH1 5 Alice
5 PATH2 1 Alice
5 PATH2 3 XYZExample: 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-00Example: 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-01Example: 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)
-------------------------
2117789The 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-00Example: 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-00Example: 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: 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}}Example: Query Using the IS SOURCE OF and IS DESTINATION OF Predicates
The following query matches FRIENDS edges that are either incoming or outgoing from Mary. For each edge, it return the NAME property for the source of the edge as well as the NAME property for the destination of the edge.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[e IS friends]- (p2 IS person)
WHERE p1.name = 'Mary'
COLUMNS (e.friendship_id,
e.meeting_date,
CASE WHEN p1 IS SOURCE OF e THEN p1.name ELSE p2.name END AS from_person,
CASE WHEN p1 IS DESTINATION OF e THEN p1.name ELSE p2.name END AS to_person))
ORDER BY friendship_id;FRIENDSHIP_ID MEETING_DATE FROM_PERSON TO_PERSON
------------- ------------ ----------- ---------
2 19-SEP-00 Mary Alice
3 19-SEP-00 Mary John
4 10-JUL-01 Bob MaryExample: Queries Fetching All the Vertex and Edge Properties
The following query matches all FRIENDS edges between two persons P1 and P2 and uses all properties references P1.* and E.* to retrieve all the properties of vertex P1 as well as all properties of edge E.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[e IS friends]-> (p2 IS person)
COLUMNS ( p1.*, p2.name AS p2_name, e.* )
)
ORDER BY 1, 2, 3, 4, 5;Note that the following result for P1.* includes properties PERSON_ID, NAME and DOB of label PERSON as well as property HEIGHT of label PERSON_HT. Furthermore, the result for E.* includes properties FRIENDSHIP_ID and MEETING_DATE of label FRIENDS.
PERSON_ID NAME DOB HEIGHT P2_NAME FRIENDSHIP_ID MEETING_D
---------- ---------- --------- ---------- ---------- ------------- ---------
1 John 13-JUN-63 1.8 Bob 1 01-SEP-00
2 Mary 25-SEP-82 1.65 Alice 2 19-SEP-00
2 Mary 25-SEP-82 1.65 John 3 19-SEP-00
3 Bob 11-MAR-66 1.75 Mary 4 10-JUL-01The following query matches all vertices in the graph and retrieves all their properties:
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (v)
COLUMNS ( v.* )
)
ORDER BY 1, 2, 3, 4, 5;The query produces the following result. Note that since the PERSON vertices do not have an ID property, the query returns NULL values (empty strings). Similarly, UNIVERSITY vertices do not have PERSON_ID, DOB and HEIGHT properties, and so again the query returns NULL values (empty strings).
PERSON_ID NAME DOB HEIGHT ID
---------- ---------- --------- ---------- ----------
1 John 13-JUN-63 1.8
2 Mary 25-SEP-82 1.65
3 Bob 11-MAR-66 1.75
4 Alice 01-FEB-87 1.7
ABC 1
XYZ 2
6 rows selected.Example: Query Using the IS SOURCE OF and IS DESTINATION OF Predicates
The following query matches FRIENDS edges that are either incoming or outgoing from Mary. For each edge, it returns the NAME property for the source of the edge as well as the NAME property for the destination of the edge.
SELECT *
FROM GRAPH_TABLE ( students_graph
MATCH (p1 IS person) -[e IS friends]- (p2 IS person)
WHERE p1.name = 'Mary'
COLUMNS (e.friendship_id,
e.meeting_date,
CASE WHEN p1 IS SOURCE OF e THEN p1.name ELSE p2.name END AS from_person,
CASE WHEN p1 IS DESTINATION OF e THEN p1.name ELSE p2.name END AS to_person))
ORDER BY friendship_id;FRIENDSHIP_ID MEETING_DATE FROM_PERSON TO_PERSON
------------- ------------ ----------- ---------
2 19-SEP-00 Mary Alice
3 19-SEP-00 Mary John
4 10-JUL-01 Bob MarySetting Up Sample Data in the Database
In order to create the SQL property graph, students_graph, shown in Creating a SQL Property Graph, the following sample tables with data need to be set up in the database.
-
Connect to the database as the schema user.
-
Run the following SQL script to create the
university,persons,students, andfriendshipstables with sample data in the database.CREATE TABLE university ( id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), name VARCHAR2(10), CONSTRAINT u_pk PRIMARY KEY (id)); INSERT INTO university (name) VALUES ('ABC'); INSERT INTO university (name) VALUES ('XYZ'); CREATE TABLE persons ( person_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), name VARCHAR2(10), birthdate DATE, height FLOAT DEFAULT ON NULL 0, hr_data JSON, CONSTRAINT person_pk PRIMARY KEY (person_id) ); INSERT INTO persons (name, height, birthdate, hr_data) VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'), '{"department":"IT","role":"Software Developer"}'); INSERT INTO persons (name, height, birthdate, hr_data) VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Manager"}'); INSERT INTO persons (name, height, birthdate, hr_data) VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'), '{"department":"IT","role":"Technical Consultant"}'); INSERT INTO persons (name, height, birthdate, hr_data) VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Assistant"}'); CREATE TABLE student_of ( s_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), s_univ_id NUMBER, s_person_id NUMBER, subject VARCHAR2(10), CONSTRAINT stud_pk PRIMARY KEY (s_id), CONSTRAINT stud_fk_person FOREIGN KEY (s_person_id) REFERENCES persons(person_id), CONSTRAINT stud_fk_univ FOREIGN KEY (s_univ_id) REFERENCES university(id) ); INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,1,'Arts'); INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,3,'Music'); INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,2,'Math'); INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,4,'Science'); CREATE TABLE friends ( friendship_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), person_a NUMBER, person_b NUMBER, meeting_date DATE, CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES persons(person_id), CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES persons(person_id), CONSTRAINT fs_pk PRIMARY KEY (friendship_id) ); INSERT INTO friends (person_a, person_b, meeting_date) VALUES (1, 3, to_date('01/09/2000', 'DD/MM/YYYY')); INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 4, to_date('19/09/2000', 'DD/MM/YYYY')); INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 1, to_date('19/09/2000', 'DD/MM/YYYY')); INSERT INTO friends (person_a, person_b, meeting_date) VALUES (3, 2, to_date('10/07/2001', 'DD/MM/YYYY'));