[SOLVED] SQL Developer Graph Visualization Extension for VS Code and Quantified path pattern

SQL Developer Graph Visualization Extension for VS Code and Quantified path pattern

I am trying to use the SQL Developer Graph Visualization Extension for VS Code to visualize a Graph where the relationship can be multi-hop defined using a Quantified path pattern:

select *
from GRAPH_TABLE(
match (c1 is customer) - [e is relationship]{1,10} -> (c2 is customer)
where c1.address_id = c2.address_id
columns (c1.name, c2.name)

However, this Visualization Extension requires the query must project vertex/edge IDs for the extension to work correctly.

How do I project the [e] edge of a variable length?

The following errors out:

select *
from GRAPH_TABLE(
match (c1 is customer) - [e is relationship]{1,10} -> (c2 is customer)
where c1.address_id = c2.address_id
columns (c1.name, c2.name, vertex_id(c1) as c1, vertex_id(c2) as c2 and edge_id(e) as e1);

Error:

ORA-40990: invalid reference of group variable E in the COLUMNS clause of a GRAPH_TABLE operator https://docs.oracle.com/error-help/db/ora-40990/
A group variable was referenced outside of the quantified path pattern in which it was declared

Solution

Figured it out. We have to unnest/flatten using ONE ROW PER STEP (v1, e, v2) clause:

select *
from GRAPH_TABLE(
match (c1 is customer) - [e_path is relationship]{1,10} -> (c2 is customer)
where c1.address_id = c2.address_id
ONE ROW PER STEP (v1, e, v2)
columns (c1.name, c2.name
  , vertex_id(v1) as v1
  , vertex_id(v2) as v2 
  , edge_id(e) as e1);