Where statement not workign with UNWIND

Neo4j Graph Platform Cypher
1

I've got a rather long cypher query,

MATCH (l:Lease {id: $leaseID})
MATCH (o:Operator {id: $operatorID})
WITH l as lease, o as operator, $LeaseLandInfo as interest 
MERGE (lease)-[r:ASSIGNEE {id: randomUUID(), acres: interest.amount, volPage: interest.volPage, book: interest.book, effectiveDate: interest.effectiveDate, dateFiled: interest.dateFiled, docType: interest.docType, grantor: interest.grantor, grantee: interest.grantee, total: interest.total, comments: interest.comments, wellsDrilled: interest.wellsDrilled, assignment: interest.assignment, workingInterest: interest.workingInterest, ORRI: interest.ORRI}]->(operator) 
WITH operator, lease, interest
UNWIND $landIDs as landID
WHERE landID.acres is not null 
MATCH (t:Tract {id: landID.TractID}) 
MERGE (operator)-[:ASSIGNMENT_INTEREST {id: randomUUID(), workintInterest: interest.workingInterest, ORRI: interest.ORRI, acres: landID.acres}]->(t) 
RETURN lease

I'm trying to execute the last part of the query only if acres is not null. The error I'm getting is:

 "message": "Failed to invoke procedure `apoc.cypher.doIt`: Caused by: org.opencypher.v9_0.util.SyntaxException: Invalid input 'h': expected 'i/I' (line 1, column 837 (offset: 836))",

Any push in the right direction would be appreciated.

2

The objects being referenced as landIDs are input objects;

input AssignmentInputs {
    TractID: ID
    acres: String
}

in this instance both the TractID and the acres are null and I'm trying to avoid creating the relationship.

3

To cross over from our discussion on the users-slack, it looks like the error is originating from this:

...
UNWIND $landIDs as landID 
where landID.acres is not null
...

A WHERE clause can only follow a MATCH, OPTIONAL MATCH, CALL ... YIELD ..., or a WITH clause, it can't follow an UNWIND.

You can try changing it to:

...
UNWIND $landIDs as landID
WITH landID // include any other variables you want to keep in scope
WHERE landID.acres is not null
...

That should get things straightened up, just make sure to include any other variables you want to keep in scope otherwise they won't persist beyond that WITH clause.

Given, this is a badly worded exception focusing on unxpected characters rather than unexpected tokens, so that's some feedback I can pass along to the Cypher team.

1 Like
4

I made the suggested changes:

UNWIND $landIDs as landID
WITH landID, operator, lease, interest
WHERE landID.acres IS NOT NULL 
MATCH

Response I'm getting is:

"Failed to invoke procedure `apoc.cypher.doIt`: 
Caused by: org.opencypher.v9_0.util.InvalidSemanticsException: 
Cannot merge relationship using null property value for acres"

Actual Values for input types are:

landIDs: [{TractID: null, acres: null},
      {TractID:null, acres: null}]

I attempted to the with clause after the first match as well:

MATCH (l:Lease {id: $leaseID})
MATCH (o:Operator {id: $operatorID})
WITH l as lease, o as operator, $LeaseLandInfo as interest
MERGE (lease)-[r:ASSIGNEE {id: randomUUID(), acres: interest.amount, volPage: interest.volPage, book: interest.book, effectiveDate: interest.effectiveDate, dateFiled: interest.dateFiled, docType: interest.docType, grantor: interest.grantor, grantee: interest.grantee, total: interest.total, comments: interest.comments, wellsDrilled: interest.wellsDrilled, assignment: interest.assignment, workingInterest: interest.workingInterest, ORRI: interest.ORRI}]->(operator)
WITH operator, lease, interest 
UNWIND $landIDs as landID 
WITH landID, operator, lease, interest
where landID.acres is not null 
MATCH (t:Tract {id: landID.TractID}) 
MERGE (operator)-[:ASSIGNMENT_INTEREST {id: randomUUID(), workintInterest: interest.workingInterest, ORRI: interest.ORRI, acres: landID.acres}]->(t)
RETURN lease

edited to bring whole query over and format it

5

Could you use multiple lines when you include your Cypher, one per clause? It's easier to read that way.

I think the error is resulting from your MERGE of lease to operator, you have acres: interest.amount present here on the relationship.

To avoid confusion if you can use the entire query each time that would be helpful.

1 Like
6

That was the mistake. Thank you.

7

Thanks. Looks like that last error is probably around this:

acres: interest.amount

so you may need to filter on your $LeaseLandInfo parameter, if you want to exclude merging this when interest.amount is null.

Otherwise, if it's okay that this value can be null, then you should ensure you only MERGE properties that should be unique per relationship, and use ON CREATE SET to set all the other properties (which could potentially be null).

1 Like