Graph Database "SHORTEST_PATH" with edge filtering

Yam Ng 6 Reputation points
2021-10-06T05:50:07.563+00:00

Hi all,

Currently I am trying to use the Graph DB functionality,

which in case my node table has an ID column:
Node Table Schema:

  1. nodeId

And Edge table has below Schema:
Edge Table Schema:

  1. startDate
  2. endDate

Which denote the start/end date for the edge relationship.

My question is that when using SHORTEST_PATH function, it seems there is no provided method for using edge which is effective currently (i.e., today >= startDate && today < endDate), may I ask if have any solution for the mentioned issue?

My SQL query:

select node_1.nodeId  
   STRING_AGG(node_2.nodeId, '->') WITHIN GROUP (GRAPH PATH) AS node_path,  
   LAST_VALUE(node_2.nodeId) WITHIN GROUP (GRAPH PATH) AS descendant_node  
from node_tb as node_1,  
 edge_tb for PATH as edge,  
 node_tb FOR PATH as node_2  
where match(SHORTEST_PATH(node_1(-(edge)->node_2)+))  

Thanks a lot.

-----------------------------------------------------------------------

Updates (2021/10/07):

Using the example from (with a bit amendment): https://learn.microsoft.com/en-us/sql/t-sql/queries/match-sql-graph?view=sql-server-ver15

CREATE TABLE dbo.Person (ID INTEGER PRIMARY KEY, name VARCHAR(50)) AS NODE;  
CREATE TABLE dbo.friend (start_date DATE, end_date Date) AS EDGE;  
  
-- Insert into node table  
INSERT INTO dbo.Person VALUES (1, 'Alice');  
INSERT INTO dbo.Person VALUES (2,'John');  
INSERT INTO dbo.Person VALUES (3, 'Jacob');  
INSERT INTO dbo.Person VALUES (4, 'Tom');  
  
-- Insert into edge table  
INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),  
       (SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'), '20210901', '20211001');  

INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),  
       (SELECT $node_id FROM dbo.Person WHERE name = 'John'), '20210901', NULL);  

INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'),  
       (SELECT $node_id FROM dbo.Person WHERE name = 'John'), '20210901', NULL);  

INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'John'),  
       (SELECT $node_id FROM dbo.Person WHERE name = 'Tom'), '20210901', NULL);  

I want to achieve to searched recursively for the person's social network at the current moment (i.e., what's are their friends and the person's friend at that moment, considering dbo.friend start_dt and end_dt)

Current node structure according to the above example:
138319-image.png

I can filter the edge using edge properties when I retrieve just one level of friend (using Alice as example):

select person_2.name,   
from Person as person_1,  
  friend as hasFriendship,  
  Person as person_2  
where match(person_1-(hasFriendship)->person_2)  
and person_1.name = 'Alice'  
and CURRENT_TIMESTAMP >= hasFriendship.start_dt   
and (CURRENT_TIMESTAMP < hasFriendship.end_dt or hasFriendship.end_dt is null)  

But considering the case of transitive closure syntax (which uses "SHORTEST_PATH" function), i.e.,

 select person_1.name,  
    STRING_AGG(person_2.name, '->') WITHIN GROUP (GRAPH PATH) AS node_path,  
    LAST_VALUE(person_2.name) WITHIN GROUP (GRAPH PATH) AS descendant_node  
 from Person as person_1,  
  friend for PATH as hasFriendship,  
  Person FOR PATH as person_2  
 where match(SHORTEST_PATH(person_1(-(hasFriendship)->person_2)+))  
 and person_1.name = 'Alice';  

I can't do the filer (i.e., where clause) for the edge table (i.e., friend table). Resulting error on finding "SHORTEST_PATH" based on dataset not considering friend start/end date.

May I ask if there are workarounds for the issue?

----------------------------------------------------

Expected Result:

I want to add the "edge properties filtering" in the query which do the transitive closure, i.e.,

     select person_1.name,  
        STRING_AGG(person_2.name, '->') WITHIN GROUP (GRAPH PATH) AS node_path,  
        LAST_VALUE(person_2.name) WITHIN GROUP (GRAPH PATH) AS descendant_node  
     from Person as person_1,  
      friend for PATH as hasFriendship,  
      Person FOR PATH as person_2  
     where match(SHORTEST_PATH(person_1(-(hasFriendship)->person_2)+))  
     and person_1.name = 'Alice'  
    --> and CURRENT_TIMESTAMP >= hasFriendship.start_dt   
    --> and (CURRENT_TIMESTAMP < hasFriendship.end_dt or hasFriendship.end_dt is null)  

But since the "SHORTEST_PATH" operation disallow the filtering, I can't find a way to achieve the goal without writing a recursion. I want to see I missed something from the doc in order to achieve my goal in 1 SQL query.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,311 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} vote