Neo4j Cypher Aggregating Value Counts

Question

I am returning date that looks like this:

"Jonathan" | "Chicago" | 6 | ["Hot","Warm","Cold","Cold","Cold","Warm"]

Where the third column is a count of the values in column 4.

I want to extract values out of the collection in column 4 and create new columns based on the values. My expected output would be:

Hot | Cold | Warm with the values 1 | 3 | 2 representing the counts of each value.

My current query is match (p)-[]->(c)-[]->(w) return distinct p.name, c.name, count(w), collect (w.weather)

I'd imagine this is simple, but i cant figure it out for the life of me.

cybersam cybersam · Accepted Answer · 2020-11-10T04:19:09

Cypher does not have way to "pivot" data (as discussed here). That is in part because it does not support dynamically generating the names of return values (e.g., "Cold") -- and it is these names that appear as "column" headers in the Text and Table visualizations provided by the neo4j Browser.

However, if you know that you only have, say, 3 possible "weather" names, you can use a query like this, which hardcodes those names in the RETURN clause:

MATCH (c:City)-[:HAS_WEATHER]->(w:Weather)
WITH c, {weather: w.weather, count: COUNT(*)} AS weatherCount
WITH c, REDUCE(s = {Cold: 0, Warm: 0, Hot: 0}, x IN COLLECT(weatherCount) | apoc.map.setKey(s, x.weather, x.count)) AS counts
MATCH (p:Person)-[:LIVES_IN]->(c)
RETURN p.name AS pName, c.name AS cName, counts.Cold AS Cold, counts.Warm AS Warm, counts.Hot AS Hot

The above query efficiently gets the weather data for a city once (for all people in that city), instead of once per person.

The APOC function apoc.map.setKey is a convenient way to get a map with an updated key value.

Neo4j Cypher Aggregating Value Counts

1 Answers