cancel
Showing results for 
Search instead for 
Did you mean: 

how to find relationships with distinct node

manjeetthakur
Node Clone

I have write this query (I'm using Movie DBMS)

 

MATCH (p) WITH labels(p) as label, count(*) as nCount with collect({label:label, count: nCount}) as nodes
call{
MATCH ()-[relationship]->()
with type(relationship) as r, COUNT(relationship) as rCount
order by rCount desc
return collect({label:r, count: rCount}) as relationships
}
return nodes, relationships

 

which return this result

 

[{
  "count": 229,
  "label": [
    "Movie"
  ]
}, 
{
  "count": 803,
  "label": [
    "Person"
  ]
}
]	
----------------------------
[{
  "count": 1036,
  "label": "ACTED_IN"
}
, 
{
  "count": 266,
  "label": "DIRECTED"
}
, 
{
  "count": 90,
  "label": "PRODUCED"
}
, 
{
  "count": 59,
  "label": "WROTE"
}
, 
{
  "count": 53,
  "label": "REVIEWED"
}
, 
{
  "count": 18,
  "label": "FOLLOWS"
}
]

 

 I want to show high level structural view  now my requirement is  to return source and target

[{
"count": 1036,
"type": "ACTED_IN",
"nodes": [{
                 "source" : "Movies",  // or Can I add id instead of name of the node like "source": "0"
                 "target" : "Person"
            }]
}]

so is there any way I can achieve in cypher query or it is best practices to do in cypher

1 ACCEPTED SOLUTION

Is this it?

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
    MATCH ()-[r]->()
    WITH type(r) as type, startNode(r) as startNode, endNode(r) as endNode
    WITH type, labels(startNode)[0] as startLabel, labels(endNode)[0] as endLabel, COUNT(*) as rCount
    ORDER BY rCount
    RETURN collect({count: rCount, type: type, node: {source: startLabel, target: endLabel}}) as relationships 
}
RETURN nodes, relationships
[
{
  "node": {
"source": "Person",
"target": "Person"
  },
  "count": 3,
  "type": "FOLLOWS"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 9,
  "type": "REVIEWED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 10,
  "type": "WROTE"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 15,
  "type": "PRODUCED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 44,
  "type": "DIRECTED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 172,
  "type": "ACTED_IN"
}
]

View solution in original post

11 REPLIES 11

Cobra
Ninja
Ninja

Hello @manjeetthakur 😊

I'm not sure but I think this can solve your issue without cypher.

Regards,

Cobra

manjeetthakur
Node Clone

@CobraThank you so much for answer

is there any other approach

glilienfield
Ninja
Ninja

Just a note on your first query.  The labels(p) function returns a list. When you 'count' its values, you are counting the instances of a list. It works in your case, since the labels are singleton lists. You will not get the results you expect if nodes have multiple labels, as you will be counting the number of instances of a specific list, including the order of the elements. You can overcome this by 'unwinding' the labels(p) list, to get a stream of the individual labels. I modified your query to demonstrate it. 

 

 

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
MATCH ()-[relationship]->()
WITH type(relationship) as r, COUNT(relationship) as rCount
order by rCount desc
return collect({label:r, count: rCount}) as relationships
}
return nodes, relationships

 

 

 

[
{
  "count": 38,
  "label": "Movie"
}
, 
{
  "count": 133,
  "label": "Person"
}
]

 

 

Try the following query to get the relationship node information. 

 

 

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
    MATCH ()-[relationship]->()
    WITH type(relationship) as r, startNode(relationship) as startNode, endNode(relationship) as endNode
    WITH r, COUNT(*) as rCount, collect({source: id(startNode), target: id(endNode)}) as nodes
    ORDER BY rCount
    RETURN collect({count: rCount, type: r, nodes: nodes}) as relationships 
}
RETURN nodes, relationships

 

 

Example of one relationship:

 

{
  "nodes": [
    {
      "source": 167,
      "target": 168
    },
    {
      "source": 168,
      "target": 169
    },
    {
      "source": 170,
      "target": 169
    }
  ],
  "count": 3,
  "type": "FOLLOWS"
}

 

You can expand the node information for each relationship too. 

manjeetthakur
Node Clone

@glilienfield  Thank you so much for answering my question

But I only want to show high level view. I only interested to show  single value

like Let suppose we have many nodes  with two unique labels

1. Person

2. Movies

Now I want to show like this Picture2.png

for all relationships ACTED_IN, DIRECTED, PRODUCED, WROTE, REVIEWED, FOLLOWS

 

One way which is I'm think is in programming language (JavaScript) I'm looping over the relationships and get like this

match x=(a)-[b:ACTED_IN]->(c)
return distinct labels(a), labels(c)

 

and then prepare new result using JavaScript. But I want to know if there is a way in cypher query to reduce burden in Nodejs side

 

Do you want the list of labels for the source and target nodes for each type of relationship? 

I want like this

[{
"count": 266,
"type": "DIRECTED"
"node": {
         "source": "Movies",
         "target" : "Person"
  }
},{
  "count": 53,
  "label": "WROTE",
  "node": {
         "source": "Person",
         "target" : "Movies"
  }
}]

 

manjeetthakur
Node Clone

@glilienfield 

 

{
  "nodes": [
    {
      "source": 165,
      "target": 166
    },
    {
      "source": 166,
      "target": 167
    },
    {
      "source": 168,
      "target": 167
    },
    {
      "source": 336,
      "target": 337
    },
    {
      "source": 337,
      "target": 338
    },
    {
      "source": 339,
      "target": 338
    },
    {
      "source": 507,
      "target": 508
    },
    {
      "source": 508,
      "target": 509
    },
    {
      "source": 510,
      "target": 509
    },
    {
      "source": 678,
      "target": 679
    },
    {
      "source": 679,
      "target": 680
    },
    {
      "source": 681,
      "target": 680
    },
    {
      "source": 849,
      "target": 850
    },
    {
      "source": 850,
      "target": 851
    },
    {
      "source": 852,
      "target": 851
    },
    {
      "source": 1020,
      "target": 1021
    },
    {
      "source": 1021,
      "target": 1022
    },
    {
      "source": 1023,
      "target": 1022
    }
  ],
  "count": 18,
  "type": "FOLLOWS"
}

 

 

Instead of this result I'm looking for like this

 

 

"count": 18,
"type": "FOLLOWS"
"node": {
         "source": "Movies",
         "target" : "Person"
  }

 

Thank you so much

 

Is this it?

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
    MATCH ()-[r]->()
    WITH type(r) as type, startNode(r) as startNode, endNode(r) as endNode
    WITH type, labels(startNode)[0] as startLabel, labels(endNode)[0] as endLabel, COUNT(*) as rCount
    ORDER BY rCount
    RETURN collect({count: rCount, type: type, node: {source: startLabel, target: endLabel}}) as relationships 
}
RETURN nodes, relationships
[
{
  "node": {
"source": "Person",
"target": "Person"
  },
  "count": 3,
  "type": "FOLLOWS"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 9,
  "type": "REVIEWED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 10,
  "type": "WROTE"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 15,
  "type": "PRODUCED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 44,
  "type": "DIRECTED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 172,
  "type": "ACTED_IN"
}
]

BTW- I assumed one label per node. 

@glilienfield  Thank you so much. Yes one label per node
Thank you so much once again

You are welcome.  The secret is to group on the triplet: type, source label and target label, to get the correct count.  Then collect them in the format you desired.