CODEWITHSUNDEEP
neo4jcypher
kami
kami

Reputation: 361

Combine the output of two Cypher statements with aggregation

I have separate cypher queries as follows:

Q1: START m=node(30) MATCH (m)-[d:define]->(n) where d.start=4 RETURN n.name,COUNT(d) as count
Q2: START m=node(30) MATCH (m)-[d:define]->(n) where d.start=5 RETURN n.name,COUNT(d) as count

which return the following sample results:

from Q1: 

a | 2
b | 1
c | 4
d | 3

from Q2:

a | 1
b | 1
c | 4
e | 2

I can process the results offline and generate a combined count, but can I combine the two cypher queries above so that I can get the following merged counts from d.start 4 and 5?

a | 2 | 1
b | 1 | 1
c | 4 | 4
d | 3 | 0
e | 0 | 2

What I have so far: how can I continue on the name and the count(d) to the next phase?

START m=node(30) 
MATCH (m)-[d:define]->(n) 
where d.start=4
WITH m, COLLECT (n.name, COUNT(d)) as from1
MATCH (m)-[d:define]->(n) 
where d.start=5
WITH m, COLLECT (n.name, COUNT(d)) as from2

A union doesn't work for me as I don't want to just union the results together. I need to separate the counts coming from d.start=4 and 5 resulting in more like an intersection.

Upvotes: 2

Views: 1733

Answers (3)

cybersam
cybersam

Reputation: 66957

This query should do what you want:

MATCH (m)-[d:define]->(n)
WHERE ID(m) = 30 AND d.start IN [4, 5]
RETURN n.name AS name, REDUCE(c = [0, 0], x IN COLLECT(d.start) |
  CASE WHEN x = 4 THEN [c[0]+1, c[1]] ELSE [c[0], c[1]+1] END) AS counts
ORDER BY name;

You can test it by first creating your sample data:

CREATE (m:Foo), (a:Foo {name: 'a'}), (b:Foo {name: 'b'}), (c:Foo {name: 'c'}), (d:Foo {name: 'd'}), (e:Foo {name: 'e'}),
  (m)-[:define {start:4}]->(a),
  (m)-[:define {start:4}]->(a),
  (m)-[:define {start:4}]->(b),
  (m)-[:define {start:4}]->(c),
  (m)-[:define {start:4}]->(c),
  (m)-[:define {start:4}]->(c),
  (m)-[:define {start:4}]->(c),
  (m)-[:define {start:4}]->(d),
  (m)-[:define {start:4}]->(d),
  (m)-[:define {start:4}]->(d),
  (m)-[:define {start:5}]->(a),
  (m)-[:define {start:5}]->(b),
  (m)-[:define {start:5}]->(c),
  (m)-[:define {start:5}]->(c),
  (m)-[:define {start:5}]->(c),
  (m)-[:define {start:5}]->(c),
  (m)-[:define {start:5}]->(e),
  (m)-[:define {start:5}]->(e);

With the above data (and assuming that the query uses the correct ID for m), the query returns:

+---------------+
| name | counts |
+---------------+
| "a"  | [2,1]  |
| "b"  | [1,1]  |
| "c"  | [4,4]  |
| "d"  | [3,0]  |
| "e"  | [0,2]  |
+---------------+

Upvotes: 2

Gabor Szarnyas
Gabor Szarnyas

Reputation: 5047

I believe you can ditch COLLECT and only use COUNT.

Example dataset:

CREATE
  (m {name: 1}),
  (m)-[:define {start: 4}]->({name: 2}),
  (m)-[:define {start: 4}]->({name: 3}),
  (m)-[:define {start: 5}]->({name: 4})

Query:

MATCH (m)-[d:define]->()
WHERE d.start=4 
WITH m, COUNT(d) as from1
MATCH (m)-[d:define]->()
WHERE d.start=5
RETURN m.name, from1, COUNT(d) as from2

Result:

╒══════╤═════╤═════╕
│m.name│from1│from2│
╞══════╪═════╪═════╡
│1     │2    │1    │
└──────┴─────┴─────┘

Upvotes: 0

Tore Eschliman
Tore Eschliman

Reputation: 2507

MATCH (n)
WHERE ID(n) = 30
MATCH (m)-[d:define]->(n) 
where d.start=4
WITH m, COLLECT([n.name, COUNT(c)]) as from1
MATCH (m)-[d:define]->(n) 
where d.start=5
WITH m, from1 + COLLECT([n.name, COUNT(c)]) as from2
UNWIND from2 AS row
WITH row[0] AS name, row[1] AS ct
RETURN name, SUM(ct)

Though I'm not totally sure if this will return the counts you expect, because you start by finding n, but then carry m forward instead, so that (m)-[d]->(n) path will match fundamentally different patterns. You're on the right track, though; just keep adding things to a collection until you've got all your result rows, then unwind the one big collection and perform your aggregation or other analysis.

Upvotes: 1

Related Questions