You can use SELECT v.* to select all properties of the vertices or edges that bind to the variable v. For example:

SELECT label(n), n.* FROM MATCH (n) ORDER BY "number", "name"

On execution, the query output is as shown:

+-----------------------------+
| label(n) | number | name    |
+-----------------------------+
| Account  | 1001   | <null>  |
| Account  | 2090   | <null>  |
| Account  | 8021   | <null>  |
| Account  | 10039  | <null>  |
| Person   | <null> | Camille |
| Person   | <null> | Liam    |
| Person   | <null> | Nikita  |
| Company  | <null> | Oracle  |
+-----------------------------+

You can use label expressions to select properties that belong to the specified vertex or edge labels. For example:

SELECT label(n), n.* FROM MATCH (n:Person) ORDER BY "name"

The preceding query retrieves all the properties for the specified Person label:

+--------------------+
| label(n) | name    |
+--------------------+
| Person   | Camille |
| Person   | Liam    |
| Person   | Nikita  |
+--------------------+

You can also specify a PREFIX to avoid duplicate column names in cases where you select all properties using multiple variables. For example:

SELECT n.* PREFIX 'n_', e.* PREFIX 'e_', m.* PREFIX 'm_'
FROM MATCH (n:Account) -[e:transaction]-> (m:Account)
ORDER BY "e_amount"

The query output is as shown:

+--------------------------------+
| n_number | e_amount | m_number |
+--------------------------------+
| 10039    | 1000.0   | 8021     |
| 8021     | 1500.3   | 1001     |
| 8021     | 3000.7   | 1001     |
| 2090     | 9900.0   | 10039    |
| 1001     | 9999.5   | 2090     |
+--------------------------------+

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.

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

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 18-1 Visualizing Unnesting of Variable-Length Path Queries

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

Also, when using the ONE ROW PER STEP option, if the path is empty (that is, the path length is zero), then it has a single step such that the first vertex variable (v1) is bound but the edge variable (e) and the second vertex variable (v2) are unbound. Note that accessing the properties of unbound variables will result in NULL values.

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 |
+---------------+

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

+-----------------------------------------------------------+
| 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                 |
+-----------------------------------------------------------+

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

+---------------------------------------------------------------------+
| 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                 |
+---------------------------------------------------------------------+

The following path modes are available in combination with ANY, ALL, ANY SHORTEST, SHORTEST k, and ALL SHORTEST:

• WALK (default path mode): A walk is traversing a graph through a sequence of vertices and edges. The vertices and edges visited in a walk can be repeated. Hence there is no filtering of paths in this default path mode.
• TRAIL: A trail is traversing a graph without repeating the edges. Therefore, path bindings with repeated edges are not returned.

  SELECT CAST(a.number AS STRING) || ' -> ' || LISTAGG(x.number, ' -> ') AS accounts_along_path
  FROM MATCH ALL TRAIL PATHS (a IS account) (-[IS transaction]-> (x)){2,} (b IS Account)
  WHERE a.number = 8021 AND b.number = 1001
  
  +-----------------------------------------------+
  | accounts_along_path                           |
  +-----------------------------------------------+
  | 8021 -> 1001 -> 2090 -> 10039 -> 8021 -> 1001 |
  | 8021 -> 1001 -> 2090 -> 10039 -> 8021 -> 1001 |
  +-----------------------------------------------+

  In the preceding output, both the paths contain the vertices 8021 and 1001 twice but they are still valid trails as long as no edges are repeated.
• ACYCLIC: If the starting and ending vertex in a graph traversal are different, then this implies that there are no cycles in the path. In this case, the path bindings with repeated vertices are not returned.

  SELECT CAST(a.number AS STRING) || ' -> ' || LISTAGG(x.number, ' -> ') AS accounts_along_path
  FROM MATCH SHORTEST 10 ACYCLIC PATHS (a IS account) (-[IS transaction]-> (x))+ (b)
  WHERE a.number = 10039 AND b.number = 1001
  
  +-----------------------+
  | accounts_along_path   |
  +-----------------------+
  | 10039 -> 8021 -> 1001 |
  | 10039 -> 8021 -> 1001 |
  +-----------------------+

  The preceding query requested 10 shortest paths. But only two are returned since all the other paths are cyclic.
• SIMPLE: A simple walk is traversing a graph without repeating the vertices. Therefore, path bindings with repeated vertices are not returned. The only exception is when the repeated vertex is the first and the last in a path.

  SELECT CAST(a.number AS STRING) || ' -> ' || LISTAGG(x.number, ' -> ') AS accounts_along_path
  FROM MATCH ANY SIMPLE PATH (a IS account) (-[IS transaction]-> (x))+ (a)
  WHERE a.number = 10039
  
  +----------------------------------------+
  | accounts_along_path                    |
  +----------------------------------------+
  | 10039 -> 8021 -> 1001 -> 2090 -> 10039 |
  +----------------------------------------+

  The preceding query returns a cyclic path. This path is a valid simple path since it starts and ends in the same vertex and there is no other cycle in the path.

Note that the path modes are syntactically placed after ANY, ALL, ANY SHORTEST, SHORTEST k, ALL SHORTEST, CHEAPEST, and CHEAPEST k. The path mode is optionally followed by a PATH or PATHS keyword.

Note that using TRAIL, ACYCLIC, or SIMPLE matching path modes for all unbounded quantifiers guarantees that the result set of a graph pattern matching will be finite.

You can use a LATERAL subquery to pass the output rows of one query into another.

For example, you can use the ORDER BY or GROUP BY clause on top of another ORDER BY or GROUP BY clause:

/* Find the top-5 largest transactions and return the account number
   that received the highest number of such large transactions */
SELECT recipient, COUNT(*) AS num_large_transactions
FROM LATERAL ( SELECT m.number AS recipient
               FROM MATCH (n:account) -[e:transaction]-> (m:account)
               ORDER BY e.amount DESC
               LIMIT 5 )
GROUP BY recipient
ORDER BY num_large_transactions DESC
LIMIT 1

Also, the LATERAL subquery in the FROM clause can be followed by one or more MATCH clauses. For example:

SELECT path_num, elem_num, owner.name
FROM LATERAL ( SELECT v, MATCHNUM(v) AS path_num, ELEMENT_NUMBER(v) AS elem_num
               FROM MATCH SHORTEST 2 PATHS (a1:account) -[e:transaction]->* (a2:account)
                      ONE ROW PER VERTEX ( v )
               WHERE a1.number = 10039 AND a2.number = 2090 )
    , MATCH (v) -[:owner]-> (owner:Person|Company)
ORDER BY path_num, elem_num 

Note that the FROM clause may contain any number of MATCH clauses and LATERAL subqueries.

Both, MATCH clause followed by a LATERAL subquery, or a LATERAL subquery followed by one or more LATERAL subqueries are supported.

Also, note the following:

• Variables exported from previous table expressions can be used in subsequent table expressions.
• Operators like ORDER BY and LIMIT can be used followed by additional pattern matching.
• Variables that are not projected from a LATERAL subquery cannot be accessed in the outer query.

The following example query first retrieves a list of companies, Then, it finds the respective accounts of the companies. Finally, it finds the top 2 transactions made to the account of each company in the initial list.

SELECT c.name, a.number, t.amount FROM 
  LATERAL ( SELECT c FROM MATCH (c:Company) ORDER BY c.name LIMIT 1),
  MATCH (a:Account)->(c), 
  LATERAL (SELECT t FROM MATCH ()-[t:Transaction]->(a) ORDER BY t.amount DESC LIMIT 2)

In the following query, the LATERAL subquery is followed by two other LATERAL subqueries. Each subquery builds upon the output from the previous clauses:

SELECT f.number as fundsAccount FROM
LATERAL ( SELECT p FROM MATCH (p:Person) WHERE p.name = 'Nikita'),
LATERAL ( SELECT a FROM MATCH (a)->(p)),
LATERAL ( SELECT f FROM MATCH (f)->(a))

The OPTIONAL MATCH clause has the same set of capabilities as a regular MATCH clause, and can contain one or more path patterns followed by an optional WHERE clause.

Using OPTIONAL MATCH in a PGQL query is similar to a LEFT OUTER JOIN in a SQL query.

The OPTIONAL keyword can be applied to a MATCH clause in a PGQL query as shown:

SELECT * FROM MATCH (person), OPTIONAL MATCH ((person)->(address) WHERE address.number=33)

The resulting query output lists all persons with and without address.number=33:

+-------------------------+
| person  |    address    |
+-------------------------+
| Nikita  | Greenwhich 33 |
| Camille | Silver St 33  |
| Liam    |    <null>     |
| John    |    <null>     |
+-------------------------+

The following shows an example of an OPTIONAL MATCH clause containing multiple path patterns and a WHERE clause.

-- List all people as well as their addresses in zip code 12345
SELECT p.name, s.street_name, z.zip_code
FROM MATCH (p IS person),
     OPTIONAL MATCH ( (p) -[e1:has_address]-> (a:address),
                      (a) -[e2:street]-> (s:street),
                      (a) -[e3:zip_code]-> (z:zip_code)
                      WHERE z.zip_code = 12345 )

In the preceding query, note that the WHERE clause is present inside the parenthesis. This indicates that the filter is applied before the OPTIONAL binding is executed. The query output may appear as shown:

John P.   1st Street    12345
Mary M.   NULL          NULL      <== NULL value means Mary M. has no address with zip 12345
Jane X.   NULL          NULL      <== NULL value means Jane X. has no address with zip 12345

If the WHERE clause is placed outside of the OPTIONAL MATCH clause, then the filter will be applied after the OPTIONAL clause, essentially removing all unbound variables added by the OPTIONAL clause.

-- List all people as well as their addresses, then filter out addresses that do not have zip code 12345
SELECT p.name, s.street_name, z.zip_code
FROM MATCH (p IS person),
     OPTIONAL MATCH ( (p) -[e1:has_address]-> (a:address),
                      (a) -[e2:has_street]-> (s:street),
                      (a) -[e3:has_zip_code]-> (z:zip_code)
                    )
WHERE z.zip_code = 12345

John P.   1st Street    12345

OPTIONAL MATCH can be used in conjunction with LATERAL subqueries, scalar subqueries, ORDER BY or GROUP BY clauses. However, OPTIONAL MATCH is not supported with the GRAPH_TABLE operator.