Cypher Query with multiple MATCH statements and returns a COLLECT array

Question

I am having trouble with a particular cypher query and I am hoping someone can help. I am trying to query different relationship types of a parent so that I can return a response that includes some key/value pairs for id, name, and node label of the parent while also returning an array of children as another key/value pair in the same object returned. The array of children come from the second relationship MATCH set. When I run this, I am getting an amount of elements in that array that is much larger that the relationship with the parent supports (about 100 elements instead of the intended three).

MATCH (n)-[relone:RELATIONSHIP_ONE]->(children_one),
      (n)-[reltwo:RELATIONSHIP_TWO]->(children_two)
WHERE n.id='839930493049039430'
RETURN n.id          AS id,
       n.name        AS name,
       labels(n)[0]  AS type,
       {
         keystring: {
           id: (n.id + '_all'),
           count: count(relone)
         }
       } AS rel_one_representation,
       COLLECT({ 
          name        : children_two.name
       }) AS rel_two_representation

Here is the json output I hope to eventually produce:

{
    "id"                        : "839930493049039430",
    "name"                      : "lorem",
    "type"                      : "epsim",
    "rel_one_representation"    : {
        "keystring"             : { 
            "id"                : "839930493049039430_all",
            "count"             : 7
        }
    },
    "rel_two_representation"    : [
        {
            "name"              : "barker"
        },
        {
            "name"              : "bird"
        },
        {
            "name"              : "tiki"
        }
    ]
}

Thank you in advance for any guidance you can suggest.

cybersam cybersam · Accepted Answer · 2015-09-16T20:48:03

The issue with your query was it was returning results for all possible pairs of children_one and children_two nodes. That caused each children_two node to be repeated N1 times, where N1 is the number of children_one nodes. It also caused each children_one node to be repeated N2 times, where N2 is the number of children_two nodes.

Solution 1:

This approach uses WITH to separate the two MATCH clauses to avoid the "pairing effect".

MATCH (n)-[relone:RELATIONSHIP_ONE]->(children_one)
WHERE n.id='839930493049039430'
WITH n, COUNT(relone) AS cnt
MATCH (n)-[reltwo:RELATIONSHIP_TWO]->(children_two)
RETURN n.id AS id, n.name AS name, LABELS(n)[0] AS type, { keystring: { id: (n.id + '_all'), count: cnt }} AS rel_one_representation, COLLECT({ name : children_two.name }) AS rel_two_representation

Solution 2:

This approach uses the DISTINCT keyword to filter out duplicates.

MATCH (n)-[relone:RELATIONSHIP_ONE]->(children_one),(n)-[reltwo:RELATIONSHIP_TWO]->(children_two)
WHERE n.id='839930493049039430'
RETURN n.id AS id, n.name AS name, LABELS(n)[0] AS type, { keystring: { id: (n.id + '_all'), count: COUNT(DISTINCT relone)}} AS rel_one_representation, COLLECT(DISTINCT { name: children_two.name }) AS rel_two_representation

Cypher Query with multiple MATCH statements and returns a COLLECT array

1 Answers

Solution 1:

Solution 2: