Pivoting data in Cypher

Question

I've just gotten into working with a Neo4J database, and I'm having a hard time figuring out a good way to pivot some data that I'm working with.

I have a basic query that looks like this: MATCH (n:DATA) WHERE n.status =~ "SUCCESS" return n.group as Group, n.label as Label, avg(toFloat(n.durationMillis)/60000) as Minutes, which produces tall narrow data like this:

|Group  |Label  |Minutes|
|-------|-------|-------|
|group1 |label1 |1.0    |
|group1 |label2 |2.0    |
|group1 |label3 |5.0    |
|group2 |label1 |3.0    |
|group2 |label3 |2.0    |
...

What I would like to do is pivot this data to provide a short wide view as a summary table:

| Group | label1 | label2 | label3 |
| ----- | ------ | ------ | ------ |
|group1 | 1.0    | 2.0    | 5.0    |
|group2 | 3.0    | -      | 2.0    |
...

Is there a simple way to do this with Cypher?

Answer 1

In order for Neo4j tools (like the Neo4j Browser) to generate a visualization that looks like a pivot table from a Cypher query, the query would have to hardcode the headings for each "column" -- since a Cypher query cannot dynamically generate the names of the values it returns. That is, your RETURN clause would have to look something like RETURN Group, label1, label2, label3.

Now, if you do happen to know all the possible labels beforehand, then you can indeed perform a simple query that returns your pivot table. For example:

MATCH (n:DATA)
WHERE n.status =~ "SUCCESS"
WITH n.group as Group, n.label AS l, AVG(n.durationMillis/60000.0) AS m
WITH Group, apoc.map.fromLists(COLLECT(l), COLLECT(m)) AS lmMap
RETURN Group,
  lmMap['label1'] AS label1,
  lmMap['label2'] AS label2,
  lmMap['label3'] AS label3

The APOC function apoc.map.fromLists returns a map generated from lists of keys and values. If a Group does not have a particular label, its cell value will be null.