CODEWITHSUNDEEP
neo4jcypherneo4jclient
Darshit Jain
Darshit Jain

Reputation: 27

Add another column in result set instead of UNION

I have a set of nodes labeled as Person and City. Node citiy has two properties Name and isInAustralia. A person can have relation 'hasHouse' relationship with the City node. A person can have multiple houses

I want to retrieve the data in such a way that it results in format: Name of the person || CityInAustralia ||CityOutsideAustralia

suppose a person has a house in city A,D,E in the Australia and houses in city B,C outside the Australia.

The result I am getting is :

Adam----A----B

Adam----D-----B

Adam----E----B

Adam----A-----C

Adam----D----C

Adam----E-----C

I have tried the below written query: 

Match(person:Person{Name:"Adam"})-[rel:hasHouseIn]->(city:City) 
where city.isInAustralia="true"
optional match (person:Person{Name:"Adam"})-[rel:hasHouseIn]->
(cityOutside:City) 
where city.isInAustralia="false"
return person.Name,City.Name as CityInAustralia ,cityOutside.Name as 
CityOutsideAustralia

I want results to be like:

Name ---------- CityInAustralia ---------- CityOutsideAustralia

Adam------------A-----------------------------B

Adam------------D-------------------------C

Adam------------E-------------------------Blank

I want to get a result as what we get when we apply left outer join in sql sever where Name will be the column on which we will join.

Upvotes: 0

Views: 112

Answers (1)

Charlotte Skardon
Charlotte Skardon

Reputation: 6270

I don't really know exactly what you're trying to do - the query won't work as it is as you have duplicate identifiers (rel) so you'd get nothing as a result. But also - if you have 'Adam' having 3 houses (A,D,E) in Australia, and 2 outside (B,C), you'll get:

A --> B
A --> C
D --> B
D --> C
E --> B
E --> C

Neo4j can't determine which of the cityOutside should be displayed with cityInAustralia as that would have to be a random guess, that seems like a post processing job. The query that will get you the results I've indicated above is:

MATCH (person:Person{Name:"Adam"})-[:hasHouseIn]->(city:City {IsInAustralia:true}) 
OPTIONAL MATCH (person)-[:hasHouseIn]->(cityOutside:City {IsInAustralia:false}) 
RETURN 
    person.Name, 
    city.Name AS CityInAustralia,
    cityOutside.Name AS CityOutsideAustralia

Personally, I would use Collect and get two arrays so you get:

CityInsideAustralia: [A,D,E], 
CityOutsideAustralia: [B,C]

So you only get one result per Person:

MATCH (person:Person{Name:"Adam"})-[:hasHouseIn]->(city:City {IsInAustralia:true}) 
OPTIONAL MATCH (person)-[:hasHouseIn]->(cityOutside:City {IsInAustralia:false}) 
RETURN 
    person.Name, 
    COLLECT(DISTINCT city.Name) AS CityInAustralia,
    COLLECT(DISTINCT cityOutside.Name) AS CityOutsideAustralia

NB. I'm using the below dataset:

CREATE 
    (p:Person {Name:'Adam'})-[:hasHouseIn]->(:City {IsInAustralia:true, Name:'A'}),
    (p)-[:hasHouseIn]->(:City {IsInAustralia:true, Name:'D'}),
    (p)-[:hasHouseIn]->(:City {IsInAustralia:true, Name:'E'}),
    (p)-[:hasHouseIn]->(:City {IsInAustralia:false, Name:'B'}),
    (p)-[:hasHouseIn]->(:City {IsInAustralia:false, Name:'C'})

Upvotes: 2

Related Questions