6.8.3.2 Unnesting of Variable-Length Path Queries

Unnesting of variable-length path queries (such as, SHORTEST or CHEAPEST paths) to obtain a separate row for each vertex or edge along a path is supported.

You can unnest a path aggregation using one of the following options:

• ONE ROW PER MATCH (default option)
• ONE ROW PER VERTEX(vertex_variable)
• ONE ROW PER EDGE(edge_variable)

Note:

Visualization of variable-length path queries using the Graph Visualization application is not supported.

For example, the following PGQL query uses ONE ROW PER EDGE option:

SELECT k.TXN_AMOUNT
FROM MATCH ALL SHORTEST (a:Accounts) -[e:transfers]->* (b:Accounts) 
ONE ROW PER EDGE( k )
WHERE a.ACCT_ID = 284 AND b.ACCT_ID = 616

On execution, the preceding query retrieves one row per edge along a path:

+------------+
| TXN_AMOUNT |
+------------+
| 1000.0     |
| 1000.0     |
| 1000.0     |
+------------+

An example for a query with ONE ROW PER VERTEX option is as follows:

SELECT k.ACCT_ID AS id, k.ACCT_NAME AS name
FROM MATCH ANY SHORTEST (a:Accounts) ((src:Accounts)-[e:transfers]->){1,3}(b:Accounts)
ONE ROW PER VERTEX(k)
WHERE a.ACCT_ID=284 AND b.ACCT_ID=616

On execution, the preceding query retrieves one row per vertex along a path:

+----------------------+
| ACCT_ID | ACCT_NAME  |
+----------------------+
| 616     | Account4   |
| 744     | Account3   |
| 772     | Account2   |
| 284     | Account1   |
+----------------------+

Built-in Function Support for Recursive Path Unnesting Queries

PGQL supports the following two built-in functions, which can be used in combination with any of the path unnesting option (ONE ROW PER VERTEX, ONE ROW PER EDGE or ONE ROW PER MATCH):

• MATCH_NUMBER(k): Returns a unique per-path identifier for each unnested path (that is, if two rows come from the same path, they have the same MATCH_NUMBER(k)).
• ELEMENT_NUMBER(k): Returns the element number of a vertex or an edge along a path. Vertices are numbered with odd numbers, the leftmost vertex is numbered 1, the second 3, then 5 and so on. Edges are assigned with even numbers, starting with 2 for the leftmost edge, 4 for the next one, and so on.

For example, the following PGQL query uses the MATCH_NUMBER(k) and ELEMENT_NUMBER(k) functions with ONE ROW PER VERTEX option:

SELECT k.*, match_number(k), element_number(k) 
FROM MATCH ANY SHORTEST (a:Accounts) -[e:transfers]->* (b:Accounts) ONE ROW PER VERTEX ( k ) 
WHERE a.ACCT_ID = 284 AND b.ACCT_ID = 616

The preceding query produces the following output on execution. Note that the element_number(k) returned for the vertices are odd numbered values. Since the preceding query uses ANY path pattern, there is only one arbitrary path displayed in the output. Therefore match_number(k) is the same for all the rows in the path.

+-----------------------------------------------------------+
| ACCT_ID | ACCT_NAME | match_number(k) | element_number(k) |
+-----------------------------------------------------------+
| 616     | Account   | 0               | 7                 |
| 744     | Account   | 0               | 5                 |
| 772     | Account   | 0               | 3                 |
| 284     | Account   | 0               | 1                 |
+-----------------------------------------------------------+

The following example shows a PGQL query using MATCH_NUMBER(k) and ELEMENT_NUMBER(k) functions with ONE ROW PER EDGE option:

SELECT k.*, match_number(k), element_number(k) 
FROM MATCH ALL SHORTEST (a:Accounts) -[e:transfers]->* (b:Accounts) ONE ROW PER EDGE( k )
WHERE a.ACCT_ID = 284 AND b.ACCT_ID = 616

The preceding query output is as shown. Note that there are two paths identified by match_number(k)and the edges are displayed with even numbered element_number(k) values.

+-----------------------------------------------------------------------------------------+
| FROM_ACCT_ID | TXN_AMOUNT | TXN_DESC | TO_ACCT_ID | match_number(k) | element_number(k) |
+-----------------------------------------------------------------------------------------+
| 744          | 1000.0     | transfer | 616        | 0               | 6                 |
| 772          | 1500.0     | transfer | 744        | 0               | 4                 |
| 284          | 1000.0     | transfer | 772        | 0               | 2                 |
| 744          | 1000.0     | transfer | 616        | 1               | 6                 |
| 772          | 1000.0     | transfer | 744        | 1               | 4                 |
| 284          | 1000.0     | transfer | 772        | 1               | 2                 |
+-----------------------------------------------------------------------------------------+