Neo4j Cypher - How to Count Multiple Property Values With Cypher Efficiently And Paginate Properly

Question

I am struggling to get the proper cypher that is both efficient and allows pagination through skip and limit.

Here is the simple scenario: I have the related nodes (company)<-[A]-(set)<-[B]-(job) where there are multiple instances of (set) with distinct (job) instances related to them. The (job) nodes have a specific status property that can hold one of several states. We need to count the number of (job) nodes in a particular state per (set) and use skip and limit to paginate on the distinct (set) nodes.

So we can get a very efficient query for job.status counts using this.

match (c:Company {id: 'MY.co'})<-[:type_of]-(s:Set)<-[:job_for]-(j:Job) 
return s.Description, j.Status, count(*) as StatusCount;

Which will give us a rows of the Set.Description, Job.Status, and JobStatus count. But we will get multiple rows for the Set based on the Job.Status. This is not conducive to paging over distinct sets though. Something like:

s.Description        j.Status       StatusCount
-------------------+--------------+----------------
Set 1              | Unassigned   | 10
Set 1              | Completed    | 2
Set 2              | Unassigned   | 3
Set 1              | Reviewed     | 10
Set 3              | Completed    | 4
Set 2              | Reviewed     | 7

What we are trying to achieve with the proper cypher is result rows based on distinct Sets. Something like this:

s.Description        Unassigned      Completed    Reviewed
-------------------+--------------+-------------+----------
Set 1              | 10           | 2           | 10
Set 2              | 3            | 0           | 7
Set 3              | 0            | 4           | 0

This would then allow us to paginate over Sets using skip and limit properly.

I have tried many different approaches and cannot seem to find the right combination for this type of result. Anyone have any ideas? Thanks!

** EDIT - Using the answer provided by MIchael, here's how to get the status count values in java **

match (c:Company {id: 'MY.co'})<-[:type_of]-(s:Set)<-[:job_for]-(j:Job) 
with s, j.Status as Status,count(*) as StatusCount
return s.Description, collect({Status:Status,StatusCount:StatusCount]) as StatusCounts;


   List<Object> statusMaps =  (List<Object>) row.get("StatusCounts");
   for(Object statusEntry : statusMaps ) {
     Map<String,Object> statusMap = (Map<String,Object>) statusEntry;
     String status = (String) statusMap.get("Status");
     Number count = statusMap.get("StatusCount");
    }

Michael Hunger Michael Hunger · Accepted Answer · 2015-05-15T18:25:46

You can use WITH and aggregation, and optionally a map result

match (c:Company {id: 'MY.co'})<-[:type_of]-(s:Set)<-[:job_for]-(j:Job) 
with s, j.Status as Status,count(*) as StatusCount
return s.Description, collect([Status,StatusCount]);

or

match (c:Company {id: 'MY.co'})<-[:type_of]-(s:Set)<-[:job_for]-(j:Job) 
with s, j.Status as Status,count(*) as StatusCount
return s.Description, collect({Status:Status,StatusCount:StatusCount]);

Neo4j Cypher - How to Count Multiple Property Values With Cypher Efficiently And Paginate Properly

1 Answers