Cypher procedure call with optional result

TomVW 0
Viewed 43

I have a procedure that looks for the last active session for a specific user. The procedure will return a single result or nothing. When I execute the following cypher query, it only returns a result when both the user and the session are present.

MATCH (user:User) 

 CALL org.custom.last_active_session(user) YIELD session

RETURN user, session

How can I return the user node, even if the last_active_session procedure yields no result?

cypher neo4j java
3 Answers

It is tricky. You can add a null (dummy) session by using UNION then return it with user. Then remove duplicates by doing a collect. Lastly, get the session which is the only item in the list.

MATCH (user: User) 
//call your user defined function and combine (union) it with a dummy session
CALL   { WITH user
     CALL org.custom.last_active_session(user) 
        YIELD  session 
        RETURN session 
     UNION
        RETURN null as session}
//remove duplicates caused by dummy (null) session using collect
WITH user, collect(session)[0] as session
//collect returns a list and session is always a single result so it is indexed 0 
RETURN user, session

Sample output:

user   |   session |
--------------------
{id:1}    session1
{id:2}    null

IMO, you should handle it in your procedure.

jose_bacoy0

It seems that it has to do with your custom procedure that it can't return a null value? Anyhow, I would try to use subqueries:

MATCH (user:User)
CALL {WITH user
      CALL org.custom.last_active_session(user) YIELD session
      RETURN session}
RETURN user, session
Tomaž Bratanič0

There are a lot of options for customizing functionality in Neo4j. The two related to your use case are User-Defined Procedures and User-Defined Functions.

Currently, you have created a User-Defined Procedure I think, which is expected to return a Stream of results and hence we use YIELD in Cypher.

As mentioned in the question, since your procedure returns a single value or nothing, you can create a User-Defined Function, which is expected to return a single value.

Once you do that, your query becomes:

MATCH (user:User) 
RETURN user, org.custom.last_active_session(user) AS session

Read more about user-defined functions here.

Charchit Kapoor0
Related