Variable evaluation in FOREACH

[stugorf]

I am loading a CSV and during each row I am trying to set properties for nodes dynamically within a FOREACH block. I have verified that the variables are in scope and available in the loop, but I am finding that when a variable is part of a String concatenation that it is not evaluated. Does anyone have an idea why t.state and t.panel are set, but that t.val is not?

WITH ["TEST-1", "TEST-2"] AS panels, row
FOREACH (panel in panels |
    MERGE (t:DEBUG)
    SET t.state   = row.State
    SET t.panel   = panel
    SET t.val     = row["Result " + panel + "-ID"]
)

[andrew_bowman]

Your result means row["Result " + panel + "-ID"] is evaluating to null...there is no key for that in your CSV header.

You should probably examine your CSV headers, making sure that your case is correct and that there are no extra (or missing) spaces. The key must be exact.

[stugorf]

The key is correct, when I replace panel with either "TEST-1" or "TEST-2" then it sets a value for t.val.

[stugorf]

I am now trying this to load a variable named panels without explicitly creating the list :

with [x in row where x contains "-Plate" | x] as panels,row
FOREACH (panel in panels |
    MERGE (p:Plate {name:panel})
    ON CREATE SET p.plate_id   = row[panel]
)

but I get this error for the use of panel as an index:
Neo.ClientError.Statement.TypeError: Expected String("Olink CARDIOMETABOLIC-Plate ID") to be a org.neo4j.values.storable.NumberValue, but it was a org.neo4j.values.storable.StringWrappingStringValue

[andrew_bowman]

I think you meant to use:

with [x in keys(row) where x contains "-Plate" | x] as panels,row

This way panels contains the keys that end in -Plate, is that what you wanted? I don't think this works with your MERGE or ON CREATE SET, though, as this would only create a single node per key, and only the first one would set the plate_id value (you shouldn't process a CSV this way, only the first row would matter)

[stugorf]

Returns this error:

Neo.ClientError.Statement.SyntaxError: Type mismatch: expected Map, Node or Relationship but was List<String> (line 22, column 17 (offset: 585))
"with [x in keys(row) where x contains "-Plate" | x] as panels,row"

My intent is to parse the CSV and build a list of the keys that match the contains expression. I want to create a node per key that has properties name:key value:key_value.

[stugorf]

I changed my load to use WITH HEADERS and it works!

[stugorf]

This works, and creates the 13 nodes I was expecting, with the appropriate properties!

with [x in keys(row) where x contains "-Plate" | x ] as panels,row
FOREACH (panel in panels |
    MERGE (p:Plate {name:panel})
    ON CREATE SET p.plate_id   = row[panel]
)

[andrew_bowman]

Sure, but that does it only for the first row, correct? For all subsequent rows in the CSV, MERGE will match to the existing plate by that name and won't overwrite the plate_id.

Remember that MERGE is like a MATCH, and if the thing doesn't exist already, a CREATE. Since your MERGE will create all the nodes you need by the time the first row is processed, for the processing of the rest of the CSV all the other rows are essentially no-op.

[stugorf]

I'll change my properties to be set in the MERGE rather than in an ON CREATE line. Thank you for all of your support.

[andrew_bowman]

That was meant to be a snippet, not standalone. You would need to use that within your LOAD CSV, with row being the variable for each row in the CSV. It compiles fine on my side, when included like that within such a query.

If you want such a node per value, then you will need to MERGE with both variables, not just one ( for the name, you would be creating just a single node...nothing would happen for any other row in the CSV beyond the first row).

// your LOAD CSV here
with [x in row where x contains "-Plate" | x] as panels,row
FOREACH (panel in panels |
    MERGE (p:Plate {name:panel, plate_id:row[panel]})
)

You'll want a composite index on :Plate(name, plate_id) to ensure it's quick, otherwise it will slow down as more rows are added.

[stugorf]

Hmmm, trying to put the value into the MERGE statement does not work:

// Plate Nodes
with [x in keys(row) where x contains "-Plate" | x ] as panels,row
FOREACH (panel in panels |
    MERGE (p:Plate {name:panel, plate_id:row[panel]})
)

Results in:
Neo.ClientError.Statement.SemanticError: Cannot merge node using null property value for plate_id

[stugorf]

I tried reducing the MERGE statement to only MERGE (p:Plate {plate_id:row[panel]}) which did not work. Do I need to use an apoc to set a property value where the value is in row[index] format?

[stugorf]

Okay, found a solution...this seems to create unique nodes, one for each tuple of (name, plate_id), it is using CREATE, I need to research if there is a MERGE option to prevent duplicates:

// Plate Nodes
with [x in keys(row) where x contains "-Plate" | x ] as panels,row
UNWIND panels as panel
    CALL apoc.create.node(['Plate'],{name:panel, plate_id:row[panel]}) YIELD node
    return node

[stugorf]

I resolved duplicates generated by the APOC call to apoc.create.node, even though my solution is a bit hacky, please let me know if anyone has a more elegant solution. I am using the console's allow multiple calls feature:

// Plate Nodes
with [x in keys(row) where x contains "-Plate" | x ] as panels,row
UNWIND panels as panel
CALL apoc.create.node(['Plate'],{name:panel, plate_id:row[panel]}) YIELD node
return node;

// Merge duplicates
match(p:Plate)
with collect(p) as nodes, p.name as name, p.plate_id as plate_id
CALL apoc.refactor.mergeNodes(nodes,{name:"discard", plate_id:"discard", mergeRels:true}) yield node
return node;