How to get null for labels that don't exist in Neo4J?

Question

I'm running the following query in Neo4J and I'm getting an error. In a nutshell, I'm asking the database show me all nodes with the label "user" and, if it has a relationship, the type of the relationship and the label type of the node it's connected to.

START n=node(*) MATCH (n:user)-[r?]-(m) WHERE HAS (n.name) RETURN n, labels(n), type(r), labels(m), m.name;

An error occurs when a node of type user is found that doesn't have a node related to it. It seems to be blowing up on the labels(m) part. I would expect that if the node didn't exist, I would get a NULL returned instead of an error. This is how the type() function works. If no relationship is found, type() returns NULL.

Here is what my result looks like if I omit the labels(m) part...

==>+-----------------------------------------------------------------------------------------------------------------------------------+
==> | n                                                                                    | labels(n) | type(r)      | m.name          |
==> +-----------------------------------------------------------------------------------------------------------------------------------+
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"} | ["user"]  | "hasContext" | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"} | ["user"]  | "hasContext" | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"} | ["user"]  | "hasContext" | "vinniecontext" |
==> | Node[2558]{lastName:"b",mobilePhone:"b",status:"b",email:"b",name:"b",firstName:"b"} | ["user"]  | <null>       | <null>          |
==> | Node[2559]{lastName:"c",mobilePhone:"c",status:"c",email:"c",name:"c",firstName:"c"} | ["user"]  | <null>       | <null>          |
==> | Node[2560]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"} | ["user"]  | <null>       | <null>          |
==> | Node[2561]{lastName:"b",mobilePhone:"b",status:"b",email:"b",name:"",firstName:"b"}  | ["user"]  | <null>       | <null>          |
==> +-----------------------------------------------------------------------------------------------------------------------------------+

How can I get the result that I want? I basically want a NULL value in the case where there is no connecting node, otherwise give me the label of the connecting node.

Thanks in advance!!

Vinnie Vinnie · Accepted Answer · 2013-07-24T15:04:15

OK so a coworker helped me solve this problem (after I pounded my head against my desk for a day and a half). I got the results I wanted by using a UNION ALL. I have to do two different queries; one where relationships are null and one where relationships are not null.

START n=node(*) MATCH (n:user)-[r?]-(m) WHERE r IS NOT NULL RETURN n, labels(n), type(r), labels(m) as i, m.name as a 
UNION ALL
START n=node(*) MATCH (n:user)-[r?]-(m) WHERE r IS NULL RETURN n, labels(n), type(r), null as i, null as a;

And here's the result...

==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | n                                                                                                                                               | labels(n) | type(r)      | i           | a               |
==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2557]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | "hasContext" | ["context"] | "vinniecontext" |
==> | Node[2558]{lastName:"b",mobilePhone:"b",status:"b",email:"b",name:"b",firstName:"b"}                                                            | ["user"]  | <null>       | <null>      | <null>          |
==> | Node[2559]{lastName:"c",mobilePhone:"c",status:"c",email:"c",name:"c",firstName:"c"}                                                            | ["user"]  | <null>       | <null>      | <null>          |
==> | Node[2560]{lastName:"a",mobilePhone:"a",status:"a",email:"a",name:"a",firstName:"a"}                                                            | ["user"]  | <null>       | <null>      | <null>          |
==> | Node[2561]{lastName:"b",mobilePhone:"b",status:"b",email:"b",name:"",firstName:"b"}                                                             | ["user"]  | <null>       | <null>      | <null>          |
==> | Node[7638]{lastName:"afdasdfasdf",mobilePhone:"asdfa",status:"asdfasdf",email:"asdfasdfasdf",name:"asdfasdfa",firstName:"asdfasdfas"}           | ["user"]  | <null>       | <null>      | <null>          |
==> | Node[7639]{lastName:"asddsafsda",mobilePhone:"asdfasdfdsa",status:"sadfsadfsda",email:"sadfasdfas",name:"sdafasdfsadf",firstName:"sadfsadfsad"} | ["user"]  | <null>       | <null>      | <null>          |
==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I hope this solution saves someone else some pain.

How to get null for labels that don't exist in Neo4J?

1 Answers