Unnesting of Variable-Length Path Queries

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 STEP(edge_source_variable,edge_variable,edge_destination_variable)

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

SELECT  v1.ACCT_ID AS src_no, k.TXN_AMOUNT, v2.ACCT_ID AS dest_no 
FROM MATCH ALL SHORTEST (a:Accounts) -[e:transfers]->+ (b:Accounts)
ONE ROW PER STEP( v1,k,v2 )
WHERE a.ACCT_ID = 284 AND b.ACCT_ID = 616

It is important to note that the ONE ROW PER STEP option only supports paths with a minimal hop greater than 0 and hence * quantifier is not supported with this option.

On execution, the preceding query retrieves one row for every edge on the path that is bound by the corresponding source and destination vertices:

+-------------------------------+
| src_no | TXN_AMOUNT | dest_no |
+-------------------------------+
| 744    | 1000.0     | 616     |
| 772    | 1000.0     | 744     |
| 284    | 1000.0     | 772     |
| 744    | 1000.0     | 616     |
| 772    | 1500.0     | 744     |
| 284    | 1000.0     | 772     |
+-------------------------------+

You can also use the Graph Visualization tool to visualize edges using ONE ROW PER STEP along a path:

Figure 6-1 Visualizing Unnesting of Variable-Length Path Queries

Description of "Figure 6-1 Visualizing Unnesting of Variable-Length Path Queries"

An example for a query with the 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:

+----------------+
| id  | 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 STEP 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 STEP option:

SELECT v1.acct_id AS src_no,k.txn_amount,v2.acct_id AS dest_no, match_number(k), element_number(k)
FROM MATCH ALL SHORTEST (a:Accounts) -[e:transfers]->+ (b:Accounts)
ONE ROW PER STEP( v1,k,v2 )
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.

+---------------------------------------------------------------------+
| src_no | txn_amount | dest_no | match_number(k) | element_number(k) |
+---------------------------------------------------------------------+
| 744    | 1000.0     | 616     | 0               | 6                 |
| 772    | 1000.0     | 744     | 0               | 4                 |
| 284    | 1000.0     | 772     | 0               | 2                 |
| 744    | 1000.0     | 616     | 1               | 6                 |
| 772    | 1500.0     | 744     | 1               | 4                 |
| 284    | 1000.0     | 772     | 1               | 2                 |
+---------------------------------------------------------------------+

Parent topic: PGQL Features Supported in the Graph Server (PGX)