Neo4j Cypher Query - return counts of relationships in separate columns

Question

I have experience with Neo4j and Cypher, but still struggle with aggregate functions. I'm trying to pull a CSV out of Neo4j that should look like this:

Location	Number of Node X at Location	Number of Node Y at Location
ABC	5	20
DEF	39	4
Etc.	#	#

My current query looks like this:

MATCH (loc:Location)--(x:Node_X)
RETURN loc.key AS Location, count(x) AS `Number of Node X at Location`, 0 AS `Number of Node Y at Location`
UNION
MATCH (loc:Location)--(y:Node_Y)
RETURN loc.key AS Location, 0 AS `Number of Node X at Location`, count(y) AS `Number of Node Y at Location`

Which yields a table like:

Location	Number of Node X at Location	Number of Node Y at Location
ABC	5	0
DEF	39	0
Etc.	#	#
ABC	0	20
DEF	0	4
Etc.	#	#

I think I'm close, but I have double the number of Location rows as I need, and am not sure how to make the results more succinct. Suggestions on this and generally tips for aggregate functions are appreciated!

Graphileon · Answer 1 · 2022-03-18T20:01:39.427

1

I think You can solve it like this with even when counts are 0

MATCH (loc:loc1)
RETURN loc.type , 
       size((loc)--(:Node_X)) AS xCount,
       size((loc)--(:Node_Y)) AS yCount

You can also do

MATCH (loc:loc1)
RETURN loc.type , 
       size([(loc)—-(x:Node_X) | x]) AS xCount

edited Mar 18 '22 at 20:01

answered Mar 18 '22 at 19:21

Graphileon

5,275
3
17
31

That seems to work! I do get a warning about this being a deprecated feature that will be removed in future versions. So someone looking at this in the future might not be able to use this solution. – MGipson Mar 18 '22 at 19:41
Which part is being up for deprecation? – Graphileon Mar 18 '22 at 19:57
Added another option to avoid the deprecation warning. Not as succinct though – Graphileon Mar 18 '22 at 20:02

score 0 · Answer 2 · answered Mar 18 '22 at 17:58

You can aggregate with distinct here.

MATCH (loc:loc1)--(x:Node_X), (loc)--(y:Node_Y) 
RETURN loc.key , 
       count(distinct(x)) as NODES_OF_TYPE_X, 
       count(distinct(y)) as NODES_OF_TYPE_Y

The problem in accessing x and y in above query is it changes the cardinality of the solution. For each solution of x, it will have all the solutions of y. If you had n1 x nodes and n2 y nodes and you don't use distinct, then you would get n1*n2 nodes for each x and y.

That almost works, but if there are 0 matches of x then that location is not included in the final output. — MGipson, Mar 18 '22 at 18:28

score 0 · Answer 3 · answered Mar 18 '22 at 19:29

@PrashantUpadhyay got the answer started, but I think this is the final answer I was looking for. It accounts for cases where counts may return zero, but still includes all Location rows.

MATCH (loc:Location)
OPTIONAL MATCH (loc)--(x:Node_X)
OPTIONAL MATCH (loc)--(y:Node_Y)
RETURN loc.key AS Location, 
       coalesce(count(distinct(x)), 0) as Node_X, 
       coalesce(count(distinct(y)), 0) as Node_Y
ORDER BY Location

score 0 · Answer 4 · answered Mar 18 '22 at 20:45

I'd try this:

MATCH (loc:Location)
with distinct loc
OPTIONAL MATCH (loc)--(x:Node_X)
WITH distinct loc, count(x) AS xnum
OPTIONAL MATCH  (loc)--(y:Node_Y)
WITH DISTINCT loc, count(y) AS ynum, xnum

RETURN 
DISTINCT loc.key as Location, 
xnum as `Number of Node X at Location`, 
ynum as `Number of Node Y at Location`

Neo4j Cypher Query - return counts of relationships in separate columns

4 Answers4