Neo4j: search in children too

Question

I have a simple parent/child graph in neo4j. I would like search the parents (and its children) with a property value in parent node or in child node. A parent can have (or not) a list of children

I tried this query

MATCH (p:Parent) 
WHERE p.lastName =~ "(?i).*.*" 
RETURN p AS parent, [] as children 
UNION 
MATCH (p:Parent) - [:REL] -> (c:Child) 
WHERE p.lastName =~ "(?i).*1234.*" OR c.name =~ "(?i).*.*"  
RETURN p AS parent, c AS collect(children)

but it's not perfect: this query return not distinct result.

Do you know a better query to have a list of parents and its children with property value in parent node or child node.

The logic should be:

if (p.lastName == x) OR (p.children.name == x)
    return p

UPDATE

I give you more elements:

Example:

(p1:Parent { lastName: "AAA" }) no children

(p2:Parent { lastName: "BBB" }) 2 children 
    (c1:Child { name: "CCC" })
    (c2:Child { name: "DDD"})

If I execute this query

MATCH (p:Parent)
OPTIONAL MATCH (p)-[:REL]->(c:Child)
WHERE p.lastName CONTAINS 'DDD' OR c.name CONTAINS 'DDD'
RETURN p AS parent, collect(c) AS children;

the result is p1, p2 with c2. I would like only p2 with c2

cybersam cybersam · Accepted Answer · 2016-06-27T10:02:44

I assume you have typos in a couple of your regular expressions, and that they should all actually be "(?i).*1234.*".

[UPDATED TWICE]

In Cypher, the UNION clause additively combines result rows, which explains why you did not get distinct results.

This query might be closer to what you want:

MATCH (p1:Parent)
WHERE p1.lastName CONTAINS 'DDD'
OPTIONAL MATCH (p1)-[:REL]->(c1:Child)
WITH COLLECT({parent: p1, child: c1}) AS x
OPTIONAL MATCH (p2:Parent)-[:REL]->(c2:Child)
WHERE c2.name CONTAINS 'DDD' AND NOT(p2.lastName CONTAINS 'DDD')
WITH x + (CASE WHEN p2 IS NULL THEN [] ELSE COLLECT({parent: p2, child: c2}) END) AS y
UNWIND y AS z
RETURN z.parent AS parent, COLLECT(z.child) AS children;

It first collects all Parent nodes having the desired lastName, along with their Child nodes, if any. It then appends to that collection all Parent/Child nodes pairs where the parent does NOT have the appropriate lastName but the child does have the desired name. Finally, it returns each distinct Parent node along with its Child nodes.

Neo4j: search in children too

1 Answers