Using Aggregate Functions in SQL Graph Queries

5.5 Using Aggregate Functions in SQL Graph Queries

You can use aggregate functions in a SQL graph query to obtain an aggregated output.

Both SQL built-in Aggregate Functions and user-defined aggregates are supported. These functions can be included in both fixed length and variable length path patterns in a SQL graph query.

The aggregate functions can be applied in the COLUMNS clause or in the graph pattern WHERE clause of the SQL graph query. For instance, consider the following sample query:

SELECT *
FROM GRAPH_TABLE ( g
       MATCH (v1) (-[e]->(v2)){1,2}
       COLUMNS (LISTAGG(v2.id, ',') AS id_list)

The preceding graph query describes a variable length path pattern having {1,2} as the quantifier. The LISTAGG aggregate function is used in the COLUMNS clause to list all the ids along a path.

Similarly, you can also apply aggregations in a fixed length path pattern as shown:

SELECT *
FROM GRAPH_TABLE ( g
       MATCH (v1) (-[e]->(v2)){2}
       WHERE AVG(v2.age) >= 30
       COLUMNS (LISTAGG(v2.id, ',') AS id_list)

The preceding graph query describes a fixed length path pattern. The AVG aggregate used in the WHERE clause determines only those paths where the average age >= 30 condition is met. The resulting query output is a list of ids along a path.

See Example 5-15 for example queries using aggregations.