All GoogleSQL functions are supported, including the following GQL-specific functions:
Function list
| Name | Summary |
|---|---|
LABELS
|
Gets the labels associated with a graph element. |
PROPERTY_NAMES
|
Gets the property names associated with a graph element. |
LABELS
LABELS(element)
Description
Gets the labels associated with a graph element and preserves the original case of each label.
Arguments
element: AGRAPH_ELEMENTvalue.
Details
Returns NULL if element is NULL.
Return type
ARRAY<STRING>
Examples
GRAPH FinGraph
MATCH (n:Person|Account)
RETURN LABELS(n) AS label, n.id
/*----------------+
| label | id |
+----------------+
| [Account] | 7 |
| [Account] | 16 |
| [Account] | 20 |
| [Person] | 1 |
| [Person] | 2 |
| [Person] | 3 |
+----------------*/
PROPERTY_NAMES
PROPERTY_NAMES(element)
Description
Gets the name of each property associated with a graph element and preserves the original case of each name.
Arguments
element: AGRAPH_ELEMENTvalue.
Details
Returns NULL if element is NULL.
Return type
ARRAY<STRING>
Examples
GRAPH FinGraph
MATCH (n:Person|Account)
RETURN PROPERTY_NAMES(n) AS property_names, n.id
/*-----------------------------------------------+
| label | id |
+-----------------------------------------------+
| [create_time, id, is_blocked, nick_name] | 7 |
| [create_time, id, is_blocked, nick_name] | 16 |
| [create_time, id, is_blocked, nick_name] | 20 |
| [birthday, city, country, id, name] | 1 |
| [birthday, city, country, id, name] | 2 |
| [birthday, city, country, id, name] | 3 |
+-----------------------------------------------*/
Horizontal aggregate function calls in GQL
In GQL, a horizontal aggregate function is an aggregate function that summarizes
the contents of exactly one array-typed value. Because a horizontal aggregate
function does not need to aggregate vertically across rows like a traditional
aggregate function, you can use it like a normal function expression.
Horizontal aggregates are only allowed in certain syntactic contexts: LET,
FILTER statements or WHERE clauses.
Horizontal aggregation is especially useful when paired with a group variable. You can create a group variable inside a quantified path pattern in a linear graph query.
Syntactic restrictions
- The argument to the aggregate function must reference exactly one array-typed value.
- Can only be used in
LET,FILTERstatements orWHEREclauses. - Nesting horizontal aggregates is not allowed.
- Aggregate functions that support ordering (
ARRAY_AGG,STRING_AGG,ARRAY_CONCAT_AGG) can't be used as horizontal aggregate functions.
Examples
In the following query, the SUM function horizontally aggregates over an
array (arr), and then produces the sum of the values in arr:
GRAPH FinGraph
LET arr = [1, 2, 3]
LET total = SUM(arr)
RETURN total
/*-------+
| total |
+-------+
| 6 |
+-------*/
In the following query, the SUM function horizontally aggregates over an
array of structs (arr), and then produces the sum of the x fields in the
array:
GRAPH FinGraph
LET arr = [STRUCT(1 as x, 10 as y), STRUCT(2, 9), STRUCT(3, 8)]
LET total = SUM(arr.x)
RETURN total
/*-------+
| total |
+-------+
| 6 |
+-------*/
In the following query, the AVG function horizontally aggregates over an
array of structs (arr), and then produces the average of the x and y
fields in the array:
GRAPH FinGraph
LET arr = [STRUCT(1 as x, 10 as y), STRUCT(2, 9), STRUCT(3, 8)]
LET avg_sum = AVG(arr.x + arr.y)
RETURN avg_sum
/*---------+
| avg_sum |
+---------+
| 11 |
+---------*/
The following query produces an error because two arrays were passed into
the AVG aggregate function:
-- ERROR: Horizontal aggregation on more than one array-typed variable
-- is not allowed
GRAPH FinGraph
LET arr1 = [1, 2, 3]
LET arr2 = [5, 4, 3]
LET avg_val = AVG(arr1 + arr2)
RETURN avg_val
The following query demonstrates a common pitfall. All instances of the array that we're horizontal aggregating over are treated as a single element from that array in the aggregate.
The fix is to lift any expressions that want to use the array as is outside the horizontal aggregation.
-- ERROR: No matching signature for function ARRAY_LENGTH for argument types: INT64
GRAPH FinGraph
LET arr1 = [1, 2, 3]
LET bad_avg_val = SUM(arr1 / ARRAY_LENGTH(arr1))
RETURN bad_avg_val
The fix:
GRAPH FinGraph
LET arr1 = [1, 2, 3]
LET len = ARRAY_LENGTH(arr1)
LET avg_val = SUM(arr1 / len)
RETURN avg_val
In the following query, the COUNT function counts the unique amount
transfers with one to three hops between a source account (src) and a
destination account (dst):
GRAPH FinGraph
MATCH (src:Account)-[e:Transfers]->{1, 3}(dst:Account)
WHERE src != dst
LET num_transfers = COUNT(e)
LET unique_amount_transfers = COUNT(DISTINCT e.amount)
FILTER unique_amount_transfers != num_transfers
RETURN src.id as src_id, num_transfers, unique_amount_transfers, dst.id AS destination_account_id
/*---------------------------------------------------------------------------+
| src_id | num_transfers | unique_transfers_amount | destination_account_id |
+---------------------------------------------------------------------------+
| 7 | 3 | 2 | 16 |
| 20 | 3 | 2 | 16 |
| 7 | 2 | 1 | 20 |
| 16 | 3 | 2 | 20 |
+---------------------------------------------------------------------------*/
In the following query, the SUM function takes a group variable called
e that represents an array of transfers, and then sums the amount
for each transfer. Note that horizontal aggregation is not allowed in the
RETURN statement: that ARRAY_AGG is an aggregate over the result set.
GRAPH FinGraph
MATCH (src:Account {id: 7})-[e:Transfers]->{1,2}(dst:Account)
LET total_amount = SUM(e.amount)
RETURN
src.id AS source_account_id, dst.id AS destination_account_id,
ARRAY_AGG(total_amount) as total_amounts_per_path
/*---------------------------------------------------------------------+
| source_account_id | destination_account_id | total_amounts_per_path |
+---------------------------------------------------------------------+
| 7 | 16 | 300,100 |
| 7 | 20 | 600,400 |
+---------------------------------------------------------------------*/