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> }... ]

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

Create this graphInsert data to the graph
CREATE GRAPH myGraph { 
  NODE Student ({name string, gender string}),
  NODE Course ({name string, credit uint32}),
  EDGE Take ()-[{year uint32, term string}]->()
} PARTITION BY HASH(Crc32) SHARDS [1]

Returning Nodes

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

GQL
MATCH (n:Course)
RETURN n

Result: n

Returning Edges

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

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

Result: e

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:

s

c

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 with Aggregation

Aggregation functions, such as sum() and max(), can be directly applied in the RETURN statement.

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

Result:

Due to the use of the aggregate function, the c returned by this query contains only one record, as expected:

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

Result:

c

sum(c.credit)

Returning by CASE

GQL
MATCH (n:Course)
RETURN n.name, CASE WHEN n.credit > 14 THEN "Y" ELSE "N" END AS Recommended

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: n

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:

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: