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.eps

graph_table_column_definition::=

Description of the illustration graph_table_column_definition.eps

all_properties_reference::=

Description of the illustration all_properties_reference.eps

element_reference::=

Description of the illustration element_reference.eps

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.

Restrictions

Value expressions may not contain aggregations and cannot reference variables enclosed by a quantifier since group degree of reference is not allowed.

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.5777

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.

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::=

Description of the illustration graph_table_rows_clause.eps

one_row_per_iteration::=

Description of the illustration one_row_per_iteration.eps

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.

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 graph-table-shape.html#GUID-1C95A975-EEC8-44B0-AAE4-655B69F528E2__GUID-252808EF-CF93-420C-8DE9-73E5625B4577 all_properties_clause of COLUMNS.

Restrictions

The graph_table_rows_clause clause is subject to the following restrictions:

• If one_row_per_iteration is used then the graph pattern must consist of exactly one path pattern. Furthermore, that path pattern must consists of an optional vertex pattern followed by either a quantified edge pattern or a quantified path pattern, followed by an optional vertex pattern. For example: -[e]->{1,5} or (v1) ((v2)->(v3)->(v4)){2,3}(v5) are valid path patterns.

• Iterator element variables cannot be multiply declared. This means that an iterator variable may not be declared with the same name as 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.

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.