Return node after aggregation in Cypher

Question

I am having a hard time understanding how to properly use aggregate functions in Cypher.

Let say I have nodes labelled as Animal, with properties size and species. For each species, I want to get the largest.

So far, I understand I can do it with the following :

MATCH (n:Animal)
WITH n.species as species, max(n.size) as size
RETURN species, size

And I will effectively get the largest sizes with corresponding species.

But how can I get the nodes instead of species ?

I can't return n because of the WITH statement, and I can't inject it into the WITH because it will break species aggregation.

I know this question has already been asked a few times, but the different solutions I came accross were case-specific and used relations

Any advice is very welcome

EDIT: I finally made it work with :

MATCH (n:Animal)
WITH n.species as species, max(n.size) as size, collect(n) as ns
UNWIND ns as n
WITH n
WHERE n.size = size
RETURN n

Is this the Cypher-way to settle things ? Seems a bit verbose and not efficient (all nodes are fetched here) to me, isn't there a more straightforward option ?

user_974731 · Answer

Since the MAX aggregation function does not return the node with the max value, you should not use it. Otherwise, you'd have to test the size of every animal twice to get both the max value and the node of interest (as you discovered).

You can instead use the REDUCE function to test the size of every animal just once:

MATCH (n:Animal)
WITH n.species AS species, COLLECT(n) as ns
RETURN species, REDUCE(s = {size: -1}, a IN ns |
  CASE WHEN a.size > s.size THEN {size: a.size, a: a} ELSE s END
) AS result;

user_92359 · Answer

This is a frequently encountered limitation with our max() and min() aggregation functions, so we added an APOC function that can help: apoc.agg.maxItems():

apoc.agg.maxItems(item, value, groupLimit: -1) - returns a map {items:[], value:n} where value is the maximum value present, and items are all items with the same value. The number of items can be optionally limited.

MATCH (n:Animal)
WITH n.species as species, apoc.agg.maxItems(n.size, n) as sizeData
RETURN species, sizeData.value as size, sizeData.items as animals

Return node after aggregation in Cypher

2 Answers