List declared before UNWINDing a second list becomes null after executing a MATCH returning no results from the unwound list

Neo4j Graph Platform Cypher
1

I have the following scenario in a neo4j db:
There are tasks which can be assigned to different users based on some criteria. There's an optional criterion (some tasks have a filter for user's location, some don't).
I need to find all tasks for a user (if they have a location filter, I need to check user's location as well, if they don't I match only by the rest of the criteria).

I've tried to collect the tasks matching the mandatory criteria, then filter those which don't require the optional filter, then filter those which require the optional filter and match the current user and eventually merge the two lists.

Could you also suggest a more efficient way to do this please?

Here's a minimal example (you can see a more complex example, similar to the real scenario, at the end of the post)

WITH [{a: 'test'}, {a: 'a', b: 'b'}] AS initialList

WITH [i IN initialList WHERE i.b IS NULL] AS itemsWithoutB, initialList

UNWIND initialList AS item
MATCH (item) WHERE item.a IS NULL

RETURN COLLECT(item) + itemsWithoutB

I would expect here to have the content of itemsWithoutB returned, but I get no records (Response: []).

Note that if the MATCH done after UNWIND does actually return some records, then the content of itemsWithoutB is returned as well.

For example:

WITH [{a: 'test'}, {a: 'a', b: 'b'}] AS initialList

WITH [i IN initialList WHERE i.b IS NULL] AS itemsWithoutB, initialList

UNWIND initialList AS item
MATCH (item) WHERE item.a IS NOT NULL

RETURN COLLECT(item) + itemsWithoutB

this returns:

╒═════════════════════════════════════════════╕
│"COLLECT(item) + itemsWithoutB"              │
╞═════════════════════════════════════════════╡
│[{"a":"test"},{"a":"a","b":"b"},{"a":"test"}]│
└─────────────────────────────────────────────┘

Neo4j version: enterprise 3.5.6

What am I missing here, please?

I'm adding here a more complex example, closer to the real scenario:

Generate initial data:

MERGE (d:Device {code: 'device1', latitude:90.5, longitude: 90.5})-[:USED_BY]->(u:User {name: 'user1'})-[:WORKS_IN]->(c:Country {code: 'RO'})<-[targets:TARGETS]-(:Task {name: 'task1', latitude: 90.5, longitude: 90.5, maxDistance: 1000, maxMinutesAfterLastInRange: 99999})<-[:IN_RANGE {timestamp: datetime()}]-(d)

MERGE (c)<-[:TARGETS]-(:Task {name: 'task2'})

MERGE (c)<-[:TARGETS]-(:Task {name: 'task4', latitude: 10.5, longitude: 10.5, maxDistance: 1, maxMinutesAfterLastInRange: 99999})

CREATE (:User  {name: 'user2'})-[:WORKS_IN]->(:Country {code: 'GB'})<-[:TARGETS]-(:Task {name: 'task3'})

Here's a neo4j console link for this example.

I want to be able to use the same query to find the tasks for any user (task1 and task2 should be returned for user1, task3 for user2, task4 shouldn't be returned for neither of them).

The following query works for user1, but doesn't work if I change the user name filter to "user2":

MATCH (user:User {name: "user1"})-[:WORKS_IN]->(country) 
OPTIONAL MATCH (device:Device)-[:USED_BY]->(user)
WITH country, device
MATCH (task:Task)-[:TARGETS]->(country) 
WITH COLLECT(task) AS filteredTasks, device

WITH [t IN filteredTasks WHERE t.latitude IS NULL OR t.longitude IS NULL] AS matchedTasksWithoutLocationFilter, filteredTasks, device

UNWIND filteredTasks AS task
MATCH (device)-[inRange:IN_RANGE]->(task)
WHERE task.maxMinutesAfterLastInRange IS NOT NULL 
AND duration.between(datetime(inRange.timestamp), datetime()).minutes <= task.maxMinutesAfterLastInRange

RETURN COLLECT(task) + matchedTasksWithoutLocationFilter AS matchedTasks

Can anyone please suggest a query for this scenario?

2

According to your scenario, 'user2' has not used any device and hence the query fails for user2.

MATCH (device:Device)-[:USED_BY]->(user)
RETURN device.code as device, user.name

Result: device1, user1

May be you should start with collecting devices and users.......like

MATCH (device:Device)-[:USED_BY]->(user)
WITH device, user
MATCH (user)-[:WORKS_IN]->(country)
WITH device, user, country
..........

3

The following query was suggested by Pablissimo in this StackOverflow answer and it's working perfectly for my scenario:

MATCH (user: User {name: "user1" })-[:WORKS_IN]->(country)<-[:TARGETS]-(task: Task)
OPTIONAL MATCH (task)<-[inRange: IN_RANGE]-(device: Device)-[:USED_BY]->(user)
    WITH task, inRange
   MATCH (task)
   WHERE (task.latitude IS NULL OR task.longitude IS NULL)
      OR (inRange IS NOT NULL AND 
          task.maxMinutesAfterLastInRange IS NOT NULL AND 
          duration.between(datetime(inRange.timestamp), datetime()).minutes <= task.maxMinutesAfterLastInRange)
  RETURN task
4

Looks good, however you don't need MATCH (task), the WHERE can apply to the preceding WITH.

1 Like