cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.

Handling null in cypher query

I was trying to create user structures in Graph DB.
I have user entity with relationship to manager which is also user object. Here i want to update manager relationships.

There are three possibilities:
Manager with new relationship can be added
Existing relationship can point to new manager object
Current relationship can me removed

            merge (u:User {email_id: $emailId}) 
            on create set 
                u.user_id = $userId,
                u.name = $name,
                u.profile = $profile,
                u.business_phones = $businessPhones,
                u.given_name = $givenName,
                u.job_title = $jobTitle,
                u.mobile_phone = $mobilePhone,
                u.preferred_language = $preferredLanguage,
                u.surname = $surname,
                u.user_principal_name = $userPrincipalName,
                u.office_location = $officeLocation,
                u.skills = $skills,
                u.domain = $domain,
                u.tenant_id = $tenantId
            on match set
                u.user_id = $userId,
                u.name = $name,
                u.profile = $profile,
                u.business_phones = $businessPhones,
                u.given_name = $givenName,
                u.job_title = $jobTitle,
                u.mobile_phone = $mobilePhone,
                u.preferred_language = $preferredLanguage,
                u.surname = $surname,
                u.user_principal_name = $userPrincipalName,
                u.office_location = $officeLocation,
                u.skills = $skills,
                u.domain = $domain,
                u.tenant_id = $tenantId
            with u
            optional match (u)<-[oldr:IS_MANAGER_OF]-(:User)
                detach delete oldr 
            with u
            merge (mgr:User {email_id : $managerEmailId, uid: 3})    
            merge (u)<-[rz:IS_MANAGER_OF]-(mgr)

This code is breaking when managerEmailId is null. how can i handle this?

7 REPLIES 7

ameyasoft
Graph Maven
Use COALESCE($managerEmailId, 'NA')

are you suggesting create node with 'NA'?
can you please explain with an example.

'NA' is an example of at default value/catch-all you'd like to use if that field is null. Here's another example, based on your code snippet:

u.business_phones = COALESCE($businessPhones,'No Business Phones')

i want to achieve something like this

with u
            optional match (u)<-[oldr:IS_MANAGER_OF]-(:User)
                detach delete oldr 
            with u
            match (mgr:User {email_id : $managerEmailId})  
                merge (u)<-[rz:IS_MANAGER_OF]-(mgr)       
            with u, mgr,
               case 
                 when mgr is null 
                 	then  [merge (u)<-[rz:IS_MANAGER_OF]-(:User {email_id : $managerEmailId})] 
                    else []
                    end as test

This is not working

I want to establish relationship not setting property value

This is not working ???? ???

you get an error?
the query runs but gives you unexpected results?
???

When you want delete a relationship, the correct syntax is delete oldr. Try with this correction.

Finally i solved in this way

merge (u:User {email_id: ${'$'}emailId, tenant_id: ${'$'}tenantId})
            on create set 
                u.user_id = ${'$'}userId,
                u.name = ${'$'}name,
                u.profile = ${'$'}profile,
                u.business_phones = ${'$'}businessPhones,
                u.given_name = ${'$'}givenName,
                u.job_title = ${'$'}jobTitle,
                u.mobile_phone = ${'$'}mobilePhone,
                u.preferred_language = ${'$'}preferredLanguage,
                u.surname = ${'$'}surname,
                u.user_principal_name = ${'$'}userPrincipalName,
                u.office_location = ${'$'}officeLocation,
                u.skills = ${'$'}skills,
                u.domain = ${'$'}domain,
                u.self_domain = ${'$'}selfDomain,
                u.working_days = ${'$'}workingDays,
                u.working_hour_start_time_ms = ${'$'}workingHourStartTimeMillis,
                u.working_hour_end_time_ms = ${'$'}workingHourEndTimeMillis,
                u.time_zone = ${'$'}timeZone,
                u.home_address_line1 = ${'$'}homeAddressLine1,
                u.home_address_line2 = ${'$'}homeAddressLine2,
                u.home_address_postal_code = ${'$'}homeAddressPostalCode,
                u.risk_score = ${'$'}riskScore,
                u.is_employee = ${'$'}isEmployee,
                u.is_contractor = ${'$'}isContractor 
            on match set
                u.user_id = ${'$'}userId,
                u.name = ${'$'}name,
                u.profile = ${'$'}profile,
                u.business_phones = ${'$'}businessPhones,
                u.given_name = ${'$'}givenName,
                u.job_title = ${'$'}jobTitle,
                u.mobile_phone = ${'$'}mobilePhone,
                u.preferred_language = ${'$'}preferredLanguage,
                u.surname = ${'$'}surname,
                u.user_principal_name = ${'$'}userPrincipalName,
                u.office_location = ${'$'}officeLocation,
                u.skills = ${'$'}skills,
                u.domain = ${'$'}domain,
                u.self_domain = ${'$'}selfDomain,
                u.working_days = ${'$'}workingDays,
                u.working_hour_start_time_ms = ${'$'}workingHourStartTimeMillis,
                u.working_hour_end_time_ms = ${'$'}workingHourEndTimeMillis,
                u.time_zone = ${'$'}timeZone,
                u.home_address_line1 = ${'$'}homeAddressLine1,
                u.home_address_line2 = ${'$'}homeAddressLine2,
                u.home_address_postal_code = ${'$'}homeAddressPostalCode,
                u.risk_score = ${'$'}riskScore,
                u.is_employee = ${'$'}isEmployee,
                u.is_contractor = ${'$'}isContractor
            with u
            
            optional match (u)<-[r:IS_MANAGER_OF]-(:User)
                detach delete r 
            with u
            
            optional match (u)-[r:IS_MANAGER_OF]->(:User)
                detach delete r 
            with u
            
            foreach(
                managerEmailId in CASE when ${'$'}managerEmailId IS NOT NULL THEN[${'$'}managerEmailId] ELSE [] END | 
                merge (u)<-[:IS_MANAGER_OF]-(:User {email_id : managerEmailId, tenant_id: ${'$'}tenantId})
            )
            with u
            
            unwind ${'$'}directReports as reporterEmailId 
                optional match (:User {email_id: reporterEmailId, tenant_id: ${'$'}tenantId})<-[rm:IS_MANAGER_OF]-(:User)
                    detach delete rm
                with u
                
            unwind ${'$'}directReports as reporterEmailId    
                merge (reportee:User {email_id : reporterEmailId, tenant_id: ${'$'}tenantId})
                merge (u)-[:IS_MANAGER_OF]->(reportee)