Search code examples
neo4jnodesrelationshipcounting

Neo4j cypher queries, counting nodes and two way relationships

I’m trying to run a cypher query on nodes with two way relationships and would like to count each of those relationships as well.

Example:

Nodes: store + customer 
Relationships: sold + bought

In an ideal scenario, I’d want to see both relationships connecting the store and customer. However, there are cases when only the customer is reporting they bought from the store, and vice versa when only the store is reporting they sold to the customer, but the customer hasn’t verified that sale.

I’d like to return the following:

  • Store name
  • count of sold & bought relationships (store <—> customer)
  • count of only sold relationships (store —> customer)
  • count of only bought relationship (store <— customer)

*** Clarification:

for example, it would be 10 stores with 7-10 customers each, and yes in Neo it would be two different arcs connecting:

store-customer r/ships

the goals is to look at the different reporting practices for each store, as some stores may say they sold when they haven't, and customers may say they bought items. How often do each of those scenarios occur?

Solution

  • You can use something like

    match (n:Store)<-[r:bought]-(n1:Customer)<-[r1:sold]-(n) with count(distinct r) as verified 
match (n:Store)<-[r:bought]-(n1:Customer) with verified, count(distinct r) - verified as boughtOnly 
match (n:Store)-[r:sold]->(n1:Customer) with verified, boughtOnly, count(distinct r) - verified as soldOnly 
return verified, soldOnly, boughtOnly

    See the dummy database here to test on

    EDIT Updated query with where clause and returning store info

    match (n:Store) where n.state = 'MA' and n.city = 'Boston' with n as Store
match (Store)<-[r:bought]-(n1:Customer)<-[r1:sold]-(n) with Store, count(distinct r) as verified 
match (Store)<-[r:bought]-(n1:Customer) with Store, verified, count(distinct r) - verified as boughtOnly 
match (Store)-[r:sold]->(n1:Customer) with Store, verified, boughtOnly, count(distinct r) - verified as soldOnly 
return verified, soldOnly, boughtOnly, Store