UNWIND multiple columns from different OPTIONAL MATCHes

Neo4j Graph Platform Cypher
1

I am trying to visualise family relationships with different optional matches for spouses, children, etc.
The (simplified) query looks as follows (in NeoDash):

MATCH (n)<-[r:HAS_NAME_VARIANT]-(p:Producer)
WHERE n.NameID = $neodash_name_nameid
OPTIONAL MATCH (n2:Name)<-[r2:HAS_NAME_VARIANT {qualification: "preferred"}]-(p2)<--(f:Family {Type:"spouse"})<--(p)
WITH CASE WHEN NOT(f.StartDate) IS NULL THEN n2.Name + " -- WHEN: " + f.StartDate ELSE n2.Name END as res1, p
OPTIONAL MATCH (n3:Name)<-[r3:HAS_NAME_VARIANT {qualification: "preferred"}]-(p3)-[IS_RELATED_TO]->(f2:Family {Type:"child"})-[IS_RELATED_TO_2]->(p)
WITH CASE WHEN NOT(f2.BornDate) IS NULL THEN n3.Name + " -- WHEN: " + f2.BornDate ELSE n3.Name END as res2, res1
WITH { `1. Married to:`: res1, `2. Parent of:`: res2} as map
UNWIND keys(map) as key
RETURN key AS `Type`, map[key] AS `Person / Date`
ORDER BY key asc

It works fine and looks like this:
image

However, I would like just the Person (n2.name, n3.Name) to be in the 2nd column and the Date (f.StartDate, f2.Borndate) in another column.
The subquery, UNWIND and CALL examples I found in the documentation are either related to 1 single (OPTIONAL) MATCH while I need different ones for every type of relationship, or produce only 2 colums.
Any suggestions more than welcome!

2

Does this work? I returned an empty string when the date is null, so there would be a value in the map. You could change it to 'null' or 'n/a', etc.

MATCH (n)<-[r:HAS_NAME_VARIANT]-(p:Producer)
WHERE n.NameID = $neodash_name_nameid
OPTIONAL MATCH (n2:Name)<-[r2:HAS_NAME_VARIANT {qualification: "preferred"}]-(p2)<--(f:Family {Type:"spouse"})<--(p)
WITH CASE WHEN f.StartDate IS NOT NULL THEN f.StartDate ELSE "" END as date1, n2.Name as res1, p
OPTIONAL MATCH (n3:Name)<-[r3:HAS_NAME_VARIANT {qualification: "preferred"}]-(p3)-[IS_RELATED_TO]->(f2:Family {Type:"child"})-[IS_RELATED_TO_2]->(p)
WITH CASE WHEN f2.BornDate IS NOT NULL THEN f2.BornDate ELSE "" END as date2, n3.Name as res2, res1, date1
WITH { `1. Married to:`: res1, `When`: date1, `2. Parent of:`: res2, `When`: date2} as map
UNWIND keys(map) as key
RETURN key AS `Type`, map[key] AS `Person / Date`
ORDER BY key asc
3

Hi Gary,
thanks for replying.
Unfortunately, it doesn't: the date information now appears as an extra line, not in an extra column:

image
image750×264 3.64 KB

Just to avoid misunderstandings, this is what I want it to look like:

image
image797×190 3.85 KB

Thanks.

4

My bad, try this:

MATCH (n)<-[r:HAS_NAME_VARIANT]-(p:Producer)
WHERE n.NameID = $neodash_name_nameid
OPTIONAL MATCH (n2:Name)<-[r2:HAS_NAME_VARIANT {qualification: "preferred"}]-(p2)<--(f:Family {Type:"spouse"})<--(p)
WITH CASE WHEN f.StartDate IS NOT NULL THEN f.StartDate ELSE "" END as date1, n2.Name as res1, p
OPTIONAL MATCH (n3:Name)<-[r3:HAS_NAME_VARIANT {qualification: "preferred"}]-(p3)-[IS_RELATED_TO]->(f2:Family {Type:"child"})-[IS_RELATED_TO_2]->(p)
WITH CASE WHEN f2.BornDate IS NOT NULL THEN f2.BornDate ELSE "" END as date2, n3.Name as res2, res1, date1
WITH[{relation: '1. Married to:', person: res1, date: date1},{relation: '2. Parent of:', person: res2, date: date2}] as data
UNWIND data as row
RETURN row.relation AS Type, row.person AS Person, row.date as Date
ORDER BY Type asc
1 Like
5

There was 1 typo on line 7: {type: has to be {relation.
Works perfectly now!
Thanks, as always :slight_smile:

1 Like
6

I fixed the typo, so there is no confusion if others refer to it.

1 Like
7

For genealogy, you might take a look at Graphs for Genealogists. It has a Neo4j PlugIn with ~200 functions for loading and analyzing GEDCOM and DNA data. In several days there will be an upgrade for Neo4j 5.6. Right now is 4.x.