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. In this post we will look at the how to identify each match when there are multiple matches. Let’s start with our basic Social Network example. We will add a new path as following
We know that Saqib is connected to Uroosa, but what the intermediary connections required to reach Uroosa? Also there are two different paths. We would like to identify each of these match.
We can use the MATCHNUM Function along with ONE ROW PER STEP to unnest the path and then group them into unique Matches.
Let’s start with some sample data:
drop table if exists users;
drop table if exists connections;
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')
, (5, 'Fatima');
insert into connections values
(1, 2, 'friend')
, (2, 3, 'co-worker')
, (3, 4, 'friend')
, (1, 5, 'co-worker')
, (5, 4, 'friend');
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 along with MATCHNUM function to get the data path and the match identifier.
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 STEP(v1, e, v2)
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
--, element_number(v1) AS elementNumber
, matchnum() AS matchnum
)
);
This will output:
Note the MATCHNUM 3 and 4 in the query out. These are the Path Identifiers.
Note: The MATCHNUM function returns a number that uniquely identifies a match in a set of matches. MATCHNUM is an identifier, there is no guarantee that the numbers will be consecutive.