FOR statement

The FOR statement unnest a list or a table by iterating over its elements, allowing subsequent statements to process these elements one by one.

If more than one FOR statement is cascaded together, they produce the Cartesian product of all the elements from the corresponding lists or tables.

Syntax

diagramsource

for_statement

for_ordinality_or_offset

for_statement ::=
        'FOR' variable_name 'IN' ( list_value_expression ('||' list_value_expression)* | table_reference_value_expression ) for_ordinality_or_offset?

for_ordinality_or_offset ::=
        ('WITH ORDINALITY' | 'WITH OFFSET') index_variable_name

Parameters

variable_name

The name of the variable that refers to each element in the list or table during the iteration. variable_name can be a regular identifier or a delimited identifier enclosed with backticks (`). For more information about identifiers, see Rules for naming identifiers.

list_value_expression

A value expression that evaluates to a list or a reference of a list. It can be a variable, a function call, or any expression that evaluates to a list.

For more information, see List type, List functions, and Construct value expression.

||

The concatenation operator that combines multiple lists into a single list.

table_reference_value_expression

A value expression that evaluates to a reference of a table, such as a table variable.

for_ordinality_or_offset

An optional clause that adds a positional index to each element during iteration.

WITH ORDINALITY

The clause that returns a 1-based index (starting from 1) for each element in the list or table.

WITH OFFSET

The clause that returns a 0-based index (starting from 0) for each element in the list or table.

index_variable_name

The name of the index variable that stores the serial number of each element during iteration. index_variable_name can be a regular identifier or a delimited identifier enclosed with backticks (`). For more information about identifiers, see Rules for naming identifiers.

index_variable_name and variable_name must be different.

Examples

The following examples are based on the example dataset.

Unnest a list

'''
LET l = list[1, 2, 3] 
FOR e IN l 
RETURN e
'''

The preceding statement unnests the list l and returns the result set e.

Return:

+---+
| e |
+---+
| 1 |
| 2 |
| 3 |
+---+

Unnest a nested list

'''
LET ll = list[list[1, 2, 3], list[4, 5, 6]] 
FOR l IN ll 
FOR e IN l 
RETURN e
'''

The preceding statement first unnests the nested list ll, returning the result set l. It then unnests the list l, returning the result set e.

Return:

+---+
| e |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

Unnest two lists

'''
LET l1 = list[1, 2, 3], l2 = list[4, 5, 6] 
FOR e1 IN l1 
FOR e2 IN l2 
RETURN e1, e2
'''

The preceding statement unnests the list l1, returning the result set e1. It then unnests the list l2, returning the result set e2. The result sets e1 and e2 are combined using the Cartesian Product.

Return:

+----+----+
| e1 | e2 |
+----+----+
| 1  | 4  |
| 1  | 5  |
| 1  | 6  |
| 2  | 4  |
| 2  | 5  |
| 2  | 6  |
| 3  | 4  |
| 3  | 5  |
| 3  | 6  |
+----+----+

Unnest a list returned by a function

'''
USE movie 
MATCH p =  (v@Actor)-[]->(f@Movie{name:"Wilbur Wants to Kill Himself"})-[]->(g@Genre) 
LET sz = length(p), vid = v.id, l = range(1, length(p)) 
FOR e IN l 
RETURN DISTINCT sz, vid, l, e 
ORDER BY vid 
LIMIT 6
'''

The preceding statement performs the following operations:

1. Matches a path p that starts from a node v and ends at a node g. The path p contains two edges.
2. Assigns sz as the length of the path p using length(), vid as the ID of the node v, and l as a list containing numbers from 1 to the length of the path p using range().
3. Unnests the list l with the FOR statement and returns the result set e.
4. Returns sz, vid, l, and e without duplicates.
5. Sorts the result set by the ID of the node v and limits the result set to 6 rows.

Return:

+----+------+-------+---+
| sz | vid  | l     | e |
+----+------+-------+---+
| 2  | 1019 | [1,2] | 2 |
| 2  | 1019 | [1,2] | 1 |
| 2  | 1643 | [1,2] | 1 |
| 2  | 1643 | [1,2] | 2 |
| 2  | 1833 | [1,2] | 2 |
| 2  | 1833 | [1,2] | 1 |
+----+------+-------+---+

Unnest two lists concatenated by ||

'''
LET l1 = list[1, 2, 3], l2 = trim(list[4, 5, 6, 7], 2) 
FOR e IN l1 || l2 
RETURN e
'''

Return:

+---+
| e |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+

Unnest a table

'''
TABLE t { id, name, birthDate } =  
    ( 10001, 'Alice', DATE '1990-01-01' ),  
    ( 10002, 'Bob', DATE '1991-02-02' ),  
    ( 10003, 'Charlie', DATE '1992-03-03' ) 
FOR e IN t 
RETURN e.id, e.name, e.birthDate
'''

Return:

+-------+---------+-------------+
| e.id  | e.name  | e.birthDate |
+-------+---------+-------------+
| 10001 | Alice   | 1990-01-01  |
| 10002 | Bob     | 1991-02-02  |
| 10003 | Charlie | 1992-03-03  |
+-------+---------+-------------+

Unnest a list with ordinality

'''
LET l=list[10, 20, 30] 
FOR e IN l WITH ORDINALITY idx 
RETURN e, idx
'''

Return:

+----+-----+
| e  | idx |
+----+-----+
| 10 | 1   |
| 20 | 2   |
| 30 | 3   |
+----+-----+

Unnest a list with offset

'''
FOR i IN range(1, 10239) WITH OFFSET idx
RETURN i, idx
ORDER BY i ASC 
LIMIT 5
'''

Return:

+---+-----+
| i | idx |
+---+-----+
| 1 | 0   |
| 2 | 1   |
| 3 | 2   |
| 4 | 3   |
| 5 | 4   |
+---+-----+