UNION With Differing Columns

[irishvik]

Hello, I am struggling with finding a solution to perform two separate sub-queries and combining/returning the results similar to the CALL { } here and under section 3.16.2 here, but with differing columns between the sub-queries.

I'm using Browser Neo4j 4.3 Desktop Edition.

My example query is:

CALL {
	OPTIONAL MATCH (p:Person)-[v:VISITED]->(f:Farm {farmID:"B1"}) 
    WHERE date(v.date) >= date('2020-3-27') AND date(v.date) <= date('2020-4-7')
    RETURN DISTINCT p.personID, v.date, v.reason, f.farmID 
	
    UNION 
	
    OPTIONAL MATCH (p:Person)-[j:JOB_IS]->(f:Farm {farmID:"B1"}) 
    RETURN p.personID, j.role as job_role
}    
RETURN p.personID, j.role as job_role, v.date AS visit_date, v.reason as visit_reason, f.farmID 

What I am hoping to get out of the query is something like the following, including nulls when a record isn't found:

[andrew_bowman]

For one, returned variables from a UNION must be the same, so that wouldn't work.

Also you probably wouldn't want this to work, as your return would be a cross product of visitors and employee (that is, each row would have 1 visitor and 1 employee, and then the next row would have a different combination of 1 visitor and 1 employee, such that the set of all rows encompasses all combinations of 1 visitor and 1 employee. So if you had 3 employees and 5 visitors, you would have 15 rows symbolizing all possible pairings of 1 employee and 1 visitor. Each employee would show up 5 times across all rows, and each visitor would show up 3 times across all rows.

I think what you're really after for this case is to get, for a farm, a list of employees and their role, and a separate list of visitors with their visit information, and you'd get all this on a single row. Something like this:

MATCH (f:Farm {farmID:"B1"})
WITH f, 
   [(p:Person)-[v:VISITED]->(f) WHERE date(v.date) >= date('2020-3-27') AND date(v.date) <= date('2020-4-7') | v {.date, .reason, personID:p.personID}] as visitors, 
   [(p:Person)-[j:JOB_IS]->(f) | p {.personID, job_role:j.role}] as employees
RETURN f.farmID as farmID, visitors, employees

Now, if the visitor and the employee are supposed to be the same person, and you're looking for visit information for that employee to the farm, then that's a different kind of query.

[irishvik]

That is excellent @andrew.bowman, I really appreciate it. I would assume from here I could UNWIND the array into a tabular row/column format?

[andrew_bowman · Accepted solution]

You could, but UNWINDing both will get you a cross product of the two lists, probably something you don't really want. Try it out and see!

Ah, but if you want one person per row, no matter if they were a visitor or an employee, you could do something like this:

MATCH (f:Farm {farmID:"B1"})
WITH f, 
   [(p:Person)-[v:VISITED]->(f) WHERE date(v.date) >= date('2020-3-27') AND date(v.date) <= date('2020-4-7') | v {.date, .reason, personID:p.personID}] as visitors, 
   [(p:Person)-[j:JOB_IS]->(f) | p {.personID, job_role:j.role}] as employees
UNWIND visitors + employees as person
RETURN f.farmID as farmID, person

[irishvik]

Thanks again. The demonstration is to show how graph/Neo4j can help in contact tracing for virus outbreaks at farms, so combining the visitors and employees into a single column, with columns that show other properties like date and job, is meant to pool those who may have been in contact or could spread to other farms. The UNWIND did improve it, and it helps to see the syntax to potentially UNWIND the rest.

[kiranhegde]

CALL{

MATCH(a:Product)-[:Relation]- >(c:workflow{workflow_name:"wf_oracle_extract_30_08"})< - [:Relation] -(b:storage)

RETURN a,b,c

UNION

MATCH(a:Product)-[:Relation]- >(b:storage)-[:Relation]- (c:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c

UNION

MATCH(a:rs_xref)< -[:Relation] - (b: storage)< -[:Relation] - (c:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c

UNION

MATCH(a:Product) -[:Relation] ->(b:report) -[:Relation] - >(c:rs_xref) <-[:Relation] -(d:storage) <-[:Relation] - (e:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c,d,e

}

RETURN a,b,c,d,e

how can I make it run

[andrew_bowman]

And as a follow-up to your original question, how to get the equivalent of the post-union processing via a subquery in Neo4j 3.5.x, you could do what I just did, combine lists and UNWIND into a single row, or you could leverage APOC Procedures and use apoc.cypher.run() as the subquery that performs the UNION.

It's documented in this knowledge base article:

Neo4j Graph Database Platform

Post-UNION processing - Neo4j Graph Database Platform

Cypher does not allow further processing of UNION or UNION ALL results, since RETURN is required in all queries of the union. Here are some workarounds. Post-UNION processing in Neo4j 4.0 With Neo4j 4.0, post-UNION processing is now possible via…...

[kiranhegde]

CALL{

MATCH(a:Product)-[:Relation]- >(c:workflow{workflow_name:"wf_oracle_extract_30_08"})< - [:Relation] -(b:storage)

RETURN a,b,c

UNION

MATCH(a:Product)-[:Relation]- >(b:storage)-[:Relation]- (c:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c

UNION

MATCH(a:rs_xref)< -[:Relation] - (b:storage)< -[:Relation] - (c:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c

UNION

MATCH(a:Product) -[:Relation] ->(b:report) -[:Relation] - >(c:rs_xref) <-[:Relation] -(d:storage) <-[:Relation] - (e:workflow{workflow_name:"wf_oracle_extract_30_08"})

RETURN a,b,c,d,e

}

RETURN a,b,c,d,e

how can i make it run