Mapping Multiple Child Nodes to Parent

[nazevedo3]

Hello,

I'm having a hard time figuring out how to get the return structure I'm looking for. Below is a screenshot of some dummy data.

This is the current query I have that isn't giving me the expected results

Match(tree:FamilyTree)<-[:MEMBER_OF]-(gp:GrandParent)
OPTIONAL MATCH(gp)-[:GRAND_PARENT_OF]-(p:Parent)
OPTIONAL MATCH(p)-[:PARENT_OF]-(k:Kid)
WITH tree, gp, p, k, COLLECT(k) as kids
RETURN {tree: PROPERTIES(tree), grand_parent: gp, parents: {parents: PROPERTIES(p), their_kids: kids}}

What I'm trying to accomplish is to get 4 records back from the query. One for each GrandParent node. However, my query is giving me 5, and I'm certain it's because one of the Parent nodes has a relationship with two Kid nodes.

What I want is something like

[
  {tree:  tree, grand_parent: gp, parent: p, parent_kids: [kid1, kid2]}
  {tree:  tree, grand_parent: gp, parent: p, parent_kids: [kid3]}
  {tree:  tree, grand_parent: gp, parent: p, parent_kids: null}
  {tree:  tree, grand_parent: gp, parent: null, parent_kids: null}
]

Here is a EXPLAIN screenshot, which I haven't quiet figured out how to read...

How can I change my query to accomplish that?

[glilienfield (Gary Lilienfield)]

This should be the cause of five rows instead of four.

WITH tree, gp, p, k, COLLECT(k) as kids

You are grouping on ‘k’ and collecting ‘k’, as such, the collection will have one element. This is why the parent with two kids is return as one row per kid, instead of one row with a list containing both kids.

Try this:

WITH tree, gp, p, COLLECT(k) as kids

Instead of an optional match followed by a collect, I would use pattern comprehension as follows:

Match(tree:FamilyTree)<-[:MEMBER_OF]-(gp:GrandParent)
OPTIONAL MATCH(gp)-[:GRAND_PARENT_OF]-(p:Parent)
RETURN {
    tree: PROPERTIES(tree), 
    grand_parent: gp, 
    parents: {
        parents: PROPERTIES(p), 
        their_kids: [(p)-[:PARENT_OF]-(k:Kid)|k]
    }
} as result