Re: SQL Property Graph Queries (SQL/PGQ)

Peter Eisentraut Sat, 13 Dec 2025 11:58:21 -0800

On 09.12.25 11:21, Ashutosh Bapat wrote:

It looks like undirected matching -[ ]- (without arrows) doesn't work
correctly.  It seems to just match in one direction.  I don't see any
tests.  Is this implemented?

-[]- is called full edge any direction
<full edge any direction> ::=
<minus left bracket> <element pattern filler> <right bracket minus>

~[]~ is defined as full edge undirected
<full edge undirected> ::=
<tilde left bracket> <element pattern filler> <right bracket tilde>

The patch supports full edge any direction since [1] in response to
Ajay Pal's report. Such a pattern matches edges in both the
directions. There's also a test
-- edges directed in both ways - to and from v2
SELECT * FROM GRAPH_TABLE (g1 MATCH (v1 IS vl2)-[conn]-(v2) COLUMNS
(v1.vname AS v1name, conn.ename AS cname, v2.vname AS v2name));
  v1name | cname | v2name
--------+-------+--------
  v21    | e122  | v12
  v22    | e121  | v11
  v22    | e231  | v32
(3 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (v1 IS vl2)-(v2) COLUMNS (v1.vname
AS v1name, v2.vname AS v2name));
  v1name | v2name
--------+--------
  v21    | v12
  v22    | v11
  v22    | v32
(3 rows)

it's matching edges to and from vertices in v2. For example e121 is an
edge from v11 to v22 whereas e231 is an edge from v22 to v32.

Attached is a test case from an Oracle web site (the URL is in thefile). At the end there are three queries, one left, one right, one anydirection. The latter should result in the union of the first two, butit doesn't.

-- from 
<https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/graph_table-operator.html>

CREATE TABLE university (
    id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR(10),
    CONSTRAINT u_pk PRIMARY KEY (id));
INSERT INTO university (name) VALUES ('ABC');
INSERT INTO university (name) VALUES ('XYZ');

CREATE TABLE persons (
     person_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 
1),
     name VARCHAR(10),
     birthdate DATE,
     height FLOAT DEFAULT 0,
     person_data JSON,
     CONSTRAINT person_pk PRIMARY KEY (person_id));

INSERT INTO persons (name, height, birthdate, person_data)
       VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'), 
'{"department":"IT","role":"Software Developer"}');

INSERT INTO persons (name, height, birthdate, person_data)
       VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'), 
'{"department":"HR","role":"HR Manager"}');

INSERT INTO persons (name, height, birthdate, person_data)
       VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'), 
'{"department":"IT","role":"Technical Consultant"}');

INSERT INTO persons (name, height, birthdate, person_data)
       VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'), 
'{"department":"HR","role":"HR Assistant"}');

CREATE TABLE students (
      s_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
      s_univ_id INTEGER,
      s_person_id INTEGER,
      subject VARCHAR(10),
      CONSTRAINT stud_pk PRIMARY KEY (s_id),
      CONSTRAINT stud_fk_person FOREIGN KEY (s_person_id) REFERENCES 
persons(person_id),
      CONSTRAINT stud_fk_univ FOREIGN KEY (s_univ_id) REFERENCES university(id)
    );


INSERT INTO students(s_univ_id, s_person_id,subject) VALUES (1,1,'Arts');
INSERT INTO students(s_univ_id, s_person_id,subject) VALUES (1,3,'Music');
INSERT INTO students(s_univ_id, s_person_id,subject) VALUES (2,2,'Math');
INSERT INTO students(s_univ_id, s_person_id,subject) VALUES (2,4,'Science');

CREATE TABLE friendships (
    friendship_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT 
BY 1),
    person_a INTEGER,
    person_b INTEGER,
    meeting_date DATE,
    CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES 
persons(person_id),
    CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES 
persons(person_id),
    CONSTRAINT fs_pk PRIMARY KEY (friendship_id)
);

INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (1, 3, 
to_date('01/09/2000', 'DD/MM/YYYY'));  -- John -> Bob
INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (2, 4, 
to_date('19/09/2000', 'DD/MM/YYYY'));  -- Mary -> Alice
INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (2, 1, 
to_date('19/09/2000', 'DD/MM/YYYY'));  -- Mary -> John
INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (3, 2, 
to_date('10/07/2001', 'DD/MM/YYYY'));  -- Bob -> Mary


CREATE PROPERTY GRAPH students_graph
  VERTEX TABLES (
    persons KEY (person_id)
      LABEL person
        PROPERTIES (person_id, name, birthdate AS dob)
      LABEL person_ht
        PROPERTIES (height),
    university KEY (id)
  )
  EDGE TABLES (
    friendships AS friends
      KEY (friendship_id)
      SOURCE KEY (person_a) REFERENCES persons(person_id)
      DESTINATION KEY (person_b) REFERENCES persons(person_id)
      PROPERTIES (friendship_id, meeting_date),
    students AS student_of
      SOURCE KEY (s_person_id) REFERENCES persons(person_id)
      DESTINATION KEY (s_univ_id) REFERENCES university(id)
      PROPERTIES (subject)
  );


SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (a IS person) -[e IS friends]-> (b IS person)
  WHERE a.name = 'John'
  COLUMNS (b.name)
);
-- result: Bob

SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (a IS person) <-[e IS friends]- (b IS person)
  WHERE a.name = 'John'
  COLUMNS (b.name)
);
-- result: Mary

SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (a IS person) -[e IS friends]- (b IS person)
  WHERE a.name = 'John'
  COLUMNS (b.name)
);
-- result: Mary, Bob

Re: SQL Property Graph Queries (SQL/PGQ)

Reply via email to