WITH nested collect query

[cesarclaudepro]

Hello everyone,

I am trying to collect() a custom object, so I can return a nested object.

I'm using the following query :

    MATCH (node)--(g:Group)
    WHERE g.type = '{group}' AND id(node) = {id}
    OPTIONAL MATCH (node)-[:HAS_PROPERTY]->(prop)
    OPTIONAL MATCH (node)-[:CONNECTED_TO]-(rel)
    WITH { id: id(rel), node: rel } AS rel_custom
    RETURN id(node) as id, node, collect(prop) as props, collect(rel_custom) as rels

The rels part is what doesn't work ; I'd like rels to return as a list of the custom object { id: id(rel), node: rel }. So, if I match with two rels, I'd like rels to be like

[
  {
    id: 34,
    node: {
      attribute1: 1,
      attribute2: 2
    }
  },
  {
    id: 45,
    node: {
      attribute1: 1,
      attribute2: 2
    }
  }
]

I understand I am doing something wrong, as the query doesn't work. Does anyone know what it is ?

Feel free to comment if you see any other issue, I am a beginner and I welcome the feedback.

[glilienfield]

I am surprised executing the query doesn’t give you an error, as ‘node’ and ‘prop’ should be out of scope in your return clause.

Anyways, try replacing your WITH and RETURN lines with the following single RETURN statement:

RETURN id(node) as id, node, collect(prop) as props, collect({ id: id(rel), node: properties(rel) }) as rels

[cesarclaudepro]

Wow, that works a lot better, thank you !

I still have an issue though, possibly because of the scope issue you're talking about. I now use this :

    MATCH (node)--(g:Group),
        (node)-[:HAS_PROPERTY]->(prop),
        (node)-[:CONNECTED_TO]-(rel)
    WHERE g.type = '{group}' AND id(node) = {id}
    RETURN id(node) as id, node, collect(prop) as props, collect({{ id: id(rel), node: properties(rel) }}) as rels

It does return the format I want, however it returns TWO of the same rel. This doesn't happen if I remove the prop parts, using for example :

    MATCH (node)--(g:Group),
        (node)-[:CONNECTED_TO]-(rel)
    WHERE g.type = '{group}' AND id(node) = {id}
    RETURN id(node) as id, node, collect({{ id: id(rel), node: properties(rel) }}) as rels

Do you know why that is ?

[glilienfield · Accepted solution]

Try this:

MATCH (node)--(g:Group),
(node)-[:HAS_PROPERTY]->(prop),
(node)-[:CONNECTED_TO]-(rel)
WHERE g.type = '{group}' AND id(node) = {id}
RETURN id(node) as id, node, collect(distinct prop) as props, collect(distinct { id: id(rel), node: properties(rel) }) as rels

[glilienfield]

Excellent. The following is the cause if you are interested in understanding for the next time. Basically, cypher returns rows of results. In your query, for the single node, the result will consist of rows of all the combinations of the prop and rel entities. As an example, the node below has two relationships to one entity and three relationships to another entity.

if you query with the following query, you get the results below, which demonstrate the repetition that cause the duplication when collecting the columns.

match(n:Test{id: 0})
match (n)-[:REL]->(n1:Node1)
match (n)-[:REL]->(n2:Node2)
return id(n), id(n1), id(n2)