Super Label & Sub Label Counts

cypher

(Sandip) #1

Version 3.4.8

//Sample Interaction node with multiple labels
<
CALL apoc.create.node(['Interaction'] + 'App_Reg', {userid:toInteger(a.userid), event_date:date(a.reg_date)}
CALL apoc.create.node(['Interaction'] + 'Open', {userid:toInteger(a.userid), event_date:date(a.reg_date)}
CALL apoc.create.node(['Interaction'] + 'Click', {userid:toInteger(a.userid), event_date:date(a.reg_date)}
CALL apoc.create.node(['Interaction'] + 'Call', {userid:toInteger(a.userid), event_date:date(a.reg_date)}
CALL apoc.create.node(['Interaction'] + 'Chat', {userid:toInteger(a.userid), event_date:date(a.reg_date)}
/>

So above a node, I have “Interaction” is a super label and App_Reg, Open, Call, Click, Chat etc as sub-labels. Interaction nodes are linked to the individual user and here is match statement

MATCH (u:User)-[:has_interaction]->(i:Interaction)

Since interaction nodes have multiple labels like App_Reg, Open, Call, Click, Chat etc.

I have following Cypher which is returning me correct results to provide count by each interaction.
<
MATCH (u:User)-[:has_interaction]->(o:Interaction)
WITH DISTINCT LABELS(o) AS temp, COUNT(o) AS tempCnt
UNWIND temp AS label
RETURN label, SUM(tempCnt) AS cnt
/>

Output is
Interaction-->205
App_Reg-->100
Open-->50
Click-->40
Call-->10
Chat-->5

But I want the following output, I tried multiple ways but no luck. Need expert help here.

[Super_Label]-->[Lable]-->[Count]
Interction-->App_Reg-->100
Interction-->Open-->50
Interction-->Click-->40
Interction-->Call-->10
Interction-->Chat-->5

I can do hard code but there are other nodes like Interaction with multiple tables. How can we achieve the above results?

Thanks in Advance.
Sandip


(Andrew Bowman) #2

There's currently no concept of superlabels in Neo4j, this is just a label that happens to be applied to all of the other labels.

In order to extract out the superlabels, you would need to collect the label collections of the nodes, then perform an intersection across all of them to find those that are common to all. Then you can do your UNWINDing back to individual labels.

Something like this (using APOC helper functions to do the intersection):

match (u:User)-[:has_interaction]->(o:Interaction)
with collect(labels(o)) as allLabels
with allLabels, reduce(superlabels = head(allLabels), labelSet in tail(allLabels) | apoc.coll.intersection(superlabels, labelSet)) as superlabels
unwind allLabels as labels
unwind labels as label
with superlabels, label, count(label) as count
// where not label in superlabels // uncomment the line if you want to exclude superlabel counts
return superlabels, label, count

(Sandip) #3

Andrew, this is what I was looking for.

My bad there is no super labels concept in Neo4j but we are calling it internally for more clarification :slight_smile:

Appreciate your quick response here.

Regards,
Sandip