Graph Table Shape

Purpose

A graph table shape defines how the result of pattern matching should be transformed into tabular form. This is done through the graph_table_rows_clause and graph_table_columns_clause clauses.

Syntax

graph_table_shape::=

Description of the illustration graph_table_shape.gif

COLUMNS Clause

Rows Clause

COLUMNS Clause

Purpose

The COLUMNS clause allows for defining a projection that transforms the result of graph pattern matching into a regular table that no longer contains graph objects like vertices and edges but instead regular data values only.

Syntax

graph_table_columns_clause::=

Description of the illustration graph_table_columns_clause.gif

graph_table_column_definition::=

Description of the illustration graph_table_column_definition.gif

all_properties_reference::=

element_reference::=

Semantics

Syntactically, the COLUMNS clause starts with the keyword COLUMNS and is followed by an opening parenthesis, a comma-separated list of one or more graph_table_column_definition and a closing parenthesis.

A graph_table_column_definition defines either:

A single output column via an arbitrary value expression. The value expression may contain references to vertices and edges in the graph pattern, for example to access property values of vertices and edges. An optional alias, AS column_name provides a name for the column. The alias can only be omitted if the value expression is a property reference, in which case the alias defaults to the property name.
An all_properties_reference that expands to the set of all valid properties based on the element type (vertex or edge) and any label expression specified for the element. The set of properties is the union of properties of the vertex (or edge) labels belonging to tables that have at least one label that satisfies the label expression. In case some of these matching tables define a property while other tables do not, then NULL values will be returned for those tables that do not define the property.

An optional alias, AS column_name provides a name for the column. The alias can only be omitted if the value expression is a property reference, in which case the alias defaults to the property name.

Examples

Example 1

The following example returns the name of each person as well as the height in feet by multiplying the height in meters by 3.281:

SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (n IS person|person_ht)
  COLUMNS (n.name, n.height * 3.281 AS height_in_feet)
)
ORDER BY name;

In the query above, the COLUMNS clause defines two columns. Note that n.name is short for n.name AS name.

The result is:

NAME       HEIGHT_IN_FEET
---------- --------------
Alice	5.578
Bob	  5.742
John	 5.906
Mary	 5.414

Example 2

The following query matches all FRIENDS edges between two persons P1 and P2 and uses all properties references P1.* and E.* to retrieve all the properties of vertex P1 as well as all properties of edge E:

SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (p1 IS person) -[e IS friends]-> (p2 IS person)
  COLUMNS ( p1.*, p2.name AS p2_name, e.* )
)
ORDER BY 1, 2, 3, 4, 5;

The result is:

PERSON_ID NAME DOB       HEIGHT P2_NAME FRIENDSHIP_ID MEETING_D
--------- ---- --------- ------ ------- ------------- ---------
        1 John 13-JUN-63   1.8   Bob                1 01-SEP-00
        2 Mary 25-SEP-82   1.65  Alice              2 19-SEP-00
        2 Mary 25-SEP-82   1.65  John               3 19-SEP-00
        3 Bob  11-MAR-66   1.75  Mary               4 10-JUL-01

Note that the result for P1.* includes properties PERSON_ID, NAME and DOB of label PERSON as well as property HEIGHT of label PERSON_HT. Furthermore, the result for E.* includes properties FRIENDSHIP_ID and MEETING_DATE of label FRIENDS.

Example 3

The following query matches all vertices in the graph and retrieves all their properties:

SELECT *
FROM GRAPH_TABLE ( students_graph
  MATCH (v)
  COLUMNS ( v.* )
)
ORDER BY 1, 2, 3, 4, 5;

The result is:

 PERSON_ID NAME       DOB       HEIGHT     ID
---------- ---------- --------- ---------- ----------
         1 John       13-JUN-63        1.8
         2 Mary       25-SEP-82       1.65
         3 Bob        11-MAR-66       1.75
         4 Alice      01-FEB-87        1.7
           ABC                                      1
           XYZ                                      2

Note that since PERSON vertices do not have an ID property, NULL values (empty strings) are returned. Similarly, UNIVERSITY vertices do not have PERSON_ID, DOB and HEIGHT properties so again NULL values (empty strings) are returned.

Rows Clause

Purpose

The GRAPH_TABLE rows clause is used to specify how many rows should be returned from GRAPH_TABLE, based on the number of matches to the graph pattern or the number of vertices or steps in such matches.

Syntax

graph_table_rows_clause::=

one_row_per_iteration::=

in_paths_clause::=

graph_table_rows_clause

ONE ROW PER MATCH, the default, specifies that one row is returned per match to the graph pattern.
one_row_per_iteration declares one or more iterator variables and returns one row per iteration. In particular:
- ONE ROW PER VERTEX declares a single iterator vertex variable. It iterates through the vertices in paths and binds the iterator variable to different vertices in different iterations. For each path, it creates as many rows as there are vertices in the path. For example, if a pattern matches two paths, one with 3 vertices and another with 5 vertices, then a total of 8 rows are returned.
- ONE ROW PER STEP declares an iterator vertex variable, an iterator edge variable, and another iterator vertex variable. It iterates through the steps of the different paths. A step is a vertex-edge-vertex triple. If a path is non-empty and thus contains at least one edge and two vertices, then there are as many steps as there are edges and each iteration binds the iterator variables to the next edge and its source and destination in the path. However, if a path is empty and consists of a single vertex only, then the path has a single step and the first iterator vertex variable binds to that vertex, while the iterator edge variable and the second iterator vertex variable are not bound to any graph element.

An optional IN paths clause specifies one or more path variables referencing the paths that should be iterated through. If no IN paths clause is specified then all paths are iterated through.

When an all_properties_reference contains a reference to an iterator variable, then depending on the type of the iterator variable, it expands to either all vertex properties or to all edge properties in the graph. Note that label expressions for elements in the graph pattern are not considered when expanding the properties of an iterator variable.

See all_properties_reference::= of COLUMNS.

Restrictions

The graph_table_rows_clause clause is subject to the following restrictions:

Iterator element variables cannot be multiply declared. This means that an iterator variable may not be declared with the same name as a path variable or an element variable declared in the graph pattern, or as another iterator variable.
Iterator variables may only be referenced in the COLUMNS clause but not in the graph pattern or in the graph pattern WHERE clause.

The in_paths_clause may reference a path variable at most once.

If the in_paths_clause is omitted, then either a single path pattern must be specified, or all the path patterns must have a path variable declaration.

Examples

Example 1

The following query finds all friends path with length between 0 and 3 starting from a person named John. It outputs one row per vertex.

SELECT *
FROM GRAPH_TABLE ( students_graph
       MATCH (n IS person) -[e1 IS friends]->{0,3} (IS person)
       WHERE n.name = 'John'
       ONE ROW PER VERTEX (v)
       COLUMNS (
        LISTAGG(e1.friendship_id, ', ') AS friendship_ids,
        v.name)
     );

The results are:

FRIENDSHIP_IDS       NAME
-------------------- ---------------
                     John
1                    John
1                    Bob
1, 4                 John
1, 4                 Bob
1, 4                 Mary
1, 4, 3              John
1, 4, 3              Bob
1, 4, 3              Mary
1, 4, 3              John
1, 4, 2              John
1, 4, 2              Bob
1, 4, 2              Mary
1, 4, 2              Alice

The results above show data from five paths that were matched:

The empty path (zero friendship_ids) contains a single person named John.
The path with friendship_ids 1 contains two persons named John and Bob.
The path with friendship_ids 1, 4 contains three persons named John, Bob and Mary.
The path with friendship_ids 1, 4, 3 contains four persons named John, Bob, Mary and John (this is a cycle).
The path with friendship_ids 1, 4, 2 contains four persons named John, Bob, Mary and Alice.

Example 2

The following query again finds all friends path with length between 0 and 3 starting from a person named John. This time it outputs one row per step.

SELECT *
FROM GRAPH_TABLE ( students_graph
       MATCH (n IS person) -[e1 IS friends]->{0,3} (IS person)
       WHERE n.name = 'John'
       ONE ROW PER STEP (src, e2, dst)
       COLUMNS (
         LISTAGG(e1.friendship_id, ', ') AS friendship_ids,
         src.name AS src_name,
         e2.friendship_id,
         dst.name AS dst_name)
     );

The results are:

FRIENDSHIP_IDS       SRC_NAME   FRIENDSHIP_ID DST_NAME
-------------------- ---------- ------------- ----------
                     John
1                    John       1             Bob
1, 4                 John       1             Bob
1, 4                 Bob        4             Mary
1, 4, 3              John       1             Bob
1, 4, 3              Bob        4             Mary
1, 4, 3              Mary       3             John
1, 4, 2              John       1             Bob
1, 4, 2              Bob        4             Mary
1, 4, 2              Mary       2             Alice

The results above show data from five paths that were matched:

The empty path (no friendship_ids) has a single step in which iterator vertex variable src is bound to the vertex corresponding to the person named John, while iterator edge variable e2 and iterator vertex variable dst are not bound, resulting in NULL values for FRIENDSHIP_ID and DST_NAME.
The path with friendship_ids 1 has a single step since it has a single edge. In this step, iterator vertex variable src is bound to the vertex corresponding to John, iterator edge variable e2 is bound to the edge with friendship_ids 1, and iterator vertex variable dst is bound to the vertex corresponding to Bob.
The path with friendship_ids 1, 4 has two steps since it has two edges.
The path with friendship_ids 1, 4, 3 has three steps since it has three edges.
The path with friendship_ids 1, 4, 2 again has three steps since it has three edges.

Example 3

The following query matches paths between universities ABC and XYZ such that paths consist of an incoming student_of edge, followed by one or two friends edges, followed by an outgoing student_of edge. The query returns one row per vertex and for each row it returns the match number, the element number, the type of the vertex (either person or university), as well as the name of the university or the person.

SELECT *
FROM GRAPH_TABLE ( students_graph
       MATCH (u1 IS university)
               <-[IS student_of]- (p1 IS person)
               -[IS friends]-{1,2} (p2 IS person)
               -[IS student_of]-> (u2 IS university)
       WHERE u1.name = 'ABC' AND u2.name = 'XYZ'
       ONE ROW PER VERTEX (v)
       COLUMNS (MATCHNUM() AS matchnum,
                ELEMENT_NUMBER(v) AS element_number,
                CASE WHEN v.person_id IS NOT NULL
                  THEN 'person'
                  ELSE 'university'
                  END AS label,
                v.name))
ORDER BY matchnum, element_number;

The results are:

MATCHNUM ELEMENT_NUMBER        LABEL       NAME
---------- -------------- ---------- ----------
         1              1 university         ABC
         1              3 person             John
         1              5 person             Mary
         1              7 university         XYZ
         2              1 university         ABC
         2              3 person             Bob
         2              5 person             John
         2              7 person             Mary
         2              9 university         XYZ
         3              1 university         ABC
         3              3 person             Bob
         3              5 person             Mary
         3              7 university         XYZ
         4              1 university         ABC
         4              3 person             John
         4              5 person             Mary
         4              7 person             Alice
         4              9 university         XYZ
         6              1 university         ABC
         6              3 person             John
         6              5 person             Bob
         6              7 person             Mary
         6              9 university         XYZ
         8              1 university         ABC
         8              3 person             Bob
         8              5 person             Mary
         8              7 person             Alice
         8              9 university         XYZ

Note that a total of 6 paths were matched with match numbers 1, 2, 3, 4, 6 and 8. Each path has university ABC as the first vertex and university XYZ as the last vertex. Furthermore, paths with match numbers 1 and 3 contain two person vertices while the other paths (match numbers 2, 4, 6 and 8) contain three person vertices.

Example 4

Like in Example 3, the following query matches paths between universities ABC and XYZ. In Example 4, the graph pattern is split into three path patterns. The first path pattern matches an incoming student_of edge, the second path pattern matches one or two friends’ edges, and the third path pattern matches again a student_of edge. The query returns one row per vertex in the second path. This path contains only person vertices. For each vertex, the query returns the match number, the path name, the element number, and all the vertex properties.

SELECT *
FROM GRAPH_TABLE ( students_graph
       MATCH path1 = (u1 IS university) <-[IS student_of]- (p1 IS person),
             path2 = (p1) -[IS friends]-{1,2} (p2 IS person),
             path3 = (p2) -[IS student_of]-> (u2 IS university)
       WHERE u1.name = 'ABC' AND u2.name = 'XYZ'
       ONE ROW PER VERTEX (v) IN (path2)
       COLUMNS (MATCHNUM() AS matchnum,
                PATH_NAME() AS path_name,
                ELEMENT_NUMBER(v) AS element_number,
                v.*))
ORDER BY matchnum, element_number;
The results are:
MATCHNUM PATH_NAME ELEMENT_NUMBER PERSON_ID NAME  DOB       HEIGHT    ID
-------- --------- -------------- --------- ----- --------- --------- --
       1 PATH2                  1         1 John  13-JUN-63       1.8
       1 PATH2                  3         2 Mary  25-SEP-82      1.65
       2 PATH2                  1         3 Bob   11-MAR-66      1.75
       2 PATH2                  3         1 John  13-JUN-63       1.8
       2 PATH2                  5         2 Mary  25-SEP-82      1.65
       3 PATH2                  1         3 Bob   11-MAR-66      1.75
       3 PATH2                  3         2 Mary  25-SEP-82      1.65
       4 PATH2                  1         1 John  13-JUN-63       1.8
       4 PATH2                  3         2 Mary  25-SEP-82      1.65
       4 PATH2                  5         4 Alice 01-FEB-87       1.7
       6 PATH2                  1         1 John  13-JUN-63       1.8
       6 PATH2                  3         3 Bob   11-MAR-66      1.75
       6 PATH2                  5         2 Mary  25-SEP-82      1.65
       8 PATH2                  1         3 Bob   11-MAR-66      1.75
       8 PATH2                  3         2 Mary  25-SEP-82      1.65
       8 PATH2                  5         4 Alice 01-FEB-87       1.7

Like in Example 3, a total of 6 paths were matched with match numbers 1, 2, 3, 4, 6 and 8. Paths with match numbers 1 and 3 contain two person vertices while the other paths (match numbers 2, 4, 6 and 8) contain three person vertices. The all properties reference v.* expands to properties PERSON_ID, NAME, DOB, HEIGHT and ID. Thus, even though person vertices do not have property ID (only university vertices do), the expansion still includes property ID because an all properties reference with an iterator variable always expands to either all vertex properties or all edge properties in the graph based on the iterator variable type.