It's more complex than that, but... WHERE
can appear in more places than documented ( WHERE - 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