cancel
Showing results for 
Search instead for 
Did you mean: 

A case for NOT IN

wolf_goe
Node

Hi All,

I have a situation where I get 2 different matches and I need to get one node from one of them that does not exist in the other.

For example:

create (a:Host {name:'aA'})
create (b:Host {name:'aB'})
create (c:Host {name:'aC'})

create (a1:Host {name:'aD'})
create (b1:Host {name:'aE'})
create (c1:Host {name:'aF'})


create (d:Cluster {name:'bA'})
create (d1:Cluster {name:'bB'})

create (e:Datastore {name:'cA'})
create (e1:Datastore {name:'cB'})
create 
(d)-[:CONTAINS]->(a),
(d)-[:CONTAINS]->(b),
(d)-[:CONTAINS]->(c),
(e)-[:MAPPED_TO]->(a),
(e)-[:MAPPED_TO]->(b),
(d1)-[:CONTAINS]->(a1),
(d1)-[:CONTAINS]->(b1),
(d1)-[:CONTAINS]->(c1),
(e1)-[:MAPPED_TO]->(b1);

Now:

match (n)-[r]-() return *;

Would return:

What I need is to only return those Hosts that, although contained in a Cluster, does not have that Datastore Mapped to it. So, from the group in the left, given the Datastore named 'cA' I would need only the Host named 'aC' and from the group in the right, given the Datastore named 'cB' I would need to get Hosts named 'aF' and 'aD'.

I tried something on the lines of creating a collect from a cypher that returns all Hosts that are Contained in a Cluster and then matching the Datastore with the Hosts for the same Cluster and using NOT IN, but that always returns the same 3 hosts. I guess I'm thinking more on the equivalent on SQLServer (EXCEPT) or Oracle (MINUS), and I don't seem to understand the concept on cypher.

The goal is, without passing a Datastore or a Cluster as parameter, return all the Datastores that are mapped to some, but not all, of the hosts that are Contained in a Cluster and those Hosts without the Datastore mapping.

Anyone has a clue?

Wolf

2 REPLIES 2

koji
Ninja
Ninja

Hi @wolf_goe

What I need is to only return those Hosts that, although contained in a Cluster, does not have that Datastore Mapped to it. So, from the group in the left, given the Datastore named 'cA' I would need only the Host named 'aC' and from the group in the right, given the Datastore named 'cB' I would need to get Hosts named 'aF' and 'aD'.

How about this code.

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h:Host)<-[:CONTAINS]-(c:Cluster)
WITH collect(id(h)) AS hostids, id(c) AS clusterid
MATCH (c:Cluster)-[:CONTAINS]->(h:Host)
  WHERE id(c) = clusterid
  AND NOT id(h) IN hostids
RETURN h;

anthapu
Graph Fellow

You can use pattern exists or not exists here.

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h)<-[:CONTAINS]-(c)
WITH DISTINCT c
MATCH (c)-[:CONTAINS]->(h)
WHERE NOT EXISTS((h)-[:MAPPED_TO]->())
RETURN h

Here all the hosts which are part of cluster but not associated to datastore are returned. If you want the paths

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h)<-[:CONTAINS]-(c)
WITH DISTINCT c
MATCH path=(c)-[:CONTAINS]->(h)
WHERE NOT EXISTS((h)-[:MAPPED_TO]->())
RETURN path
Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.