6.11 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 5-1:

Example 6-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 6-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 6-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 6-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 6-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 6-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 6-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 6-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 6-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 6-14 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 ABC

Example 6-15 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        TRUE

Example 6-16 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 6-17 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"]

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                3

Example 6-18 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              John

Example 6-19 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_ids 1 contains two persons (John and Bob).
  • The path with friendship_ids 1, 4 contains three persons (John, Bob, and Mary).
  • The path with friendship_ids 1, 4, 2 contains four persons (John, Bob, Mary, and Alice).
  • The path with friendship_ids 1, 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 6-20 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 variable src is bound to the vertex corresponding to the person named John, while iterator edge variable e2 and iterator vertex variable dst are not bound, resulting in NULL values for FRIENDSHIP_IDS and DST_NAME.
  • The path with friendship_ids 1 has a single step since it has a single edge. In this step, iterator vertex variable src is bound to the vertex corresponding to John, iterator edge variable e2 is bound to the edge with friendship_id 1, and iterator vertex variable dst is bound to the vertex corresponding to Bob.
  • The path with friendship_ids 1, 4 has two steps since it has two edges.
  • The path with friendship_ids 1, 4, 3 has three steps since it has three edges.
  • The path with friendship_ids 1, 4, 2 again has three steps since it has three edges.

Example 6-21 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_number

The 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.7

As 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 6-22 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_number

The 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 Mary

Example 6-23 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 Mary

The 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 Bob

As 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 6-24 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 XYZ

Example 6-25 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 6-26 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 6-27 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 6-28 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 6-29 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 6-30 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 6-31 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         Mary

Example 6-32 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-01

The 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 6-33 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         Mary

Parent topic: SQL Graph Queries

6.11.1 Setting 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.
  1. Connect to the database as the schema user.
  2. Run the following SQL script to create the university, persons, students, and friendships tables 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'));

Parent topic: Examples for SQL Graph Queries