Oracle: SQL/PGQ

Create demonstration tables

create table N (
   id      char    (1) primary key,
   val     number  (2) not null,
   color   varchar2(6) not null
);

create table E (
   n1     not null references N,
   n2     not null references N,
   weight number(1)
);

Fill test data

begin
   insert into N values
     ('A', 34, 'red'    ),
     ('E', 28, 'blue'   ),
     ('G', 31, 'green'  ),
     ('M', 58, 'yellow' ),
     ('R', 45, 'orange' ),
     ('U', 36, 'purple' ),
     ('Z', 47, 'lime'   );
   
   insert into E values
     ('A', 'E', 5 ),
     ('E', 'G', 3 ),
     ('G', 'M', 4 ),
     ('M', 'Z', 3 ),
     ('A', 'R', 4 ),
     ('R', 'U', 3 ),
     ('U', 'Z', 3 ),
     ('U', 'E', 3 ),
     ('U', 'A', 3 );

end;
/

Create property graph

create property graph nodes_and_edges
   vertex tables (
        N
        key (id)
        properties (id, val)
   )
   edge tables (
        E
        key (n1, n2)
        source      key (n1) references N(id)
        destination key (n2) references N(id)
        properties      (n1, n2, weight)
   )
;     

Select from the property graph

select *
from
   graph_table ( nodes_and_edges
--    match (n_from)  - [e] -  (n_to)
      match (n_from) <- [e] -> (n_to)
      columns (
        n_from.id       as node_from,
        n_to.id         as node_to,
        e.weight        as edge_weight,
        n_to.val        as node_to_value
      )
   )
;

select *
from
   graph_table ( nodes_and_edges
      match (n_from) - [e] -> (n_to)
      columns (
        n_from.id       as node_from,
        n_to.id         as node_to,
        e.weight        as edge_weight,
        n_to.val        as node_to_value
      )
   )
;

select *
from
   graph_table ( nodes_and_edges
      match (n_from) <- [e] - (n_to)
      columns (
        n_from.id       as node_from,
        n_to.id         as node_to,
        e.weight        as edge_weight,
        n_to.val        as node_to_value
      )
   )
;

select *
from
   graph_table ( nodes_and_edges
      match (n_from) - [e1] -> (n_stop) - [e2] -> (n_to)
      columns (
        n_from.id   as from_id,
        e1.weight   as edge_1_weight,
        n_stop.id   as stop_id,
        e2.weight   as edge_2_weight,
        n_to.id     as to_id,
        n_to.val    as end_val
      )
   )
;

Property graphs are SQL statement creators

explain plan for …

select * from dbms_xplan.display();
--
-- ------------------------------------
-- | Id  | Operation           | Name |
-- ------------------------------------
-- |   0 | SELECT STATEMENT    |      |
-- |   1 |  HASH JOIN          |      |
-- |   2 |   HASH JOIN         |      |
-- |   3 |    TABLE ACCESS FULL| N    |
-- |   4 |    TABLE ACCESS FULL| E    |
-- |   5 |   TABLE ACCESS FULL | E    |
-- ------------------------------------

set serveroutput on

declare
  c clob;
begin
  dbms_utility.expand_sql_text(
    input_sql_text  => q'[

      select *
      from
         graph_table ( nodes_and_edges
            match (n_from) - [e1] -> (n_stop) - [e2] -> (n_to)
            columns (
              n_from.id   as from_id,
              e1.weight   as edge_1_weight,
              n_stop.id   as stop_id,
              e2.weight   as edge_2_weight,
              n_to.id     as to_id,
              n_to.val    as end_val
            )
         )

    ]',
    output_sql_text =>  c);

  dbms_output.put_line(c);
end;
/

SELECT
    "A1"."FROM_ID"       "FROM_ID",
    "A1"."EDGE_1_WEIGHT" "EDGE_1_WEIGHT",
    "A1"."STOP_ID"       "STOP_ID",
    "A1"."EDGE_2_WEIGHT" "EDGE_2_WEIGHT",
    "A1"."TO_ID"         "TO_ID",
    "A1"."END_VAL"       "END_VAL"
FROM
    (
        SELECT
            "A6"."ID"     "FROM_ID",
            "A5"."WEIGHT" "EDGE_1_WEIGHT",
            "A4"."ID"     "STOP_ID",
            "A3"."WEIGHT" "EDGE_2_WEIGHT",
            "A2"."ID"     "TO_ID",
            "A2"."VAL"    "END_VAL"
        FROM
            "RENE"."N" "A6",
            "RENE"."E" "A5",
            "RENE"."N" "A4",
            "RENE"."E" "A3",
            "RENE"."N" "A2"
        WHERE
                "A6"."ID" = "A5"."N1"
            AND "A4"."ID" = "A5"."N2"
            AND "A4"."ID" = "A3"."N1"
            AND "A2"."ID" = "A3"."N2"
    ) "A1"

Cleaning up

drop   property graph nodes_and_edges;
drop   table E;
drop   table N;

See also