Can `apoc.when` or `CASE` do nothing for ELSE instead of returning `null` after queryNodes()

[clem]

This documentation doesn't describe what happens when there's an empty ELSE clause, e.g. ''

Neo4j Graph Database Platform

Conditional Cypher Execution - APOC Documentation

This section describes procedures and functions for conditionally executing Cypher statements.

I'm trying to do something like:

CALL apoc.when(score > 7,
     "RETURN node, score",  // keep nodes when score > 7
    '',      // filter out things when score <= 7
    {node:node, score:score}) 
YIELD value
RETURN value.score, value.node.name

However, I get a bunch of nulls (which I can suppress with DISTINCT)

Also not it's not explained here:

Neo4j Graph Database Platform

Expressions - Neo4j Cypher Manual

is that the return values of the built-in CASE seem to be restricted to a single thing, which could be a LIST

WITH node, score, cname
RETURN 
CASE
WHEN score > 7 THEN score, node.name // doesn't work but this does:  [score, node.name]
END

However, this expression also returns null for the cases that fall off the end of the CASE.

There seems to be some restrictions about where CASE can occur. It seems like it has to be after a RETURN or inside a FOREACH. I haven't quite figured it out.

I believe some improvements to the doc is needed for conditionals or perhaps improvements to Cypher. E.g., it's not obvious when or why to use CASE vs. apoc.case (apoc.do.case is used when there are writes to the DB.)

[mark_needham]

Hey,

The assumption with apoc.when is that in all the branches you want to return a value, but that the value might be different depending on the branch.

If you want to filter out values (e.g. score <= 7) couldn't you do that using a WHERE clause instead?

MATCH (node)
WHERE node.score > 7
RETURN ...

Or are you trying to do something more complicated than that?

[clem]

It's more complex than that, but... WHERE can appear in more places than documented ( WHERE - Neo4j Cypher Manual ). I am surprised! WHERE does work for what I am trying to! Knowing what to try is half the battle with Cypher! The documentation for WHERE needs to mention that it can be used after CALL.

My use case: I have a small subset of Useful Nodes (a few dozens) and a large set of Categorical Nodes whose Name is a short description. A few Categories might match based on fulltext searching.

So putting the WHERE after the CALL works!

// Make full text index of my Ontology Names
CALL db.index.fulltext.createNodeIndex("FullTextIndexOfCategories",["Category"],["Name"]);

// Next match my Useful nodes to Categories
MATCH(u:Useful)
WHERE u.importance > 80 // Small subset of my Useful nodes
WITH u.Name as uname // fulltext search for each uname 
CALL db.index.fulltext.queryNodes("FullTextIndexOfCategories", uname) YIELD node, score
WHERE score > 6
RETURN score AS SearchScore, uname, node.Code AS CategoryCode, node.Name AS CategoryName

I had also tried using CALL inside a FOREACH but couldn't get that to work. (FOREACH is very fussy....)

The result is is on uname with 1 or more Categories (with duplicate unames in a column).

One issue is I get multiple of categories coming back per uname.

By using COLLECT, I can limit the number of top search results:

// Make full text index of my Ontology Names
CALL db.index.fulltext.createNodeIndex("FullTextIndexOfCategories",["Category"],["Name"]);

// Next match my Useful nodes to Categories
MATCH(u:Useful)
WHERE u.importance > 80 // Small subset of my Useful nodes
WITH u.Name as uname // fulltext search for each uname 
CALL db.index.fulltext.queryNodes("FullTextIndexOfCategories", uname) YIELD node, score
WHERE score > 6
// Make Nodes and Scores into Lists
WITH uname, COLLECT(node) AS nodes, COLLECT(score) AS scores
RETURN 
  scores[0] AS SearchScore,  // return only the top score
  uname,
  nodes[0].Code AS CategoryCode,  // return only the top Category Code
  nodes[0].Name AS CategoryName  // return only the top Category Name

However, I'm a puzzled how to get the top 2 search results. This doesn't work:

  nodes[0..1].Code AS CategoryCode,  // want to return top two Category Code
  nodes[0..1].Name AS CategoryName  // want to return top twoCategory Name

I get the error msg:

Type mismatch: expected Map, Node, Relationship, Point, Duration, Date, Time, LocalTime, LocalDateTime or DateTime but was List

[clem]

I saw in another post, you can limit the results returned from queryNodes()

github.com

neo4j/neo4j - Kernel

Neo4j-4.1-changelog

Graphs for Everyone. Contribute to neo4j/neo4j development by creating an account on GitHub.

so:

CALL db.index.fulltext.queryNodes("FullTextIndexOfCategories", uname, {limit: 2})
YIELD node, score

[mark_needham]

clem:

 nodes[0..1].Code AS CategoryCode,  // want to return top two Category Code
  nodes[0..1].Name AS CategoryName  // want to return top twoCategory Name

The reason that doesn't work is that you still have a list of nodes after the nodes[0..1] So you need to map over each of those values and pull out the code:

[node in nodes[0..1] | node.Code] AS categoryCode,
[node in nodes[0..1] | node.Name] AS categoryName