Using ONE ROW PER Clause in a SQL Graph Query

You can use the ONE ROW PER optional clause in a SQL graph query to determine the number of rows to be returned by the query.

The ONE ROW PER clause can be placed after the MATCH clause (or if present, after a WHERE clause) and before the COLUMNS clause in a SQL graph query.

The ONE ROW PER clause supports the following options which determine the number of rows to be returned by the query:

• ONE ROW PER MATCH (default): This specifies that one row is returned per match to the graph pattern. See Example: Query Using ONE ROW PER MATCH Clause for more information.

• ONE ROW PER VERTEX: This creates one row for each vertex along a path. It declares a single iterator vertex variable and iterates through the vertices in paths, binding 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, consider the following path with three vertices - (v1) → (v2) → (v3). In this case ONE ROW PER VERTEX (v) creates three rows, one for each of v1 ,v2, and v3.

  For zero hop paths, ONE ROW PER VERTEX (v) creates one row, where v binds to the only vertex (which is the source and destination of the path).

  See Example: Query Using ONE ROW PER VERTEX Clause for more information.

• ONE ROW PER STEP: This declares an iterator vertex variable, an iterator edge variable, and another iterator vertex variable. It creates one row for every hop, where the first vertex variable binds to the source of this hop, the edge variable is the edge of the hop and the second vertex variable is the destination of the hop. 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.

  For example, consider the following path - v1 -[e1]→ v2 -[e2]→ v3. In this case ONE ROW PER STEP (v1,e,v2) returns the following two rows:

  v1, e1, v2
  v2, e2, v3

  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.

  See Example: Query Using ONE ROW PER STEP Clause for more information.

The following describes a few restrictions for the iterator variables:

• All iterator variables have to be unique. This implies that these variables need to be different from the graph element variables and path variables used in the MATCH clause. In case of ONE ROW PER STEP, all the three iterator variables need to be different.

• Iterator variables cannot be referenced in the graph pattern or graph pattern WHERE clause of the SQL graph query. They can only be used in the COLUMNS clause of the query.

Note: If you use ONE ROW PER clause in a SQL graph query in the Graph Visualization application or APEX plug-in for graph visualization, then ensure that the underlying SQL property graph definition does not contain any vertex or edge table alias (AS clause).

Using IN Path Clause

If a SQL graph query contains multiple named path patterns, then an IN path clause must be specified when using the ONE ROW PER VERTEX or ONE ROW PER STEP clause in the query. The IN path clause specifies one or more path variables referencing the paths that should be iterated through. If no IN path clause is specified, then all paths are iterated through.

Also, note the following:

• The IN clause may reference a path variable at most once.

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

See Example: Query Using IN Path Clause within ONE ROW PER VERTEX for more information.

• Using the MATCHNUM Function
  The MATCHNUM function returns a number that uniquely identifies a match in a set of matches.

• Using the PATH_NAME Function
  The PATH_NAME function can only be used in combination with the ONE ROW PER VERTEX or ONE ROW PER STEP clause in a SQL graph query and returns the name of the path that the iterator variables are currently iterating over.

• Using the ELEMENT_NUMBER Function
  The ELEMENT_NUMBER function can only be used for iterator variables that are defined in the ONE ROW PER VERTEX or ONE ROW PER STEP clause in a SQL graph query. It returns the sequential element number of the vertex or edge that the referenced iterator variable currently binds to.