OPTIONAL MATCH causing unexpected behavior

[renatospaka]

Hello

I have the following situation: a Person is a member of a Family, and she/he can be related to another Person in that Family (in this scenario, one is the Father of the other). The Family has a top cat (named Paterfamilias) who manages the information of that Family.

When a Family is created, there is only the Paterfamilias linked to it, no other family member - one.uuid (Person).

This is the cypher I use to retrieve data from the Family node:

MATCH (pater:Person)-[:IS_PATERFAMILIAS]->(family:Family)
OPTIONAL MATCH (one:Person)-[:IS_MEMBER_OF]->(family)
WHERE family.uuid = $familyUuid
RETURN 
    family.uuid AS familyUuid,
    family.description AS description,
    pater.uuid AS paterfamiliaId,
    one.uuid AS oneId,
    toString(family.updatedAt) AS updatedAt,
    toString(family.createdAt) AS createdAt

It works well when the familyUuid exists. This is the result:

familyUuid    description    paterfamiliaId    oneId    updatedAt    createdAt
"aa547c6b-94ce-4489-afe9-8a1a2bd87e4f"    "Jaca"    "bb9b7b14-7a06-43b1-bc16-9c00453b8c38"    "8a9c349e-bfb4-47cd-8e11-c3e484be3e93"    "2021-08-20T12:29:33Z"    "2021-08-05T22:58:26Z"

However, if the specific familyUuid doesn't exist, this query returns any other existing family in the Graph and the problem is the OPTIONAL MATCH. But I don't know why it does that.

Is there a better way to query the data from the Family, whether or not exist other family members?

I'm using neo4j:4.3.1-enterprise on docker.

[Bennu]

Hi @renatospaka!

Can you try using the Where condition before the optional Match and then using WITH *.

The problem is that your WHERE is filtering the optional and not your first Match.

H