CODEWITHSUNDEEP
neo4jspring-data-neo4j
Sunny Agarwal
Sunny Agarwal

Reputation: 1491

neo4j query for nullable property

I am using spring-neo4j. I have an entity called User with properties (username, firstName, lastName) and firstName and lastName are optional properties.

Now I want to implement a user search query which will search across all three properties.

@Query(value = "start user=node:__types__(className='com.xxx.entity.User') where user.username =~ {0} or user.firstName =~ {0} or user.lastName =~ {0} return user")
List<User> searchByName(String keyword);

The query fails saying that:

The property 'firstName' does not exist on Node[21].

However, if I search only on username, it gives me the result. I tried using the ? operator for nullable properties:

 @Query(value = "start user=node:__types__(className='com.xxx.entity.User') where user.username =~ {0} or user.firstName? =~ {0} or user.lastName? =~ {0} return user")
 List<User> searchByName(String keyword);

But this will fetch me all the nodes where firstName or lastName are missing.

Any idea how do I implement this query?

Upvotes: 0

Views: 623

Answers (1)

jjaderberg
jjaderberg

Reputation: 9952

Would

start user=node:__types__(className='com.xxx.entity.User') 
where user.username =~ {0} 
  or (has(user.firstName) and user.firstName =~ {0}) 
  or (has(user.lastName) and user.lastName =~ {0}) 
return user

work?

Edit:

There are many reasons for a query not to return data. If you want help with that, share sample data and the parameters you are passing with your query. Use Neo4j console to share data and put the link and parameter examples in your question.

Until then, this might be helpful:

Use ! instead of ?.

[--verbose]
In Neo4j 1.x you can indeed use the ? and ! syntax, but you are using them wrong. ? defaults to true when the property is missing, which means your query matches the nodes you want plus all nodes that don't have a firstName or that don't have a lastName. ! will default to false when the property is missing and therefore exclude those nodes. n.prop! = val is equivalent to the has(n.prop) and n.prop=val I used above. See Neo4j stable docs.

In Neo4j 2.0+ the !? syntax is removed. Non-existing properties default to null. Therefore n.prop=val will evaluate to false exactly when has(n.prop) does(*). This means that your original query would work in 2.0–the query would resolve a missing property as a non-match and it would neither break nor include all nodes that don't have firstName or lastName(**). See Neo4j milestone docs. You might as well use syntax that won't break when SpringDataNeo4j moves to Neo4j 2.0, so use has(n.prop) and n.prop=val which works on either version.

Also please see How to cypher query a neo4j DB with WHERE clause on sparse property.

(*)(except it won't if val=null)
(**)(unless you pass null as a parameter)

Upvotes: 1

Related Questions