Getting the max record with different values using cypher

Question

I have a graph with spatial data using the spatial plugin.

This graph has "Threat Zones" (Polygons) which can be on top of the other so they also have a z-index property.

Each "Threat Zone" is attached to 1 to N threat scenarios, sometimes multiple "Threat Zones" are attached to the same threat scenario, with different properties.

I'm trying to get the top threat zone for each threat scenario, based on the z-index, for a specific location.

This is my current query which is almost perfect:

MATCH (asset:Asset{name:'Asset Name'})-[]-(ara:AssetRiskAssessment)
WITH asset, ara
CALL spatial.intersects('threat_zones',asset.wkt) YIELD node 
WITH node, asset, ara
MATCH (node)<-[:FOR]-(tss:ThreatScenarioScore)-[]-(ts:ThreatScenario)
RETURN ts.name, max(node.zindex) AS zindex, tss.intention, tss.capability
ORDER BY ts.name, zindex

My problem - if I remove tss.intenion, tss.capability I'm getting what I'm looking for (each relevant threat scenario of the right zone) but what I need from that is the tss.intention and tss.capability. Since their values is different between zones the max function consider them as different records.

Is there a better way to use the max function to get what I want and / or use a nested query to extract the intention / capability (which is what I'm after)?

Answer 1

I think you are looking for an "arg max" style query. In this case, using collect is the way to go:

MATCH (asset:Asset {name:'Asset Name'})-[]-(ara:AssetRiskAssessment)
WITH asset, ara
CALL spatial.intersects('threat_zones',asset.wkt) YIELD node 
WITH node, asset, ara
MATCH (node)<-[:FOR]-(tss:ThreatScenarioScore)-[]-(ts:ThreatScenario)
WITH node, tss, ts
ORDER BY ts.name ASC, node.zindex DESC
WITH
  ts.name AS name,
  collect({
    zindex: node.zindex, intention: tss.intention, capability: tss.capability
  })[0] AS max
RETURN
  name,
  max.zindex AS zindex,
  max.intention AS intention,
  max.capability AS capability

This sorts the tuples according to their name (ascending), but more importantly, according to their zindex in a descending order. So when the zindex and tss properties are collected to a list, the first item (index [0]) will hold the elements with the maximum zindex value.