How to aggregate across elements in list in neo4j

Question

I'm a complete beginner to neo4j and cypher queries. I have a scenario where two Person nodes James and Karen have 3 nodes in between them. The relationships between all nodes have a property called weight:

CREATE 
(j:Person { name: "James" }),
(m:Person { name: "Mary" }),
(d:Person { name: "David" }),
(e:Person { name: "Emily" }),
(t:Person { name: "Todd" }),
(x:Person { name: "Xavier" }),
(k:Person { name: "Karen" }),

(j)-[:KNOWS{weight: 32}]->(m),
(j)-[:KNOWS{weight: 10}]->(d),
(j)-[:KNOWS{weight: 50}]->(e),
(j)-[:KNOWS{weight: 86}]->(t),
(j)-[:KNOWS{weight: 86}]->(x),

(d)-[:KNOWS{weight: 12}]->(e),

(k)-[:KNOWS{weight: 76}]->(e),
(k)-[:KNOWS{weight: 8}]->(t),
(k)-[:KNOWS{weight: 2}]->(x)

I want to rank these people who know James and Karen using a calculated score from the property of the relationship, which is defined as (x - min(x)/max(x) - min(x), some form of normalization i.e.

MATCH (:Person{name:'James'})-[:KNOWS]->(:Person)<-[r1:KNOWS]-(:Person{name: 'Karen'})
WITH collect(r1.weight) as a
WITH  apoc.coll.min(a) as min_val, apoc.coll.max(a) as max_val
MATCH (:Person{name:'James'})-[:KNOWS]->(p:Person)<-[r2:KNOWS]-(:Person{name: 'Karen'})
WITH p, r2, (r2.weight - min_val)/(max_val - min_val) as score
RETURN p, r2, score
ORDER BY score DESC

╒═════════════════╤═════════════╤═══════╕
│"p"              │"r2"         │"score"│
╞═════════════════╪═════════════╪═══════╡
│{"name":"Emily"} │{"weight":76}│1      │
├─────────────────┼─────────────┼───────┤
│{"name":"Xavier"}│{"weight":2} │0      │
├─────────────────┼─────────────┼───────┤
│{"name":"Todd"}  │{"weight":8} │0      │
└─────────────────┴─────────────┴───────┘

Is there a better way to do this without having to repeat the process of finding the matching nodes i.e. MATCH (:Person{name:'James'})-[:KNOWS]->(p:Person)<-[r2:KNOWS]-(:Person{name: 'Karen'})

UPDATE This is another attempt

MATCH (:Person{name:'James'})-[:KNOWS]->(p:Person)<-[r:KNOWS]-(:Person{name: 'Karen'})
WITH r, apoc.map.fromLists(["name", "r_weight"],[p.name, r.weight]) as person
WITH collect(person) as people, collect(r.weight) as w
WITH people, apoc.coll.min(w) as min_val, apoc.coll.max(w) as max_val 
UNWIND people as pp
WITH pp, (pp.r_weight - min_val)/(max_val - min_val) as score
RETURN pp, score
ORDER BY score DESC

╒══════════════════════════════╤═══════╕
│"pp"                          │"score"│
╞══════════════════════════════╪═══════╡
│{"name":"Emily","r_weight":76}│1      │
├──────────────────────────────┼───────┤
│{"name":"Xavier","r_weight":2}│0      │
├──────────────────────────────┼───────┤
│{"name":"Todd","r_weight":8}  │0      │
└──────────────────────────────┴───────┘

Any cleaner approach than my crude way?

user_12205676 · Answer

I usually UNWIND on a RANGE to deal with parallel lists. This is how I would do it.

MATCH (:Person{name:'James'})-[r1:KNOWS]->(p:Person)<-[r2:KNOWS]-(:Person{name: 'Karen'})
WITH p, r1.weight + r2.weight AS weight
WITH COLLECT(p) AS people, 
COLLECT(weight) as weights,
max(weight) AS maxWeight, 
min(weight) AS minWeight
UNWIND RANGE(0, SIZE(people)-1) AS idx
RETURN people[idx].name as person, 
weights[idx] AS weight,
toFloat(weights[idx] - minWeight)/(maxWeight - minWeight) AS score
ORDER BY score desc

How to aggregate across elements in list in neo4j

1 Answers