Neo4j returns null when only last from optional matches exists

Question

I'm frontend developer and this is my first project where I'm using NextJS with Neo4j by sending Cypher scripts.

My Cypher code is:

OPTIONAL MATCH (o:Organization {name:"Demo AB"})-[]-(source0:System {applikation: "a1"})-[ARCHITECTED_BY:ARCHITECTED_BY]->(target0)
        
OPTIONAL MATCH (o:Organization {name:"Demo AB"})-[]-(source1:System {applikation: "a1"})-[MAINTAINED_BY:MAINTAINED_BY]->(target1)
        
OPTIONAL MATCH (o:Organization {name:"Demo AB"})-[]-(source2:System {applikation: "a1"})-[OWNED_BY:OWNED_BY]->(target2)
        
OPTIONAL MATCH (o:Organization {name:"Demo AB"})-[]-(source3:System {applikation: "a1"})-[SUPPORTED_BY:SUPPORTED_BY]->(target3)

WITH 
CASE WHEN target0 IS NOT NULL THEN target0 END AS ARCHITECTED_BY, 
CASE WHEN target1 IS NOT NULL THEN target1 END AS MAINTAINED_BY, 
CASE WHEN target2 IS NOT NULL THEN target2 END AS OWNED_BY, 
CASE WHEN target3 IS NOT NULL THEN target3 END AS SUPPORTED_BY 

RETURN ARCHITECTED_BY, MAINTAINED_BY, SUPPORTED_BY, OWNED_BY 

When System has only last connection (SUPPORTED_BY) it returns 4 null

╒══════════════╤═════════════╤════════════╤════════╕
│ARCHITECTED_BY│MAINTAINED_BY│SUPPORTED_BY│OWNED_BY│
╞══════════════╪═════════════╪════════════╪════════╡
│null          │null         │null        │null    │
└──────────────┴─────────────┴────────────┴────────┘

if system has at least 1 other relation it returns good value:

╒══════════════════════════════════════════════════════════════════════╤═════════════╤══════════════════════════════════════════════════════════════════════╤════════╕
│ARCHITECTED_BY                                                        │MAINTAINED_BY│SUPPORTED_BY                                                          │OWNED_BY│
╞══════════════════════════════════════════════════════════════════════╪═════════════╪══════════════════════════════════════════════════════════════════════╪════════╡
│(:User {lastName: "Lovelace",firstName: "Ada",phone: "0732323232",emai│null         │(:User {firstName: "Edyta",lastName: "Redactello",email: "editor@test.│null    │
│l: "ada.lovelace@test.se"})                                           │             │se"})                                                                 │        │
└──────────────────────────────────────────────────────────────────────┴─────────────┴──────────────────────────────────────────────────────────────────────┴────────┘

I can fill this is some small thing but I can't spot it...

I tried to swap the order, and problem is always with the last relation

Answer 1

When you start your query with an OPTIONAL MATCH that fails to find any matches, you get the odd situation of a single record where all the variables you declared (such as o) have type NULL and value null. The subsequent os are references to that first declaration, so o remains null.

So don't start with OPTIONAL MATCH, but instead MATCH on your anchor pattern, and then optionally match on the patterns that may or may not have a match:

MATCH (:Organization {name:"Demo AB"})-[]-(system:System {applikation: "a1"})
OPTIONAL MATCH (system)-[:MAINTAINED_BY]->(MAINTAINED_BY)
OPTIONAL MATCH (system)-[:ARCHITECTED_BY]->(ARCHITECTED_BY)
OPTIONAL MATCH (system)-[:OWNED_BY]->(OWNED_BY)
OPTIONAL MATCH (system)-[:SUPPORTED_BY]->(SUPPORTED_BY)
RETURN ARCHITECTED_BY, MAINTAINED_BY, SUPPORTED_BY, OWNED_BY