2

I am trying to figure out how to aggregate the result of Union in Cypher. The following example can be executed in Cypher snippets live:

MATCH (n:Crew)-[r:KNOWS]->m
WHERE n.name='Neo'
RETURN n AS name,m
UNION
MATCH (n:Crew)-[r:KNOWS]->m
WHERE n.name='Morpheus'
RETURN n AS name,m

This query shows three rows as result because Neo knows one person and Morpheus two (note the directional link in the query). Let's say that we want to aggregate the known people. We can do something like this:

MATCH (n:Crew)-[r:KNOWS]->m
WHERE n.name='Neo'
RETURN n AS name,count(m) AS c
UNION
MATCH (n:Crew)-[r:KNOWS]->m
WHERE n.name='Morpheus'
RETURN n AS name,count(m) AS c

So far we are Ok. However, I don't know how to solve the problem if what we want to aggregate (the hidden Group By) is in both the first and second query. Since this doesn't happen in the previous case, let's assume for the sake of the explanation that we have the following queries:

MATCH (n:Label1)-[:label3]->(:label4)
RETURN n.name as name, n.age as value

and

MATCH (m:Label2)-[:label5]->(:label6)
RETURN m.surname as name, m.k as value

which return

John, 12

Sam, 17

and

John, 78

Tim, 12

Is it possible to do something like

(
MATCH (n:Label1)-[:label3]->(:label4)
RETURN n.name as name, n.age as value
UNION ALL
MATCH (m:Label2)-[:label5]->(:label6)
RETURN m.surname as name, m.k as value
)
RETURN name, sum(value)

to obtain the result below?

John, 90
Sam, 17
Tim, 12

Obviously, I already tried such a query and it doesn't compile. Hence, I am wondering if there is something similar.

Inti Gonzalez-Herrera
  • 308
  • 3
  • 13

2 Answers2

0

As of today you cannot do any aggregation on the combined result set of a UNION.

The only way is to trick around it by avoiding the UNION in favour of a more complex WHERE:

MATCH (n)-[r:label3|label5]->(m)
WHERE ((type(r)='label3') AND ("Label1" in labels(n)) AND ("label4" in labels(m))) OR
((type(r)='label5') AND ("Label2" in labels(n)) AND ("label6" in labels(m)))
RETURN n.name as name, sum(n.age)
Stefan Armbruster
  • 39,465
  • 6
  • 87
  • 97
  • Ok, I see your point. However, your answer is not complete because in my example the two queries use different properties (e.g., age vs. k, name vs. surname). I think it should be Ok using a CASE, but I am not sure how to do it for the sum. – Inti Gonzalez-Herrera Mar 30 '15 at 13:47
0

As you can see here, you can use COLLECT and UNWIND clause:

MATCH (n:Crew)-[r:KNOWS]->(m)
WHERE n.name='Neo'
WITH COLLECT({name:n, id:ID(m)}) as rows

MATCH (n:Crew)-[r:KNOWS]->(m)
WHERE n.name='Morpheus'
WITH rows + COLLECT({name:n,id:ID(m)}) as allRows
UNWIND allRows as row
RETURN row.name, count(disntict(row.id))
Xavi Torrens
  • 337
  • 1
  • 12