Query that returns spreasheet coulums

[r_chevalier335]

I have a bunch of parts with child features. There are many versions(instances) of each part. Each instance will have a different value for each feature. I want to display each instance and it's values as a column in a table.

I can return all the Nodes and Instances with:
MATCH tree = (:Node{ name: "P 1" })<-[:CHILD_OF_NODE *0..]-(c:Node) WITH tree, (c)<-[:INSTANCE_OF_NODE]-(:Instance) as instance return tree, instance

Two columns are returned. In this example both of my Instances are combined in the second column. How do I return a column for each instance?

[asperlinga]

Hi [r.chevalier335],

If the graph has a hierarchical structure it might help to create a Level property in the nodes.

Then you can write cypher query and export a csv in Excel, example:
MATCH (n)-[REL]->(x) RETURN id(n)AS IDA, n.level as LevelLeft, id(REL) AS IDREL, TYPE(REL) AS RELATION , id(x) AS IDB, x.level as LevelRight

In Excel you can use a pivot table with level properties in column.

ciao

Alessio

[r_chevalier335]

Thanks asperlinga.

I wish to display the results on a web page LIKE a spreadsheet. I do have a level property but didn't show it here for clarity.

[r_chevalier335]

I'be been struggling with this for about 6 weeks now. Clearly l will not be able to solve this on my own.

There have been no responses and I'm not sure what to try next. Is my question not clear perhaps, or maybe this cannot be done with Neo4j?

I think I should be able to reshape the data using java script with a method in my vue form. I expect it would be ugly and break a bunch of rules, but I really don't know where to go next.

I asked a freelance developer to have a look at it and he declined stating he did not know how to solve it.

ANY comments or suggestions would be appreciated at this point.

Thanks.

[Cobra]

Hello @r.chevalier335

I don't know if it's what you want but it will return the tree and a list of instances for each row, if you want one instance per row, you can remove the collect()

MATCH tree = (:Node {name: "P 1"})<-[:CHILD_OF_NODE*]-(c:Node)
WITH tree, c
MATCH (c)<-[:INSTANCE_OF_NODE]-(i:Instance)
RETURN tree, collect(i) AS instances

Regards,
Cobra

[r_chevalier335]

Thank you VERY much for the response Cobra.

It is not quite what I am looking for however. The attached image is almost what I want, exept I want to break up the instance column into a column for each instance.

MATCH tree = (:Node{ name: "P 1" })<-[:CHILD_OF_NODE *0..]-(c:Node) WITH tree, (c)<-[:INSTANCE_OF_NODE]-(:Instance) as instance return tree, instance

In the image there are two instances in one column. In this case I can cheat and return the proper results with:

return tree, instance[0], instance[1]

The number of instances is variable so this won't quite work. I suppose if I knew the number of instances I could do something like:

return tree, (foreach i in length(instance) | instance[i])

This is just the wildest of guesses as I'm not at my development machine right now.

Maybe something like this?

[Cobra]

I'm maybe wrong but you cannot do it

The best way to do it would be in the language you are using to call this function, it will be faster and cleaner

[r_chevalier335]

Not sure what you mean when you say "in the language you are using to call this function". I am only using cypher.

[Cobra]

Neo4j is the database so you must have an app no?

[r_chevalier335]

Oh, ok. ApolloGraphql (Grand Stack) and Vue.js.

[Cobra]

So you should try to call the query I gave you and after to transform the second column how you want in JS

[r_chevalier335]

Ok, I will try. Thanks.

[r_chevalier335]

I was able to get something that looks like what I want. However I had to manually repeat the cypher for each column. Here is the query:

MATCH tree = (:Node{ name: "Assembly" })<-[b:CHILD_OF_NODE *0..]-(c:Node)
WITH b,c, c.ID as ID, c.name as name,
apoc.text.join('001' + [rel in b | apoc.text.lpad(rel.index,3,'0')], '.') as path
with c,name,path, size(split(path,'.'))-1 as indent
WITH c, path, indent, c.name as name, (c)<-[:INSTANCE_OF_NODE]-(:Instance) as instance
order by path
return c{path, indent, name}as Tree,
last(nodes(instance[0])).value as I1,
last(nodes(instance[1])).value as I2,
last(nodes(instance[2])).value as I3,
last(nodes(instance[3])).value as I4,
last(nodes(instance[4])).value as I5,
last(nodes(instance[5])).value as I6

╒══════════════════════════════════════════════╤═════╤═════╤═════╤═════╤═════╤═════╕
│"Tree"                                        │"I1" │"I2" │"I3" │"I4" │"I5" │"I6" │
╞══════════════════════════════════════════════╪═════╪═════╪═════╪═════╪═════╪═════╡
│{"name":"Assembly","path":"001","indent":0}   │null │null │null │null │null │null │
├──────────────────────────────────────────────┼─────┼─────┼─────┼─────┼─────┼─────┤
│{"name":"Part #1","path":"001.001","indent":1}│"P1A"│"P1F"│"P1E"│"P1D"│"P1C"│"P1B"│
├──────────────────────────────────────────────┼─────┼─────┼─────┼─────┼─────┼─────┤
│{"name":"Part #2","path":"001.002","indent":1}│"P2A"│"P2F"│"P2E"│"P2D"│"P2C"│"P2B"│
└──────────────────────────────────────────────┴─────┴─────┴─────┴─────┴─────┴─────┘

Does anyone have any ideas how I can return the I (Instance) columns with some kind of variable length loop?

[Cobra]

Hello, nice to see you found a way to do it manually but as I said, you won't be able to do it dynamically

UNWIND result as table fields Cypher

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 l…

Regards,
Cobra