What happens when a list is null?

joshcornejo · April 9, 2025, 2:53pm

I have the cypher below, the case is with first entry in someList:

        { id: 'some_id1', a: [ 'a1' ], r: [] },

If I return a list containing .r ... i get 4 elements (right count), if i return .a, I get 3 elements (this first one is missing) ... and if I return the variables - they are null.

I've been iterating and tried nested calls to see if i could figure out why, not sure if necessary.

WITH [
  { actual: "abcde-51e6-4ef0-9549-c3304f42664e", proposed: "abcde-51e6-4ef0-9549-c3304f42554e" }, 
  { actual: "three-066e-4a48-9a7c-2ec1a36d6d1e", proposed: "three-066e-4a48-9a7c-2ec1a36d6d1e" },
  { actual: "091282f32-51e6-4ef0-9549-c3304f42664e", proposed: "091282f32-51e6-4ef0-9549-c3304f42664e" },
  { actual: "five-066e-4a48-9a7c-2ec1a36d6d1w", proposed: "five-066e-4a48-9a7c-2ec1a36d6d1w" },
  { actual: "08182f32-51e6-4ef0-9549-c3304f42664e", proposed: "08182f32-51e6-4ef0-9549-c3304f42664e" },
  { actual: "four-066e-4a48-9a7c-2ec1a36d6d1e", proposed: "four-066e-4a48-9a7c-2ec1a36d6d1e" },
  { actual: "alpha-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "alpha-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "beta-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "beta-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "betagamma-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "betagamma-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "epsilon-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "epsilon-066e-4a48-9a7c-2ec1a36d6d2f" }
] AS listOfValues

$someList: [
        {
            id: 'some_id1',
            a: [ 'a1' ],
            r: []
        },
        {
            id: 'some_id2,
            a: [ 'a1', 'a2' ],
            r: [ 'epsilon-066e-4a48-9a7c-2ec1a36d6d2f', 'four-066e-4a48-9a7c-2ec1a36d6d1e' ]
        },
        {
            id: 'some_id3',
            a: [ 'a2' ],
            r: [ 'epsilon-066e-4a48-9a7c-2ec1a36d6d2f', 'beta-066e-4a48-9a7c-2ec1a36d6d2f' ]
        },
        {
            id: 'some_id4',
            a: [ 'a1', 'a2' ],
            r: [ 'five-066e-4a48-9a7c-2ec1a36d6d1w', 'beta-066e-4a48-9a7c-2ec1a36d6d2f' ]
        }
]        

UNWIND $someList AS someElement
WITH someElement, listOfValues
CALL {
    WITH someElement, listOfValues
    // First make refinement safe and check if we should process
    WITH 
      someElement, 
      listOfValues,
      COALESCE(someElement.r, []) AS safeR
    
    // Main processing block - preserves context
    WITH someElement, listOfValues, safeR
    CALL {
        WITH someElement, listOfValues, safeR
        // Only process if we have refinements to match
        WITH someElement, listOfValues, safeR
        WHERE size(safeR) > 0
        
        // Find matching pairs
        UNWIND listOfValues AS pair
        WITH pair
        WHERE pair.proposed IN safeR
        RETURN pair.actual AS theActual
    }
    WITH someElement, listOfValues, COLLECT(theActual) AS matchedActuals
      
    RETURN someElement as SOA, listOfValues AS loV, COALESCE(someElement.a, []) + COALESCE(matchedActuals, []) AS allInterests
}
// Final output with all context preserved
RETURN 
  COALESCE(allInterests, COALESCE(someElement.a, [])) AS result

therese.magnusson · April 9, 2025, 3:30pm

Hi,

From what I can see of your query you have a

WITH someElement, listOfValues, safeR
        WHERE size(safeR) > 0

which filters out rows where safeR is empty. This is probably the culprit of why you don't see the last row you expected.

I tried out some debugging of my own and saw (with your listOfValues and someList, not shown here due to size):

WITH [...] AS listOfValues
UNWIND $someList AS someElement
WITH someElement, listOfValues
CALL (someElement, listOfValues) {
    // First make refinement safe and check if we should process
    WITH 
      someElement, 
      listOfValues,
      COALESCE(someElement.r, []) AS safeR
    
    // Main processing block - preserves context
    WITH someElement, listOfValues, safeR
    CALL (someElement, listOfValues, safeR) {
        // Only process if we have refinements to match
        WITH someElement, listOfValues, safeR
        WHERE size(safeR) > 0
        
        // just return something to show result
        RETURN 1 AS x
    }
    // return the new variables
    RETURN safeR, x
}
// just return everything to see what we get
RETURN *

that I get back only three rows

while if I remove the innermost CALL that has that filter in it:

WITH [...] AS listOfValues
UNWIND $someList AS someElement
WITH someElement, listOfValues
CALL (someElement, listOfValues) {
    // First make refinement safe and check if we should process
    WITH 
      someElement, 
      listOfValues,
      COALESCE(someElement.r, []) AS safeR

     // return the new variable
    RETURN safeR
}
// just return everything to see what we get
RETURN *

then I get all 4 rows

Not sure if this helps you, but I think it might be the culprit

/Therese

joshcornejo · April 9, 2025, 5:03pm

When I add the second part to replace items - it fails.

     // Find matching pairs
        UNWIND listOfValues AS pair
        WITH pair
        WHERE pair.proposed IN safeR
        RETURN pair.actual AS theActual

andrew_bowman · April 9, 2025, 7:44pm

Isn't this expected?

Your first item in $someList is:
{
id: 'some_id1',
a: [ 'a1' ],
r:
}

r, and therefore safeR is an empty list:

Later, you have a filter for:

        WITH pair
        WHERE pair.proposed IN safeR

This filters out any row where the proposed item isn't in safeR...so this eliminates all rows for that first item of your list, as it is impossible for anything to be present in the empty list for that row.

andrew_bowman · April 9, 2025, 7:57pm

I think I see where your confusion is.

Let's zoom in on this block:

    // Main processing block - preserves context
    WITH someElement, listOfValues, safeR
    CALL {
        WITH someElement, listOfValues, safeR
        // Only process if we have refinements to match
        WITH someElement, listOfValues, safeR
        WHERE size(safeR) > 0
        
        // Find matching pairs
        UNWIND listOfValues AS pair
        WITH pair
        WHERE pair.proposed IN safeR
        RETURN pair.actual AS theActual
    }
    WITH someElement, listOfValues, COLLECT(theActual) AS matchedActuals

You have a wrong assumption here, that just because we are making a subquery call that the context (rows) are preserved once the subquery ends.

That is only true for a Unit subquery, where nothing is returned.

It is not true for this kind of subquery.
You have two separate WHERE clauses that are filtering out rows here (WHERE size(safeR) > 0 and WHERE pair.proposed IN safeR). The affected rows are removed, and if there are no rows left to process (and nothing to return) for the driving rows into the subquery, those rows will be eliminated.

There IS something you can do to preserve those rows:
Perform your COLLECT() aggregation inside of the subquery instead of after it.
This works because

You can aggregate on zero rows to generate a resulting row (collect() on 0 rows results in 1 result row with an empty list ) , but only if there is no explicit grouping key present
You don't need an explicit grouping key, because the driving rows of the subquery already imply a grouping key (someElement, listOfValues, safeR)

So try this instead:

    // Main processing block - preserves context
    WITH someElement, listOfValues, safeR
    CALL {
        WITH someElement, listOfValues, safeR
        // Only process if we have refinements to match
        WITH someElement, listOfValues, safeR
        WHERE size(safeR) > 0
        
        // Find matching pairs
        UNWIND listOfValues AS pair
        WITH pair
        WHERE pair.proposed IN safeR

        // the aggregation ensures that no driving rows to the subquery get filtered out
        // because a collect() on 0 rows results in a single result row, which preserves the driving rows
        RETURN COLLECT(pair.actual) AS matchedActuals
    }
    WITH someElement, listOfValues, matchedActuals

glilienfield · April 9, 2025, 11:59pm

I may be completely off in my interpretation of what you are doing. It seems you want to find for each item in someList, those elements in listOfValues whose proposed value is in the someList element's 'r' list. When there is a match, you want to return the actual value.

If my understanding is correct, does this give you the result you are looking for?

WITH [
  { actual: "abcde-51e6-4ef0-9549-c3304f42664e", proposed: "abcde-51e6-4ef0-9549-c3304f42554e" }, 
  { actual: "three-066e-4a48-9a7c-2ec1a36d6d1e", proposed: "three-066e-4a48-9a7c-2ec1a36d6d1e" },
  { actual: "091282f32-51e6-4ef0-9549-c3304f42664e", proposed: "091282f32-51e6-4ef0-9549-c3304f42664e" },
  { actual: "five-066e-4a48-9a7c-2ec1a36d6d1w", proposed: "five-066e-4a48-9a7c-2ec1a36d6d1w" },
  { actual: "08182f32-51e6-4ef0-9549-c3304f42664e", proposed: "08182f32-51e6-4ef0-9549-c3304f42664e" },
  { actual: "four-066e-4a48-9a7c-2ec1a36d6d1e", proposed: "four-066e-4a48-9a7c-2ec1a36d6d1e" },
  { actual: "alpha-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "alpha-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "beta-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "beta-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "betagamma-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "betagamma-066e-4a48-9a7c-2ec1a36d6d2f" },
  { actual: "epsilon-066e-4a48-9a7c-2ec1a36d6d2f", proposed: "epsilon-066e-4a48-9a7c-2ec1a36d6d2f" }
] AS listOfValues,
[
    {id: 'some_id1',a: [ 'a1' ],r: []},
    {id: 'some_id2',a: [ 'a1', 'a2' ],r: [ 'epsilon-066e-4a48-9a7c-2ec1a36d6d2f', 'four-066e-4a48-9a7c-2ec1a36d6d1e']},
    {id: 'some_id3',a: [ 'a2' ],r: [ 'epsilon-066e-4a48-9a7c-2ec1a36d6d2f', 'beta-066e-4a48-9a7c-2ec1a36d6d2f']},
    {id: 'some_id4',a: [ 'a1', 'a2' ],r: [ 'five-066e-4a48-9a7c-2ec1a36d6d1w', 'beta-066e-4a48-9a7c-2ec1a36d6d2f']}
] AS someList
RETURN [i in someList | {id: i.id, a: i.a, r: i.r, result: [x in listOfValues where x.proposed in i.r | x.actual]}]

Result:

[
{
  "id": "some_id1",
  "result": [],
  "r": [],
  "a": [
    "a1"
  ]
}
, 
{
  "id": "some_id2",
  "result": [
    "four-066e-4a48-9a7c-2ec1a36d6d1e",
    "epsilon-066e-4a48-9a7c-2ec1a36d6d2f"
  ],
  "r": [
    "epsilon-066e-4a48-9a7c-2ec1a36d6d2f",
    "four-066e-4a48-9a7c-2ec1a36d6d1e"
  ],
  "a": [
    "a1",
    "a2"
  ]
}
, 
{
  "id": "some_id3",
  "result": [
    "beta-066e-4a48-9a7c-2ec1a36d6d2f",
    "epsilon-066e-4a48-9a7c-2ec1a36d6d2f"
  ],
  "r": [
    "epsilon-066e-4a48-9a7c-2ec1a36d6d2f",
    "beta-066e-4a48-9a7c-2ec1a36d6d2f"
  ],
  "a": [
    "a2"
  ]
}
, 
{
  "id": "some_id4",
  "result": [
    "five-066e-4a48-9a7c-2ec1a36d6d1w",
    "beta-066e-4a48-9a7c-2ec1a36d6d2f"
  ],
  "r": [
    "five-066e-4a48-9a7c-2ec1a36d6d1w",
    "beta-066e-4a48-9a7c-2ec1a36d6d2f"
  ],
  "a": [
    "a1",
    "a2"
  ]
}
]

Excuse my interjection if my understanding is incorrect.

joshcornejo · April 10, 2025, 6:31am

Great advice, what I needed was this:

  WITH [i in someList | i.a + [x in listOfValues where x.proposed in i.r | x.actual] ] AS theList
    UNWIND theList AS a referenceList // <-- now i have a list of node ids

Topic		Replies	Views
How to remove objects containing NULL from COLLECT() clause in CYPHER query? Cypher	3	4102	September 21, 2021
Empty output in subquery CALL leads to null output Cypher	2	148	February 15, 2024
Path from a null object Cypher	3	364	March 26, 2020
Dealing with not existent properties and null values Cypher cypher	4	4693	November 8, 2018
Result object return List<Record> empty of cypher query Cypher	0	248	December 21, 2021

What happens when a list is null?

Related topics