Neo4j / Cypher: Returning sum of value in relationship between nodes within the node itself

Question

There are two node types, Account and Transfer. A Transfer signifies movement of funds between Account nodes. Transfer nodes may have any number of input and output nodes. For example, three Accounts could each send $40 ($120 combined) to sixteen other Accounts in any way they please and it would work.

The Transfer object, as is, does not have the sum of the funds sent or received - those are only stored in the relationships themselves. I'd like to calculate this in the cypher query and return it as part of the the returned Transfer object, not separately. (Similar to a SQL JOIN)

I'm rather new to Neo4j + Cypher; So far, the query I've got is this:

MATCH (tf:Transfer {id:'some_id'})
MATCH (tf)<-[in:IN_TO]-(in_account:Account)
MATCH (tf)-[out:OUT_TO]->(out_account:Account)
RETURN tf,in_account,in,out_account,out, sum(in.value) as sum_in, sum(out.value) as sum_out

If I managed this database, I'd just precalculate the sums and store it in the Transfer properties - but that's not an option at this time.

tl;dr: I'd like to store sum_in and sum_out in the returned tf object.

Answer 1

Tore Eschliman's answer is very insightful, especially on the properties of aggregated aliases.

I came up with a more hackish solution, that could work in this case.

Example data set:

CREATE
  (a1:Account),
  (a2:Account),
  (a3:Account),
  (tf:Transfer),
  (a1)-[:IN_TO {value: 110}]->(tf),
  (a2)-[:IN_TO {value: 230}]->(tf),
  (tf)-[:OUT_TO {value: 450}]->(a3)

Query:

MATCH (in_account:Account)-[in:IN_TO]->(tf:Transfer)
WITH tf, SUM(in.value) AS sum_in
SET tf.sum_in = sum_in
RETURN tf
UNION
MATCH (tf:Transfer)-[out:OUT_TO]->(out_account:Account)
WITH tf, SUM(out.value) AS sum_out
SET tf.sum_out = sum_out
RETURN tf

Results:

╒═══════════════════════════╕
│tf                         │
╞═══════════════════════════╡
│{sum_in: 340, sum_out: 450}│
└───────────────────────────┘

Note that UNION performs a set union (as opposed to UNION ALL, which performs a multiset/bag union), hence we will not have duplicates in the results.

Update: as Tore Eschliman pointed out in the comments, this solution will modify the database. As a workaround, you can collect the results and abort the transaction afterwards.

Answer 2

When you use an aggregation like SUM, you have to leave the aggregated aliases out of your result row, or you'll end up with single-row sums. This should help you get something closer to what you want, including a workaround for your dynamic property assignment:

CREATE (temp)
WITH temp
MATCH (tf:Transfer {id:'some_id'})
MATCH (tf)<-[in:IN_TO]-(in_account:Account)
MATCH (tf)-[out:OUT_TO]->(out_account:Account)
SET temp += PROPERTIES(tf)
WITH temp, SUM(in.value) AS sum_in, SUM(out.value) AS sum_out, COLLECT(in_account) AS in_accounts, COLLECT(out_account) AS out_accounts 
SET temp.sum_in = sum_in
SET temp.sum_out = sum_out
WITH temp, PROPERTIES(temp) AS props, in_accounts, out_accounts
DELETE temp
RETURN props, in_accounts, out_accounts

You are creating a dummy node to hold properties, because that's the only way to assign dynamic properties to existing Maps or Map-alikes, but the node won't ever be committed to the graph. This query should return a Map of the :Transfer's properties, with the in- and out-sums included, plus lists of the in- and out-accounts in case you need to do any additional work on them.