CODEWITHSUNDEEP
neo4jcypher
analyticsPierce
analyticsPierce

Reputation: 3025

How to aggregate results in a collection in Cypher?

I am working on a cypher query (Neo4j v2.1.3) where I need to aggregate the results from several collections for use in a separate analysis. I am starting with the following query where I am returning all the Person nodes between the ones we are interested in and a specific Host.

MATCH (person:person), (host:person {name:'Host'}),
p = shortestPath((person)-[*]-(host))
WHERE person.name IN ['Steve','Jane']
RETURN collect(extract(n IN nodes(p)| n.name))

This returns a collection that includes the results from both person.name values in my WHERE condition.

Bob, Jordan, John, Jane, Lisa, Robert, John, Bob, John, Lisa, Bob, Lisa

What I want is to aggregate the values and list a count of how many each was listed ordered by descending count.

Bob, 3
John, 3
Lisa, 3
Robert, 1
Jordan, 1
Jane, 1

How can I add to my query to do this in one cypher statement?

UPDATE: From @Luanne's answer, I had to make an adjustment to get it to work correctly.

MATCH (person:person), (host:person {name:'Host'}),
p = shortestPath((person)-[*]-(host))
WHERE person.name IN ['Steve','Jane']
WITH collect(extract(n IN nodes(p)| n.name)) as nameList 
UNWIND nameList AS name
UNWIND name as ind_name
WITH ind_name, count(*) AS count
RETURN ind_name,count
ORDER BY count DESC

Upvotes: 2

Views: 2902

Answers (1)

Luanne
Luanne

Reputation: 19373

You can unwind your collection to count names:

MATCH (person:person), (host:person {name:'Host'}),
p = shortestPath((person)-[*]-(host))
WHERE person.name IN ['Steve','Jane']
with collect(extract(n IN nodes(p)| n.name)) as nameList 
unwind nameList AS name
WITH name, count(*) AS count
RETURN name,count
ORDER BY count DESC

Upvotes: 3

Related Questions