Handling null in cypher query

[rajeevprasanna]

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?

[ameyasoft]

Use COALESCE($managerEmailId, 'NA')

[rajeevprasanna]

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

[lju]

'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')

[rajeevprasanna]

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

[dana_canzano]

This is not working ???? ???

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

[ameyasoft]

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

[rajeevprasanna]

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)