Returning nodes that don't have a certain relationship?

Hi, I’m wondering if it’s possible to find nodes where a relationship doesn’t exist? I looked at this stackoverflow post but none of the solutions seem to work in Memgraph

Description

Steps to reproduce

  1. Create some example data
CREATE (u:User{name: 'user1'})
CREATE (:Movie{title: 'The Matrix'})<-[:created]-(u)
CREATE (:Movie{title: 'The Avengers'})
  1. Easy to find the movie with the created relationship:
MATCH (m:Movie)-[:created]-()
RETURN m.title
// returns "The Matrix"
  1. Try to find movie without the relationship:
MATCH (m:Movie)
OPTIONAL MATCH (m)-[c:created]-()
WHERE c is NULL
RETURN m.title
// returns "The Matrix" and "The Avengers"

Expected behavior

I thought the last query above would only return the node that doesn’t have the :created relationship

Actual behavior

The query returns both movies:

#	m.title	
1	The Matrix
2	The Avengers

Your environment

  • Memgraph version used
docker run --rm -p 127.0.0.1:7687:7687 memgraph/memgraph
You are running Memgraph v1.6.0-community
  • Memgraph Lab 1.3.0 on Windows 10 19043.1165
  • Operating system and version: Windows 10 19043.1165, using Docker with WSL2
1 Like

Hi @mackenzie! Thank you so much for an amazing issue report :smiley:

The optional match query doesn’t work because MATCH (m:Movie) will find all Movies + as soon as there is an edge present in the pattern -[c:created]- that will find any present edge (it’s not possible to filter non-existing edges if there is the edge pattern part present).

But, the solution here is, I think easy, it’s possible to filter nodes based on the number of edges they have, e.g.,


MATCH (m:Movie) WHERE degree(m) = 0 RETURN m;

Please let me know if that doesn’t work for some reason :smiley:

It’s also possible to do advanced filtering, e.g., by applying variable path filter lambdas like here Graph algorithms | Memgraph Docs, but that won’t work in case a node doesn’t have any relationships.

Maybe more applicable here is something like the following

MATCH path=(m:Movie)-[edge]-(o)
WHERE any(edge IN relationships(path) WHERE type(edge) = "created")
RETURN path;

The list of all supported functions is here Functions | Memgraph Docs (I see that any is missing now :smiley: → fixing here Add any to cypher-manual by gitbuda · Pull Request #179 · memgraph/docs · GitHub).

I hope some of the mentioned stuff is going to work!

Thanks buda!

My nodes have a bunch of relationships, so the degree trick won’t work in this case. I found a workaround however:

MATCH (m:Movie)
OPTIONAL MATCH (m)-[:created]-(u:User)
WITH collect({title: m.title, created_by: u.name}) as results
unwind results as x 
WITH x
WHERE x.created_by IS NULL 
return x.title

produces:

#	x.title	
1	The Avengers

It is a bit confusing on why this works, but the first try to filter by IS NULL before collecting didn’t work. The main reason I ask the question is in the case of refactoring the database and adding missing relationships, or checking for consistency in the db.

edit: this might be really inefficient though? Is it collecting every :Movie node and then doing the filtering after collecting all of them, or is it ‘streaming’ the results?

Here is another part of the docs that mentions that any() is not supported, maybe it should be updated too?

1 Like