RETURN

Overview

The RETURN statement allows you to specify items to include in the query result. Each item is defined by an expression that can include variables, properties, functions, constants, etc.

Syntax
<return statement> ::= 
  "RETURN" [ "DISTINCT" | "ALL" ] { < "*" > | <return items> } [ <group by clause> ]
                          
<return items> ::= <return item> [ { "," <return item> }... ]

<return item> ::= <value expression> [ "AS" <identifier> ]
  
<group by clause> ::= 
  "GROUP BY" <grouping key> [ { "," <grouping key> }... ] [ <having clause> ]

<having clause> ::= "HAVING" <search condition>

Details

• The asterisk * returns all columns in the intermediate result table. See Returning All.
• The keyword AS can be used to rename a return item. See Return Item Alias.
• The RETURN statement supports the GROUP BY clause. See Returning with Grouping.
• The <having clause> can follow GROUP BY to filter groups based on aggregate results. See Filtering Groups with HAVING.
• The DISTINCT operator can be used to deduplicate records. If neither DISTINCT nor ALL is specified, ALL is implicitly applied. See Returning Distinct Records.

Example Graph

GQL
INSERT (alex:Student {_id: 's1', name: 'Alex', gender: 'male'}),
       (susan:Student {_id: 's2', name: 'Susan', gender: 'female'}),
       (art:Course {_id: 'c1', name: 'Art', credit: 13}),
       (literature:Course {_id: 'c2', name: 'Literature', credit: 15}),
       (alex)-[:Take {year: 2024, term: 'Spring'}]->(art),
       (susan)-[:Take {year: 2023, term: 'Fall'}]->(art),
       (susan)-[:Take {year: 2023, term: 'Spring'}]->(literature)

Returning Nodes

A variable bound to nodes returns all information about each node.

GQL
MATCH (n:Course)
RETURN n

Result:

Returning Edges

A variable bound to edges returns all information about each edge.

GQL
MATCH ()-[e]->()
RETURN e

Result:

Returning Paths

A variable bound to paths returns all information about the nodes and edges included in each path.

GQL
MATCH p = ()-[:Take {term: "Spring"}]->()
RETURN p

Result: p

Returning Labels

The function labels() can be used to return the labels of nodes and edges.

GQL
MATCH ({_id: "s2"})-[e]->(n)
RETURN labels(e), labels(n)

Result:

Returning Properties

The period operator . can be used to extract the value of a specified property from a variable bound to nodes or edges. The null value will be returned if the specified property is not found on the nodes or edges.

GQL
MATCH (:Student {name:"Susan"})-[]->(c:Course)
RETURN c.name, c.credit, c.type

Result:

Returning All

The asterisk * returns all columns in the intermediate result table. Note that the RETURN statement cannot include the GROUP BY clause when using *.

GQL
MATCH (s:Student {name:"Susan"})-[]->(c:Course)
RETURN *

Result:

sc
[
  {"id": "s2", "labels": ["Student"], "properties": {"name": "Susan", "gender": "female"}},
  {"id": "s2", "labels": ["Student"], "properties": {"name": "Susan", "gender": "female"}},
]

Return Item Alias

The AS keyword allows you to assign an alias to a return item.

GQL
MATCH (s:Student)-[t:Take]->(c:Course)
RETURN s.name AS Student, c.name AS Course, t.year AS TakenIn

Result:

Returning Limited Records

The LIMIT statement can be used to restrict the number of records retained for each return item.

GQL
MATCH (n:Course)
RETURN n.name LIMIT 1

Result:

Returning Ordered Records

The ORDER BY statement can be used to sort the records.

GQL
MATCH (n:Course)
RETURN n ORDER BY n.credit DESC

Result:

Returning Computed Values

A RETURN item can be any value expression, not only a variable or property reference. The expression is evaluated per row and the result becomes a column in the output. Computed values cover arithmetic, function calls, string concatenation, conditional CASE expressions, comparisons, and any composition of these.

Arithmetic on properties:

GQL
MATCH (n:Course)
RETURN n.name, n.credit, n.credit * 2 AS double_credit

Result:

Function call on a property:

GQL
MATCH (n:Student)
RETURN n.name, upper(n.name) AS upper_name

Result:

GQL
MATCH (:Student {name:"Susan"})-[]->(c:Course)
RETURN sum(c.credit)

Result:

Comparison and conditional:

GQL
MATCH (n:Course)
RETURN n.name, CASE WHEN n.credit >= 14 THEN 'high' ELSE 'low' END AS credit_level

Result:

Returning with Grouping

The GROUP BY clause allows you to specify the keys to group the query result. After grouping, each group will keep only one record.

Grouping by One Key

GQL
MATCH ()-[e:Take]->()
RETURN e.term GROUP BY e.term

Result:

In the GQL standard, the grouping key must be a direct variable reference, where this query must be written as RETURN e.term AS <varName> GROUP BY <varName>. Ultipa simplifies this by allowing direct grouping on expressions, removing the need to introduce intermediate variables.

Grouping by Multiple Keys

GQL
MATCH ()<-[e:Take]-()
RETURN e.year, e.term GROUP BY e.year, e.term

Result:

Grouping and Aggregation

When grouping is applied, any aggregation operation in the RETURN statement is performed on each group.

This query counts the number of Take edges for each Term:

GQL
MATCH ()-[e:Take]->()
RETURN e.term, count(e) GROUP BY e.term

Result:

Filtering Groups with HAVING

The HAVING clause filters groups produced by GROUP BY based on aggregate results. It must follow GROUP BY and is evaluated after grouping and aggregation, allowing the search condition to reference aggregate functions or their aliases.

HAVING differs from WHERE/FILTER: WHERE and FILTER filter records before grouping and cannot reference aggregate results, while HAVING filters groups after aggregation.

This query returns terms in which more than one Take edge exists:

GQL
MATCH ()-[e:Take]->()
RETURN e.term AS term, count(e) AS cnt
GROUP BY e.term
HAVING cnt > 1

Result:

This query returns years in which the total credit of taken courses exceeds 20:

GQL
MATCH ()-[e:Take]->(c:Course)
RETURN e.year AS year, sum(c.credit) AS totalCredit
GROUP BY e.year
HAVING totalCredit > 20

Result:

Returning Distinct Records

The DISTINCT operator deduplicates records for all return items. When DISTINCT is specified, each return item is implicly an operand of a grouping operation.

GQL
MATCH ()-[e]->()
RETURN DISTINCT e.year

This is equivalent to:

GQL
MATCH ()-[e]->()
RETURN e.year GROUP BY e.year

Result:

GQL
MATCH ()-[e]->()
RETURN DISTINCT e.year, e.term

This is equivalent to:

GQL
MATCH ()-[e]->()
RETURN e.year, e.term GROUP BY e.year, e.term 

Result: