RETURN statement

The RETURN statement selects output columns or performs aggregation before output.

Syntax

diagramsource

return_statement

return_statement ::=
    'RETURN' ( 'ALL' | 'DISTINCT' )?  ( ( (value_expression ('AS' identifier)? ) ( ',' (value_expression ('AS' identifier)? ) )* )
      | '*' ) group_by_clause?

Parameters

ALL | DISTINCT

The rules for handling duplicate rows. The following methods are supported:

• ALL: Default method, which returns all rows and does not eliminate duplicate rows.
• DISTINCT: Eliminates duplicate rows on return.

value_expression

The value expression that evaluates to a value or constant to be returned. For more information about the types of value expressions, see Value expressions.

If at least one value expression is set to an aggregate expression, the RETURN statement performs aggregation in addition to projection. Otherwise, it performs projection only.

identifier

An alias for the field to be returned. The alias must be an identifier. For more information about identifiers, see Rules for naming identifiers.

group_by_clause

The clause that specifies the set of grouping keys to use during grouping. Each grouping key is a field or expression used to group the result set. For more information, see GROUP BY clause.

The group_by_clause can be used only when at least one value_expression is set to an aggregate expression. In this case, the return items in the RETURN statement are classified into the following three types:

• Aggregate return item: A return item that contains an aggregate expression and whose alias is not used as a grouping key in the group_by_clause.
• Group return item: A return item whose alias is used as a grouping key in the group_by_clause.
• Functional group return item: A return item that is functionally dependent on any group return item and whose alias is used as a grouping key in the group_by_clause. For example, SUM(v.age) is functionally dependent on v.age; SUM(v.age) and v.age are functionally dependent on v.

The grouping keys in the group_by_clause can be any visible variables. However, the following rules apply:

• A group return item and its functional group return item cannot both be used as grouping keys.
• If a group return item has multiple functional group return items, only one of them can be used as a grouping key.

If at least one value_expression is set to an aggregate expression but no group_by_clause is specified, implicit aggregation is performed. In this case, the result set is automatically grouped by all non-aggregate return items. If no non-aggregate return items are specified, the entire result set is treated as a single group, which is equivalent to GROUP BY ().

*

Returns all results. The group_by_clause cannot be added after *.

Examples

Return and rename columns

'''
FOR i IN LIST[1, 2, 3]
RETURN i AS num, i * i AS square
ORDER BY num
'''

Return:

+-----+--------+
| num | square |
+-----+--------+
| 1   | 1      |
| 2   | 4      |
| 3   | 9      |
+-----+--------+

Return distinct results

'''
FOR i IN LIST[1, 1, 2, 2, 3]
RETURN DISTINCT i AS num
ORDER BY num
'''

Return:

+-----+
| num |
+-----+
| 1   |
| 2   |
| 3   |
+-----+

Perform aggregation

'''
FOR i IN LIST[1, 1, 2, 3, 3, 3]
RETURN i AS num, count(*) AS cnt
GROUP BY num
ORDER BY num
'''

Return:

+-----+-----+
| num | cnt |
+-----+-----+
| 1   | 2   |
| 2   | 1   |
| 3   | 3   |
+-----+-----+

Return all columns from the current result set using *

'''
TABLE t TYPED TABLE {id INT, name STRING} =
  (1, "A"),
  (2, "B")
FOR r IN t
RETURN *
'''

Return:

+---------------+
| r             |
+---------------+
| {id:1,name:A} |
| {id:2,name:B} |
+---------------+

Return selected columns from the current result set

'''
TABLE t TYPED TABLE {id INT, name STRING} =
  (1, "A"),
  (2, "B")
FOR r IN t
RETURN r.id, r.name
'''

Return:

+------+--------+
| r.id | r.name |
+------+--------+
| 1    | A      |
| 2    | B      |
+------+--------+