UNWIND result as table fields

(Gchokeen) #1


I have schema something like this


And I tried to build query as like this

MATCH (g:genre) 
WITH collect(g.name) as genres
UNWIND genres as genre
MATCH (a:Actor)->[:ACTED]->(m:Movie)-[t:type]->(g:genre{name:genre})
RETURN a.name,genre,count(t) as num

So this query returns result something like this

**name  genre  num**

vijay  comedy  2
vijay  Action   12
Jam  comedy  5
Jam  Horror    15

But is there a way to convert all the genre as field names like this

**name   comedy Action Horror**
Vijay        2           12      0
Jam         5              0       15

similar xl/csv format

So basically I'm trying to get the result values as field names. Is it possible in cypher?


(Andrew Bowman) #2

Not dynamically, variable names must be hard coded in the query.

You could instead output a single variable for a map containing dynamic keys, but you'll need APOC Procedures for the map functions that will let you assemble that.

An example of usage:

MATCH (a:Actor)-[:ACTED]->(:Movie)-[:type]->(g:genre)
WITH a, g, count(g) as count
WITH a, collect([g.name, count]) as genreCounts
RETURN a.name as actor, apoc.map.fromPairs(genreCounts) as genreCounts

(Gchokeen) #3

Hello Andrew,

It's great query and returns a json format result genreCounts column, but I was expecting XL header and value kind of response

So basically, i need to get result something like this

return actor, comedy, Action, Horror

it above return statement actor static, but I wanna geners as dynamic comedy, Action, Horror

Idea is to generate csv from the cypher query, Since it's json or array structure I ended up lots of client side code to flattern and create csv with headers, so I'm looking for a way to do that from cypher.

Thanks again


(Andrew Bowman) #4

Again, variables (columns) can't be dynamic. You've got to know what they are ahead of time. If you already know the genres that can possibly exist, then you can hardcode each genre and do counts for each. But a dynamic approach for variable names isn't possible.