In the post titled, Decomposing path into steps in Property Graphs, we looked at how ONE ROW PER STEP can be used to unnest / flatten a path in Graph. Now let’s explore how ONE ROW PER STEP can also be used to surface the connection types in the Graphs.
We will start with our Social Network Graph.
Note that Saqib is friends with Angela, whereas Angela and Scott are co-workers. Also Scott and Uroosa are friends.
Now let’s say we want to identify this connection type i.e. friend vs co-worker, at each step of the network. We can use ONE ROW PER STEP for this.
Let’s start with creating this dataset:
create table users (user_id number, user_name varchar(80));
create table connections (user1_id number, user2_id number, connection_type varchar(10));
insert into users values (1, 'Saqib'), (2, 'Angela'), (3, 'Scott'), (4, 'Uroosa');
insert into connections values (1, 2, 'friend'), (2, 3, 'co-worker'), (3, 4, 'friend');
Next let’s create a Property Graph for this:
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
);
Now we can use the ONE ROW PER STEP(v1, e, v2) clause which 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)
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, e.connection_type as connection_type)
);
Note the e.connection_type is getting the connection_type from the connections table that we had defined.
Here is the output of the above query:
