SDN6.3.0 Used Unwind & Merge when the parameter is not empty but run failed

[OnceNever]

Hi,I just started to contact neo4j with springboot and import spring-boot-starter-data-neo4j:2.7.2
But I don't think I'm going well and got an error :
org.neo4j.driver.exceptions.ClientException: Cannot merge the following node because of null property value for 'name': (:Column {name: null})
I know Merge cant't work with null properties，This should not be the problem
Okay now, I have a column node:

@Data
@Node("Column")
public class ColumnNode {

@Id
@GeneratedValue
private Long id;

@Property
private String sourceName;

@Property
private String schemaName;

@Property
private String tableName;

@Property
private String name;

@Relationship(type = "BLOOD", direction = Relationship.Direction.INCOMING)
private ColumnRelation columnRelation;
}

and a table node:

@Data
@Node("Table")
public class TableNode {

@Id
@GeneratedValue
private Long id;

@Property
private String sourceName;

@Property
private String schemaName;

@Property
private String name;

@Property
private String tableComment;

@Relationship(type = "RELEVANCE", direction = Relationship.Direction.INCOMING)
private TableRelation tableRelation;

@Relationship(type = "BELONG", direction = Relationship.Direction.INCOMING)
private List<TableAndColumnRelation> tableAndColumnRelation;
}

with TableAndColumnRelation:

@Data
@RelationshipProperties
public class TableAndColumnRelation {

    @RelationshipId
    private Long id;

    @TargetNode
    private ColumnNode columnNode;
}

Then, I want to establish a relationship between multiple column nodes and a table node with the statement:

@Query(value =
        "UNWIND :#{#froms} AS col " +
        "MERGE (c:Column { sourceName : col.sourceName, " +
                "schemaName : col.schemaName, " +
                "tableName : col.tableName, " +
                "name : col.name }) " +
        "MERGE (t:Table {sourceName : :#{#to.sourceName}, " +
                "schemaName : :#{#to.schemaName}, " +
                "name : :#{#to.name} }) " +
                "MERGE (c) -[r:BELONG]-> (t) ")
void mergeTableAndColumnRelations(@Param("froms") List<ColumnNode> froms, @Param("to") TableNode to);

Here are the attributes of 'froms' and 'to'：

:param froms => [{__labels__: ["Column"], __id__: null, __properties__: {name: "h", sourceName: "B", schemaName: "B", tableName: "B"}}]
:param to => {__labels__: ["Table"], __id__: null, __properties__: {name: "B", tableComment: null, sourceName: "B", schemaName: "B"}}

We can see the attributes of 'froms' not null and it can inject to :#{#froms}, the properties of variable col is {name: "h", sourceName: "B", schemaName: "B", tableName: "B"}, but Merge run failed it seems that col.name is null:
org.neo4j.driver.exceptions.ClientException: Cannot merge the following node because of null property value for 'name': (:Column {name: null})
However, it works well when I do not use unwind and use a field to associate with a table like this :

@Query(value =
            "MERGE (c:Column {sourceName : :#{#from.sourceName}, " +
                    "schemaName : :#{#from.schemaName}, " +
                    "tableName : :#{#from.tableName}, " +
                    "name : :#{#from.name} }) " +
            "MERGE (t:Table {sourceName : :#{#to.sourceName}, " +
                    "schemaName : :#{#to.schemaName}, " +
                    "name : :#{#to.name} }) " +
            "MERGE (c) -[r:BELONG]-> (t) ")
    void mergeTableAndColumnRelation(@Param("from") ColumnNode from, @Param("to") TableNode to);

This is the log stack I printed out

UNWIND $__SpEL__0 AS col MERGE (c:Column { sourceName : col.sourceName, schemaName : 
col.schemaName, tableName : col.tableName, name : col.name }) MERGE (t:Table {sourceName : 
$__SpEL__1, schemaName : $__SpEL__2, name : $__SpEL__3 }) MERGE (c) -[r:BELONG]-> (t) 
2022-08-04 19:23:20.041 TRACE 4076 --- [nio-8005-exec-1] org.springframework.data.neo4j.cypher    
: with parameters:
:param 0 => [{__labels__: ["Column"], __id__: null, __properties__: {name: "h", sourceName: 
"B", schemaName: "B", tableName: "B"}}]
:param 1 => {__labels__: ["Table"], __id__: null, __properties__: {name: "B", tableComment: 
null, sourceName: "B", schemaName: "B"}}
:param __SpEL__1 => "B"
:param __SpEL__0 => [{__labels__: ["Column"], __id__: null, __properties__: {name: "h", 
sourceName: "B", schemaName: "B", tableName: "B"}}]
:param __SpEL__3 => "B"
:param __SpEL__2 => "B"
:param froms => [{__labels__: ["Column"], __id__: null, __properties__: {name: "h", 
sourceName: "B", schemaName: "B", tableName: "B"}}]
:param to => {__labels__: ["Table"], __id__: null, __properties__: {name: "B", tableComment: 
null, sourceName: "B", schemaName: "B"}}

Looking forward to receiving your reply!

[OnceNever]

@michael-simons I'm very happy to see the issues #2576, and then I try to modify my code :

@Override
    public String addColumnsToTable(List<ColumnNode> columns, TableNode table) {
        List<Value> colList = Lists.newArrayListWithCapacity(columns.size());
        columns.forEach(col -> {
            Map<String, String> params = new HashMap<>();
            params.put("sourceName", col.getSourceName());
            params.put("schemaName", col.getSchemaName());
            params.put("tableName", col.getTableName());
            params.put("name", col.getName());
            colList.add(Values.value(params));
        });
        return tableAndColumnRepository.mergeTableAndColumnRelations(colList, table);
    }

@Query(value =
            "UNWIND :#{#froms} AS col " +
            "MERGE (c:Column { sourceName : col.sourceName, " +
                    "schemaName : col.schemaName, " +
                    "tableName : col.tableName, " +
                    "name : col.name }) " +
            "MERGE (t:Table {sourceName : :#{#to.sourceName}, " +
                    "schemaName : :#{#to.schemaName}, " +
                    "name : :#{#to.name} }) " +
            "MERGE (c) -[r:BELONG]-> (t) " +
            "RETURN distinct t.name")
    String mergeTableAndColumnRelations(@Param("froms") List<Value> froms, @Param("to") TableNode to);

Now,It works very well,thank you!

[michael-simons]

Hi @walkonbench now you beat me to it in a response.

Here's another solution I wrote up for a test case:

public interface TableRepository extends Neo4jRepository<TableNode, Long> {

	@Query(value =
			"UNWIND :#{#froms} AS col " +
			"WITH col.__properties__ AS col, :#{#to}.__properties__ AS to " +
			"MERGE (c:Column { sourceName : col.sourceName, " +
			"schemaName : col.schemaName, " +
			"tableName : col.tableName, " +
			"name : col.name }) " +
			"MERGE (t:Table {sourceName : to.sourceName, " +
			"schemaName : to.schemaName, " +
			"name : to.name }) " +
			"MERGE (c) -[r:BELONG]-> (t) ")
	void mergeTableAndColumnRelations(@Param("froms") List<ColumnNode> froms, @Param("to") TableNode to);
}

Let's dig to it, what are we seeing?
I am getting the __properties__ fiel from both the column and the table objects.
Why do I need to do so? It is because you passed your domain object to the method (which is completely fine todo).
What happens than is described here: https://docs.spring.io/spring-data/neo4j/docs/current/reference/html/#custom-queries.parameters

We tried to design a map-based generic model to pass the value of objects towards Cypher.

In the modified query I do unwrap the properties once and use WITH. It's easier to read on the following steps, also avoids the whole bunch of Spring Expression Interpolation (SpEL).

Personally, I would prefer my solution as it keeps the domain model intact and does not convert things to a map.

[OnceNever]

@michael-simons Thank you very much. When I first saw this method to complete this thing, it was undoubtedly a better solution. I like it very much. Thank you again, ha ha

[mrksph]

Hi, @michael-simons I'm considering switching to this way of bulk inserting and it would be interesting to know its performance compared to a repository.save() inside a loop. Thanks