Public Preview of Shortest Path on SQL Server 2019

Shreya Verma - Thanks for introducing to the new features from SQL Graph DB, I have been trying to use this in my latest project for storing hierarchy / tree structures where we join multiple individual hierarchies to create a combined tree structure with intersections at leaf levels from one or more hierarchies to store the Cartesian product by only creating a separate Edge Table to store that relationship, which is very powerful.

While working with my example of creating separate Edge Table to store  combined tree structure, I have observed limitations of SHORTEST_PATH, in my use case when we have multiple parent nodes for same child node, SHORTEST_PATH is not able to produce output from all possible child nodes, it will find path to the last node for only first iteration of Parent to Child, please see my example below

In this example I have 2 tree structures of "Region" and "Vehicles", purpose is to create "Sales by Region" hierarchy

When SHORTEST_PATH query is executed, I am expecting to get full path from all nodes of Region to all possible nodes of Vehicles, but the path stops at the first region of "Illinois" and does not show data for "Wisonsin"

Please suggest any corrections which can help generate full path  .. many thanks

---Create Sample Schema
CREATE SCHEMA graph
GO

--Tree Nodes
CREATE TABLE graph.Tree_Nodes
(
	tree_id bigint
	,tree_parent bigint
	,tree_name NVARCHAR(50)
) AS NODE


--Combined Tree Edges
CREATE TABLE graph.Combined_Tree_Edges
(
	tree_id bigint
) AS EDGE

--Tree Insert
INSERT INTO graph.Tree_Nodes VALUES (1,null,'Region')
INSERT INTO graph.Tree_Nodes VALUES (2,1,'USA')
INSERT INTO graph.Tree_Nodes VALUES (3,2,'Illinois')
INSERT INTO graph.Tree_Nodes VALUES (4,2,'Wisonsin')
INSERT INTO graph.Tree_Nodes VALUES (5,null,'Vehicles')
INSERT INTO graph.Tree_Nodes VALUES (6,5,'Cars')
INSERT INTO graph.Tree_Nodes VALUES (7,5,'Trucks')
INSERT INTO graph.Tree_Nodes VALUES (8,6,'BMW')
INSERT INTO graph.Tree_Nodes VALUES (9,6,'Merc')
INSERT INTO graph.Tree_Nodes VALUES (10,6,'Tesla')
INSERT INTO graph.Tree_Nodes VALUES (11,7,'Volvo')
INSERT INTO graph.Tree_Nodes VALUES (12,7,'Daimler')


--Create Edge relations on graph.Combined_Tree_Edges
INSERT INTO graph.Combined_Tree_Edges ($from_id, $to_id,tree_id) 
select	 ParentFromNode.$node_id
		,ChildToNode.$node_id
		,ChildToNode.tree_id
from graph.Tree_Nodes ParentFromNode 
INNER JOIN graph.Tree_Nodes ChildToNode ON ChildToNode.tree_parent=ParentFromNode.tree_id
WHERE ChildToNode.tree_id IN (2,3,4,6,7,8,9,10,11,12)

--Intersection Edges
INSERT INTO graph.Combined_Tree_Edges ($from_id, $to_id,tree_id) 
VALUES
		(
			(SELECT $node_id FROM graph.Tree_Nodes WHERE tree_id=3) --Illinois
			,(SELECT $node_id FROM graph.Tree_Nodes WHERE tree_id=5) --Vehicles
			,3
		)

INSERT INTO graph.Combined_Tree_Edges ($from_id, $to_id,tree_id) 
VALUES
		(
			(SELECT $node_id FROM graph.Tree_Nodes WHERE tree_id=4) --Wisonsin
			,(SELECT $node_id FROM graph.Tree_Nodes WHERE tree_id=5) --Vehicles
			,4
		)


--Final Select Query
SELECT
	Parent.tree_id AS ParentId,
	Parent.tree_name AS ParentName,
	STRING_AGG(CAST(Child.tree_id AS NVARCHAR(10)), ' | ') WITHIN GROUP (GRAPH PATH) AS PathIds,
	STRING_AGG(CAST(Child.tree_name AS NVARCHAR(10)), ' | ') WITHIN GROUP (GRAPH PATH) AS PathNames,
	LAST_VALUE(Child.tree_parent) WITHIN GROUP (GRAPH PATH) AS LastNodeParentId,
	LAST_VALUE(Child.tree_id) WITHIN GROUP (GRAPH PATH) AS LastNodeId,
	LAST_VALUE(Child.tree_name) WITHIN GROUP (GRAPH PATH) AS LastNodeName
FROM
	graph.Tree_Nodes AS Parent,
	graph.Combined_Tree_Edges FOR PATH AS ChildOf,
	graph.Tree_Nodes FOR PATH  AS Child
WHERE MATCH(SHORTEST_PATH(Parent(-(ChildOf)->Child)+))
AND Parent.tree_id IN (1) ---Region

/*
--Cleanup
DROP TABLE graph.Combined_Tree_Edges
GO

DROP TABLE graph.Tree_Nodes
GO

DROP SCHEMA graph
GO

*/

Final Select Output

Excel Example