Cypher CASE WHEN / ELSE along with WITH - trouble returning values

Question

I have two different parent nodes that both have a particular relationship of a specific relationship type (pointing to various third and fourth child nodes). Only one of the parent nodes has a another relationship of a different relationship type (that point to various fifth and sixth child nodes). I would like to write a cypher query that uses the WITH operator so that I can return the node.id of the parent (for the RELATIONSHIP_ONE) and a collection array of target.id's for child nodes that result from RELATIONSHIP_TWO. I am having trouble with the cypher query returning anything for node's that don't have a relationship of type RELATIONSHIP_TWO

This returns a json object as the first index of an array:

MATCH (n)-[rel_one:RELATIONSHIP_ONE]-()
WHERE  n.id='EXAMPLE_ID_ONE'
WITH   n
MATCH (n)-[rel_two:RELATIONSHIP_TWO]->(target)
RETURN n.id                   AS id, 
       COLLECT(target.id) AS targetlist;")

[
  {
    "id": "EXAMPLE_ID_ONE",
    "targetlist": [
      "target_one_id",
      "target_two_id",
      "target_three_id",
      "target_four_id",
      "target_five_id"
    ]
  }
]

This returns an empty array:

MATCH (n)-[rel_one:RELATIONSHIP_ONE]-()
WHERE n.id='EXAMPLE_ID_TWO'
WITH n
MATCH (n)-[rel_two:RELATIONSHIP_TWO]->(target)
RETURN n.id AS id, 
       COLLECT(target.id) AS targetlist;")

[]

Because there are no results in this EXAMPLE_ID_TWO cypher query (where n and target are related by a relationship type of RELATIONSHIP_TWO) it seems to cancel out this cypher query's ability to return the n.id value. Perhaps like so:

[
  {
    "id": "EXAMPLE_ID_TWO",
    "targetlist": []          // or even null
  }
]

I made an attempt at using CASE WHEN / ELSE expressions but it failed to return a value (even with EXAMPLE_ID_ONE). examples below. I would like to use an empty array for the value of targetlist if there are no RELATIONSHIP_TW0's joining n and target nodes. I would be very grateful for your help.

MATCH (n)-[rel_one:RELATIONSHIP_ONE]-()
WHERE n.id='EXAMPLE_ID_ONE'
WITH n
MATCH (n)-[rel_two:RELATIONSHIP_TWO]->(target)
RETURN n.id AS id, 
       CASE WHEN target is NULL THEN []
            ELSE COLLECT(target.id) END AS targetlist;

MATCH (n)-[rel_one:RELATIONSHIP_ONE]-()
WHERE n.id='EXAMPLE_ID_TWO'
WITH n
MATCH (n)-[rel_two:RELATIONSHIP_TWO]->(target)
RETURN n.id AS id, 
       CASE WHEN target is NULL THEN [];
            ELSE COLLECT(target.id) END AS targetlist;

What is the cypher part in the end of your question? a solution? or something you tried? — Supamiu

Michael Hunger Michael Hunger · Accepted Answer · 2015-09-24T06:19:39

make your second match an optional match, which is acting like an outer join, i.e. also produces at least one result if there is no match

MATCH (n:Label)
WHERE n.id='EXAMPLE_ID_TWO' and exists((n)-[:RELATIONSHIP_ONE]-())
OPTIONAL MATCH (n)-[:RELATIONSHIP_TWO]->(target)
RETURN n.id AS id, 
       filter(x in COLLECT(target.id) where not x is null) AS targetlist

Cypher CASE WHEN / ELSE along with WITH - trouble returning values

1 Answers