Cypher query: Assigning an aggregate value as a property of a relationship

Question

Following on from this question, I have the following toy example:

CREATE (A1:Worker {ref:"A1"})
CREATE (A2:Worker {ref:"A2"})

CREATE (B1:Worker {ref:"B1"})
CREATE (B2:Worker {ref:"B2"})

CREATE (A1)-[:StreamsTo {type:"stream1"}]->(B1)
CREATE (A1)-[:StreamsTo {type:"stream2"}]->(B1)
CREATE (A1)-[:StreamsTo {type:"stream3"}]->(B1)
CREATE (A1)-[:StreamsTo {type:"stream1"}]->(B2)
CREATE (A1)-[:StreamsTo {type:"stream2"}]->(B2)

CREATE (A2)-[:StreamsTo {type:"stream1"}]->(B1)
CREATE (A2)-[:StreamsTo {type:"stream1"}]->(B2)
CREATE (A2)-[:StreamsTo {type:"stream2"}]->(B2);

This produces the following graph:

What I want to do is calculate the routing probability (RP) for each stream type leaving a source node, which is 1 / the number of outgoing relationships of that type. I can calculate the RP values with this query:

MATCH (source:Worker)-[st:StreamsTo]->(:Worker) 
RETURN source.ref as Worker, 
       st.type as StreamType, 
       COUNT(st) as OutgoingCount,
       1.0 / COUNT(st) as RP
ORDER BY source.ref, st.type;

Which results in:

╒════════╤════════════╤═══════════════╤════╕
│"Worker"│"StreamType"│"OutgoingCount"│"RP"│
╞════════╪════════════╪═══════════════╪════╡
│"A1"    │"stream1"   │2              │0.5 │
├────────┼────────────┼───────────────┼────┤
│"A1"    │"stream2"   │2              │0.5 │
├────────┼────────────┼───────────────┼────┤
│"A1"    │"stream3"   │1              │1   │
├────────┼────────────┼───────────────┼────┤
│"A2"    │"stream1"   │2              │0.5 │
├────────┼────────────┼───────────────┼────┤
│"A2"    │"stream2"   │1              │1   │
└────────┴────────────┴───────────────┴────┘

So what I now need to do it take the calculated RP value for each source node (Worker) and stream type combination and add that value as a property of each relationship of that type leaving the given source node.

I know I can use WITH to pass the output of the query above to a new query. However, I as stuck on how to go about applying the aggregate RP value to all the relationships from a given source with a given property? I have seen that in cypher that you can collect the results into a map, but I am not sure how to use that in a query?

I can do this operation on the application side but would obviously prefer to do this server side if possible.

Answer 1

Here is a solution using collect() and UNWIND:

MATCH (source:Worker)-[st:StreamsTo]->(:Worker) 
// do the calculations, collection st relations into sts array
WITH collect(st) as sts, source.ref as Worker, 
       st.type as StreamType, 
       COUNT(st) as OutgoingCount,
       1.0 / COUNT(st) as RP

// expanding sts list to st rows
UNWIND sts as st
// set the property sr.RP = RP
set st.RP = RP

// collect st again to avoid a cartesian product
WITH collect(st) as sts, Worker, StreamType, OutgoingCount, RP

// return desired data, ordering
return Worker, StreamType, OutgoingCount, RP
ORDER BY Worker, StreamType