Using aggregating function with condition

[ri8ika]

I have nodes like:

(n:Node) - [:HAS] -> (:Tasty {type: 'added', count: 5})
(n:Node) - [:HAS] -> (:Tasty {type: 'added', count: 8})
(n:Node) - [:HAS] -> (:Tasty {type: 'eaten', count: 3})
(n:Node) - [:HAS] -> (:Chocolate {type: 'added', count: 15})
(n:Node) - [:HAS] -> (:Chocolate {type: 'added', count: 5})
(n:Node) - [:HAS] -> (:Candy {type: 'added', count: 235})
...
...
...

Expected result: sum(count of added - count of eaten)

{
  "Tasty": 10,
  "Chocolate": 20,
  "...": ...,
  ...
}

Query may be simple like?

MATCH (n:Node) - [:HAS] -> (m)
WITH m, sum(m.count of added - m.count of eaten) as remaining_count
RETURN {m: remaining_count}

Is the desired result possible just with neo4j cypher only?

[stefan_armbrust]

Have not tried myself, but I guess you need to aggregate on labels(m) instead of m:

MATCH (n:Node) - [:HAS] -> (m)
WITH labels(m)[0] as label, sum(m.count - m.eat_count) as remaining_count
RETURN {label: remaining_count}

if you have non existing properties for count and eat_count you might need to wrap it into coalesce.

[andrew_bowman]

It's better to sum each count separately and subtract the sum of eat_count from the sum of m.count.

The tricky part of your requirement is having dynamic keys. This isn't something Cypher can currently due (Stefan's query would have a literal "label" key).

You could have the label as a property, but not as a key, and collect those:

MATCH (n:Node)-[:HAS]->(x)
WITH n, labels(x)[0] as label, sum(x.count) - sum(x.eat_count) as left
RETURN collect({label:label, left:left}) as result

With APOC you have some more options for getting the keys set accordingly:

MATCH (n:Node)-[:HAS]->(x)
WITH n, labels(x)[0] as label, sum(x.count) - sum(x.eat_count) as left
RETURN apoc.map.fromPairs(collect([label, left])) as result

[andrew_bowman · Accepted solution]

Okay, so with your changes the query will be a bit more challenging, as we will need a CASE to property negate the count when the type is eaten.

MATCH (n:Node)-[:HAS]->(x)
WITH n, labels(x)[0] as label, x.type as type, sum(x.count) as count
WITH n, label, sum(CASE WHEN type = 'eaten' THEN -1 * count ELSE count END) as left
RETURN n, collect({label:label, left:left}) as result

And with APOC:

MATCH (n:Node)-[:HAS]->(x)
WITH n, labels(x)[0] as label, x.type as type, sum(x.count) as count
WITH n, label, sum(CASE WHEN type = 'eaten' THEN -1 * count ELSE count END) as left
RETURN n, apoc.map.fromPairs(collect([label, left])) as result

[ri8ika]

I will try. Thanks.
Can we not do -count instead of -1 * count?

[andrew_bowman]

Yes, you can do that instead.

[ri8ika]

Ah, sorry. I've wrongly posted my nodes. I will update my post.

[ri8ika]

I have updated my question. Sorry for this mistake.

[ri8ika]

You mean we can't get expected result like below as of current cypher?:

{
  "Tasty": 10,
  "Chocolate": 20,
  "...": ...,
  ...
}

Hmm, no dynamic properties are allowed as of now.

Anyways, your solution is also looks fine.

[ri8ika]

BTW, any plan for the dynamic key in future?

If implemented, I think using bracket operator would be good to go with:

{[label]:label}