Neo4j - Cypher Manual Study Notes 8

COLLECT subqueries

Introduced in 5.6

A COLLECT subquery expression can be used to create a list with the rows returned by a given subquery.

COLLECT subqueries differ from COUNT and EXISTS subqueries in that the final RETURN clause is mandatory. The RETURN clause must return exactly one column.

Simple COLLECT subquery

Variables introduced by the outside scope can be used in the COLLECT subquery without importing them. In this regard, COLLECT subqueries are different from CALL subqueries, which do require importing. The following query exemplifies this and outputs the owners of the dog named Ozzy:

MATCH (person:Person)
WHERE 'Ozzy' IN COLLECT { MATCH (person)-[:HAS_DOG]->(dog:Dog) RETURN dog.name }
RETURN person.name AS name

COLLECT subquery with WHERE clause

A WHERE clause can be used inside the COLLECT subquery. Variables introduced by the MATCH clause and the outside scope can be used in the inner scope.

MATCH (person:Person)
RETURN person.name as name, COLLECT {
  MATCH (person)-[r:HAS_DOG]->(dog:Dog)
  WHERE r.since > 2017
  RETURN dog.name
} as youngDogs

COLLECT subquery with a UNION

COLLECT can be used with a UNION clause. The below example shows the collection of pet names each person has by using a UNION clause:

MATCH (person:Person)
RETURN
    person.name AS name,
    COLLECT {
        MATCH (person)-[:HAS_DOG]->(dog:Dog)
        RETURN dog.name AS petName
        UNION
        MATCH (person)-[:HAS_CAT]->(cat:Cat)
        RETURN cat.name AS petName
    } AS petNames

COLLECT subquery with WITH

Variables from the outside scope are visible for the entire subquery, even when using a WITH clause. To avoid confusion, shadowing of these variables is not allowed. An outside scope variable is shadowed when a newly introduced variable within the inner scope is defined with the same variable. In the example below, the outer variable name is shadowed and will therefore throw an error.

WITH 'Peter' as name
MATCH (person:Person {name: name})
RETURN COLLECT {
    WITH 'Ozzy' AS name
    MATCH (person)-[r:HAS_DOG]->(d:Dog {name: name})
    RETURN d.name
} as dogsOfTheYear

Error message 

The variable `name` is shadowing a variable with the same name from the outer scope and needs to be renamed (line 4, column 20 (offset: 92))

New variables can be introduced into the subquery, as long as they use a different identifier. 

Using COLLECT subqueries inside other clauses

COLLECT can be used in any position in a query, with the exception of administration commands, where the COLLECT expression is restricted.

Using COLLECT in RETURN

Using COLLECT in SET

Using COLLECT in CASE

Using COLLECT as a grouping key

Using COLLECT vs collect()

COLLECT does not handle null values in the same way that the aggregating function collect() does. The collect() function automatically removes null values. COLLECT will not remove null values automatically. However, they can be removed by adding a filtering step in the subquery.

The following queries illustrate these differences:

MATCH (p:Person)
RETURN collect(p.nickname) AS names

 

╒═══════════════╕
│names          │
╞═══════════════╡
│["Tim", "Pete"]│
└───────────────┘

 

RETURN COLLECT {
        MATCH (p:Person)
        RETURN p.nickname ORDER BY p.nickname
      } AS names

 

╒═════════════════════╕
│names                │
╞═════════════════════╡
│["Pete", "Tim", null]│
└─────────────────────┘

 

RETURN COLLECT {
        MATCH (p:Person)
        WHERE p.nickname IS NOT NULL
        RETURN p.nickname ORDER BY p.nickname
      } AS names

Rules

The following is true for COLLECT subqueries:

• Any non-writing query is allowed.

• The final RETURN clause is mandatory when using a COLLECT subquery. The RETURN clause must return exactly one column.

• A COLLECT subquery can appear anywhere in a query that an expression is valid.

• Any variable that is defined in the outside scope can be referenced inside the COLLECT subquery’s own scope.

• Variables introduced inside the COLLECT subquery are not part of the outside scope and therefore cannot be accessed on the outside.

 

COUNT subqueries

A COUNT subquery can be used to count the number of rows returned by the subquery.

Simple COUNT subquery

Variables introduced by the outside scope can be used in the COUNT subquery without importing them. In this regard, COUNT subqueries are different from CALL subqueries, which do require importing.

MATCH (person:Person)
WHERE COUNT { (person)-[:HAS_DOG]->(:Dog) } > 1
RETURN person.name AS name

COUNT subquery with WHERE clause

COUNTsubquery with a UNION

COUNTsubquery with WITH

Using COUNT subqueries inside other clauses

Using COUNT in RETURN

Using COUNT in SET

Using COUNT in CASE

Using COUNT as a grouping key

COUNTsubquery withRETURN

Introduced in 5.3

COUNT subqueries do not require a RETURN clause at the end of the subquery. If one is present, it does not need to be aliased. This is a difference compared to CALL subqueries. Any variables returned in a COUNT subquery will not be available after the subquery.

Rules

The following is true for COUNT subqueries:

• Any non-writing query is allowed.

• The final RETURN clause may be omitted, as any variable defined within the subquery will not be available outside of the expression, even if a final RETURN clause is used. One exception to this is that for a DISTINCT UNION clause, the RETURN clause is still mandatory.

• The MATCH keyword can be omitted in subqueries in cases where the COUNT consists of only a pattern and an optional WHERE clause.

• A COUNT subquery can appear anywhere in a query that an expression is valid.

• Any variable that is defined in the outside scope can be referenced inside the COUNT subquery’s own scope.

• Variables introduced inside the COUNT subquery are not part of the outside scope and therefore cannot be accessed on the outside.

 

EXISTS subqueries

An EXISTS subquery can be used to find out if a specified pattern exists at least once in the graph. It serves the same purpose as a path pattern but it is more powerful because it allows you to use MATCH and WHERE clauses internally.

Simple EXISTS subquery

MATCH (person:Person)
WHERE EXISTS {
    (person)-[:HAS_DOG]->(:Dog)
}
RETURN person.name AS name

EXISTS subquery with WHERE clause

MATCH (person:Person)
WHERE EXISTS {
  MATCH (person)-[:HAS_DOG]->(dog:Dog)
  WHERE person.name = dog.name
}
RETURN person.name AS name

Nesting EXISTS subqueries

MATCH (person:Person)
WHERE EXISTS {
  MATCH (person)-[:HAS_DOG]->(dog:Dog)
  WHERE EXISTS {
    MATCH (dog)-[:HAS_TOY]->(toy:Toy)
    WHERE toy.name = 'Banana'
  }
}
RETURN person.name AS name

EXISTS subquery outside of a WHERE clause

EXISTS subquery expressions can appear anywhere that an expression is valid. Here the result is a boolean that shows whether the subquery can find the given pattern.

MATCH (person:Person)
RETURN person.name AS name, EXISTS {
  MATCH (person)-[:HAS_DOG]->(:Dog)
} AS hasDog

EXISTSsubquery with a UNION

MATCH (person:Person)
RETURN
    person.name AS name,
    EXISTS {
        MATCH (person)-[:HAS_DOG]->(:Dog)
        UNION
        MATCH (person)-[:HAS_CAT]->(:Cat)
    } AS hasPet

EXISTSsubquery with WITH

EXISTSsubquery withRETURN

Introduced in 5.3

EXISTS subqueries do not require a RETURN clause at the end of the subquery. If one is present, it does not need to be aliased, which is different compared to CALL subqueries. Any variables returned in an EXISTS subquery will not be available after the subquery.

Rules

The following is true for EXISTS subqueries:

• Any non-writing query is allowed.

• If the EXISTS subquery evaluates to at least one row, the whole expression will become true. This also means that the system only needs to evaluate if there is at least one row and can skip the rest of the work.

• EXISTS subqueries differ from regular queries in that the final RETURN clause may be omitted, as any variable defined within the subquery will not be available outside of the expression, even if a final RETURN clause is used.

• The MATCH keyword can be omitted in subqueries in cases where the EXISTS consists of only a pattern and an optional WHERE clause.

• An EXISTS subquery can appear anywhere in a query that an expression is valid.

• Any variable that is defined in the outside scope can be referenced inside the subquery’s own scope.

• Variables introduced inside the subquery are not part of the outside scope and therefore cannot be accessed on the outside.