Decomposing path into steps in Property Graphs

[saqibali]

When working with Property Graphs, resolving the data path aka. decomposing Graph path is key to understanding the path traversed by a MATCH query in a SQL/PGQ. To facilitate this unnesting / flattening of path data, Oracle has introduced a new ROWS clause which can be used inside the GRAPH_TABLE operator for SQL Property Graph queries. This new clause can be placed directly before the COLUMNS clause to unnest/flatten path data. This syntax comes in three variations:

1. ONE ROW PER MATCH - produces one row in the result set per match returned by the MATCH clause
2. ONE ROW PER VERTEX(v) - produces row in the result set per vertex, specified by the vertex variable (in this case v)
3. ONE ROW PER STEP(v1, e, v2) - produces one row in the result set per vertex-edge-vertex triple, specified by the vertex and edge variables (in this case v1, e and v2)

The following is an example using ONE ROW PER STEP to decomposing the path data.

Let’s assume we have the following Friend-of-Friends network:

We know that Saqib is connected to Uroosa, but what the intermediary connections required to reach Uroosa? We can use ONE ROW PER STEP to answer this question.

Let’s start with some sample data:

create table users (user_id number, user_name varchar(80));
create table connections (user1_id number, user2_id number);

insert into users values (1, 'Saqib'), (2, 'Angela'), (3, 'Scott'), (4, 'Uroosa');
insert into connections values (1, 2), (2, 3), (3, 4);

Let’s create a Property Graph to capture the Friends-of-Friends relationships:

CREATE OR REPLACE PROPERTY GRAPH user_interaction_graph
  VERTEX TABLES (
    users KEY ( user_id ) PROPERTIES ARE ALL COLUMNS
)
  EDGE TABLES (
   connections AS is_connected_to
     key (user1_id, user2_id)
     SOURCE KEY ( user1_id ) REFERENCES users( user_id )
     DESTINATION KEY ( user2_id ) REFERENCES users( user_id )
     PROPERTIES ARE ALL COLUMNS

);

The following query shows how to use the ONE ROW PER STEP to get the data path

select *
from GRAPH_TABLE(user_interaction_graph
MATCH  (user1 is users) - [e1 is is_connected_to] ->{,10} (user2 is users)
where user1.user_name = 'Saqib' and user2.user_name = 'Uroosa'
--ONE ROW PER MATCH
ONE ROW PER STEP(v1, e, v2)
--ONE ROW PER VERTEX(e3) 
COLUMNS (user1.user_name as user1_name, user2.user_name as user2_name, v1.user_name as v1_user_name, v2.user_name as v2_user_name)
);

The output of the query shows that Saqib is connected to Angela, Angela is connected to Scott, and Scott is connected to Uroosa.