Count 2 Separate Conditions of Same Node

Neo4j Graph Platform Cypher
1

Greetings,

I'm trying to return 2 different counts of different conditions of a property in a Node.

I have a node, let's call it obj:Object, and obj has a property of obj.Definition (string).
Some objs have a definition and others do not. The obj.Definition property is not necessarily NULL, so that's why I am using the type of where clause seen below.

I want to return 2 different counts based on these 2 different conditions... a count of those with a definition and a count of those without a definition.

Here's what I'm trying...

(obj:Object)
With obj, obj.Definition as HasDefn
Where HasDefn contains ' ' //there's a space between the ' '
With obj, HasDefn, obj.Definition as NoDefn
Where (not (NoDefn contains ' ')) //again, there's a space between the ' '
With HasDefn, NoDefn
Return count(HasDefn), count(NoDefn)

How do I get 2 separate counts for 2 separate conditions?

Thank you for any help provided!

2

The best test for whether a node has a property is
WHERE x.prop IS NOT NULL

3

Thanks Elaine, but I shared in my original statement why I can't use your suggestion.
Even if I use your suggestion, the query still does not work.

4

Can you share a working example that illustrates the problem?

5

Try:

Match(obj:Object)
Call {
With obj
With obj
Where obj.Definition contains ' '
Return count(*) as hasDef
}
Call {
With obj
With obj
Where not (obj.Definition contains ' ')
Return count(NoDefn) as noDef
}
return hasDef, noDef
6

Thanks @glilienfield , though I'm a little confused as to why I would run the same With statements back to back and the Where statement with an alias that hasn't been established yet?
This is the error I get if I run your query...

Variable `HasDefn` not defined (line 5, column 7 (offset: 136))
"Where HasDefn contains ' '"
7

Good catch. I updated the query.

You need two ‘with’. Statements in a call subquery if you want to use a where clause, otherwise the parser will complain the first with clause needs to be ‘simple’.

8

Thanks, we're getting closer. If I run your query...

(obj:Obj)
Call {
With obj
With obj
Where obj.Definition contains ' '
Return count(*) as hasDef
}
Call {
With obj
With obj
Where not (obj.Definition contains ' ')
Return count(*) as noDef //I changed the variable you had inside count since it hadn't been declared
}
return hasDef, noDef

it results in the following...

hasdefnodef
hasdefnodef1190×533 5.38 KB

I'm hoping for a single totaled number under hasDef & noDef.
So I guess if I include sum in the final return...

hasdefnodeftotal
hasdefnodeftotal1002×444 24.5 KB

I do get the expected result. Looks like I need to read up on call subqueries.

But is this considered the best method? It took a loooooong time to run the query (25+ sec) when including that final sum.

9

Ok, the 'call subquery' is actually a bad approach. I overlooked that the call subquery would not collect all the records, but is processed one record at a time. That explains receiving ones and zeros being returned from the query.

Try this instead:

match (obj:Obj)
with (obj.Definition contains ' ') as predicate
with 
    case predicate when true then 1 else 0 end as hasDef,
    case predicate when false then 1 else 0 end as noDef 
return sum(hasDef) as hasDefCount, sum(noDef) as noDefCount
1 Like
10

Very cool! More new to me methods to learn and it's very fast. Thank you!

1 Like