cancel
Showing results for 
Search instead for 
Did you mean: 

Match statement based on unique relationship properties

phil_j_mcnamara
Node Link

Not sure if this is an unusual use case, but not able to find anything in the docs:

My graph has nodes labelled A and B, and relationships between those nodes with property C

I want to return just one A-B relationship per unique property C, for example if there are 4 relationships but only two unique values for C, the MATCH should only return two relationships

I have gotten the list of unique properties with a COLLECT DISTINCT, but I am stuck on the UNWIND.

Here is my current query:

 

MATCH ()-[x]-()
WITH COLLECT(DISTINCT x.c) AS props
UNWIND props as p
MATCH (a)-[z {c:p}]-(b) RETURN a,b,z LIMIT 1

 

The LIMIT 1 is returning only one relationship total, not one relationship per prop in p. Am I not understanding how UNWIND translates to a MATCH?

Many thanks,

Phil

1 ACCEPTED SOLUTION

glilienfield
Ninja
Ninja

I refactored your query to provide a working solution using your approach:

 

MATCH (:A)-[x]-(:B)
WITH DISTINCT x.c AS c
call {
    WITH c
    MATCH (a:A)-[r{c:c}]-(b:B) 
    RETURN a,b,r
    LIMIT 1
}
RETURN a,b,r

 

The error in your original query is the 'LIMIT 1' is applied over the entire result, regardless of the value of 'c'.  The use of a 'call' subquery, allows you to execute the query for each value of 'c' and apply the 'limit' clause agains just the results of that particular value of 'c'. 

Note, this solution is executing the 'match' clause twice, compared to my earlier query. Once to get the distinct list of 'c' values, then again to get the relationships and limit each result to one row.

View solution in original post

5 REPLIES 5

glilienfield
Ninja
Ninja

Try something like this:


@phil_j_mcnamara wrote:

Not sure if this is an unusual use case, but not able to find anything in the docs:

My graph has nodes labelled A and B, and relationships between those nodes with property C

I want to return just one A-B relationship per unique property C, for example if there are 4 relationships but only two unique values for C, the MATCH should only return two relationships

I have gotten the list of unique properties with a COLLECT DISTINCT, but I am stuck on the UNWIND.

Here is my current query:

 

MATCH ()-[x]-()
WITH COLLECT(DISTINCT x.c) AS props
UNWIND props as p
MATCH (a)-[z {c:p}]-(b) RETURN a,b,z LIMIT 1

 

The LIMIT 1 is returning only one relationship total, not one relationship per prop in p. Am I not understanding how UNWIND translates to a MATCH?

Many thanks,

Phil


match(:A)-[r]-(:B)
with r.c as c, collect(r)[0] as rel
return startNode(rel) as startNode, endNode(rel) as endNode, rel

glilienfield
Ninja
Ninja

I refactored your query to provide a working solution using your approach:

 

MATCH (:A)-[x]-(:B)
WITH DISTINCT x.c AS c
call {
    WITH c
    MATCH (a:A)-[r{c:c}]-(b:B) 
    RETURN a,b,r
    LIMIT 1
}
RETURN a,b,r

 

The error in your original query is the 'LIMIT 1' is applied over the entire result, regardless of the value of 'c'.  The use of a 'call' subquery, allows you to execute the query for each value of 'c' and apply the 'limit' clause agains just the results of that particular value of 'c'. 

Note, this solution is executing the 'match' clause twice, compared to my earlier query. Once to get the distinct list of 'c' values, then again to get the relationships and limit each result to one row.

phil_j_mcnamara
Node Link

This is working for me, thanks so much! One follow-up that I didn't realize was missing from my original question, is there a way to structure the query so I can return unique relationships based on C for each pair of matched A-B nodes? When I run the query on a group of A-B nodes it only returns relationships equal to the number of unique values in C, leaving some A and B nodes unconnected.

glilienfield
Ninja
Ninja

I think I understand. You would like to find one relationship for each unique value of 'c' between each pair of 'A' and 'B' nodes, instead of each unique value of 'c' regardless of the values of 'A' and 'B'. 

Try this:

match(a:A)-[r]-(b:B)
where id(a) < id(b)
with a, b, r.c as c, collect(r)[0] as rel
return startNode(rel) as startNode, endNode(rel) as endNode, rel

OR

MATCH (a:A)-[x]-(b:B)
WHERE id(a) < id(b)
WITH a, b, collect(DISTINCT x.c) AS cList
UNWIND cList as c
call {
    WITH a, b, c
    MATCH (a)-[r{c:c}]-(b) 
    RETURN r
    LIMIT 1
}
RETURN a,b,r

phil_j_mcnamara
Node Link

Exactly, this works perfectly thank you so much!

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.