Sub Queries

[adam_cowley]

In the filtering on patterns section of the filtering query results guide, we learnt how to filter based on patterns. For example, we wrote the following query to find the friends of someone who works for Neo4j:

MATCH (p:Person)-[r:IS_FRIENDS_WITH]->(friend:Person)
WHERE exists((p)-[:WORKS_FOR]->(:Company {name: 'Neo4j'}))
RETURN p, r, friend

If we run this query in the Neo4j Browser, the following graph is returned:

Existential subqueries enable more powerful pattern filtering. Instead of using the exists function in our WHERE clause, we use the EXISTS {} clause. We can reproduce the previous example with the following query:

MATCH (p:Person)-[r:IS_FRIENDS_WITH]->(friend:Person)
WHERE EXISTS {
  MATCH (p)-[:WORKS_FOR]->(:Company {name: 'Neo4j'})
}
RETURN p, r, friend

We’ll get the same results, which is nice, but so far all we’ve achieved is the same thing with more code!

Let’s next write a subquery that does more powerful filtering than what we can achieve with the WHERE clause or exists function alone.

Imagine that we want to find the people who:

• work for a company whose name starts with 'Company' and

• like at least one technology that’s liked by 3 or more people

We aren’t interested in knowing what those technologies are. We might try to answer this question with the following query:

MATCH (person:Person)-[:WORKS_FOR]->(company)
WHERE company.name STARTS WITH "Company"
AND (person)-[:LIKES]->(t:Technology)
AND size((t)<-[:LIKES]-()) >= 3
RETURN person.name as person, company.name AS company;

If we run this query, we’ll see the following output:

Variable `t` not defined (line 4, column 25 (offset: 112))
"AND (person)-[:LIKES]->(t:Technology)"
                         ^

We can find people that like a technology, but we can’t check that at least 3 people like that technology as well, because the variable t isn’t in the scope of the WHERE clause. Let’s instead move the two AND statements into an EXISTS {} block, resulting in the following query:

MATCH (person:Person)-[:WORKS_FOR]->(company)
WHERE company.name STARTS WITH "Company"
AND EXISTS {
  MATCH (person)-[:LIKES]->(t:Technology)
  WHERE size((t)<-[:LIKES]-()) >= 3
}
RETURN person.name as person, company.name AS company;

Now we’re able to successfully execute the query, which returns the following results:

person company "Melissa" "CompanyA" "Diana" "CompanyX"

If we recall the graph visualisation from the start of this guide, Ryan is the only other person who works for a company whose name starts with "Company". He’s been filtered out in this query because the only Technology that he likes is Python, and there aren’t 3 people who like Python.

---

This is a companion discussion topic for the original entry at https://neo4j.com/developer/cypher/subqueries/