Comments (2)
Questions about using Cypher are better directed at a user support forum such as StackOverflow or the Neo4j online community: https://community.neo4j.com/
If we start from your query, the main reasons I see for it not working is that there are typos and data type issues in the query.
The first issue that prevents things from working is that there is no status
variable in scope to insert in the prevData map projection: p{.*, status} as prevData
, so let's drop that to make p{.*} as prevData
.
This leads us to the second issue, where there is a predicate based on prevData.status
: (prevData.status <> m.status)
, and in the case where p
is null prevData
will be null which will have the predicate evaluate to null, and that isn't something apoc.do.when
seems to be able to handle. So we need to ensure that when p
is null, we provide a default for prevData
that contains some default value for status
, like so: coalesce(p{.*},{status:''}) as prevData
(I don't know if the empty string is a sensible default value for prevData.status
, since I don't know your data domain, so you will probably have to tune that).
Once we have the primary query, we find that the query passed to apoc.do.when
has a comma missing in between userId
and rowStatusId
. After we fix that, the query becomes:
UNWIND [{userId:'123', firstName:'Devika',status:'Active'}, {userId:'123', firstName:'Devika',status:'Suspended'}, {userId:'123', firstName:'Devika',status:'Terminated'}] as users
OPTIONAL MATCH(p:USER{userId:users.userId})
WITH p, coalesce(p{.*},{status:''}) as prevData, users
MERGE (m:USER { userId:users.userId })
ON CREATE SET m.firstName=users.firstName,
m.status = users.status
ON MATCH SET m.firstName=users.firstName,
m.status = users.status
with prevData, m
CALL apoc.do.when((prevData.status <> m.status), "CREATE (his:UserHistory{historyCode:\"DSC\",oldValue:prevData.status,newValue:m.status, userId:m.userId, rowStatusId:1})" ,"return null", {prevData:prevData, m:m}) yield value
return value
Which creates the following 4 nodes (when run on an empty graph):
╒═══════════════╤═══════════════════════════════════════════════════════════════════════════════════════════╕
│"labels(n)" │"properties(n)" │
╞═══════════════╪═══════════════════════════════════════════════════════════════════════════════════════════╡
│["USER"] │{"firstName":"Devika","userId":"123","status":"Terminated"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Active","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Suspended","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Terminated","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"} │
└───────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘
This makes it fairly obvious that we are getting the default value for prevData.status
in every UserHistory
node, and I am guessing that is the problem you actually wanted to resolve.
This is by design, where the specification of Cypher dictates that the effects of a later clause may not influence the result of a prior clause. In this case the MERGE
that creates the USER
nodes may not influence the MATCH
that finds p
. This means that the new nodes you create will never be seen by your OPTIONAL MATCH(p:USER{userId:users.userId})
.
We can use a subquery to ensure that we run the entire set of changes for one user before processing the next user:
UNWIND [{userId:'123', firstName:'Devika',status:'Active'}, {userId:'123', firstName:'Devika',status:'Suspended'}, {userId:'123', firstName:'Devika',status:'Terminated'}] AS user
CALL { // Use a subquery to ensure that all of this runs per row before the next row
WITH user
OPTIONAL MATCH(p:USER{userId:user.userId})
WITH coalesce(p.status,'') AS prevStatus, user
MERGE (m:USER{userId:user.userId})
// Since we set the same thing on both MATCH and CREATE, we don't need for the SET to be conditional
SET m.firstName = user.firstName, m.status = user.status
RETURN prevStatus
}
CREATE (:UserHistory{historyCode:'DSC', oldValue:prevStatus, newValue:user.status, userId:user.userId, rowStatusId:1})
Which gives us this result:
╒═══════════════╤═══════════════════════════════════════════════════════════════════════════════════════════════════╕
│"labels(n)" │"properties(n)" │
╞═══════════════╪═══════════════════════════════════════════════════════════════════════════════════════════════════╡
│["USER"] │{"firstName":"Devika","userId":"123","status":"Terminated"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Active","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Suspended","historyCode":"DSC","rowStatusId":1,"oldValue":"Active","userId":"123"} │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Terminated","historyCode":"DSC","rowStatusId":1,"oldValue":"Suspended","userId":"123"}│
└───────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────┘
Please direct any follow up questions to a user support forum such as StackOverflow or the Neo4j online community: https://community.neo4j.com/
from opencypher.
@thobe
Thank you very much.
It helps a lot to understand how the cypher query works!
from opencypher.
Related Issues (20)
- Performance is very poor for the create/update relationships HOT 1
- Any plan to release Cypher 10? HOT 3
- Beta version missing dependency ?
- Problem parsing oC_MultiPartQuery with ANTLR4 HOT 3
- OpenCypher Antlr - strange parse tree (c#) HOT 3
- Improve the use of lists stored as property HOT 3
- missing children from `OC_ComparisonExpressionContext` in cpp-runtime of ANTLR HOT 2
- Incomplete EBNF Grammar HOT 9
- NULL token name is a reserved word in C++ HOT 2
- MANDATORY MATCH HOT 3
- Invalid syntax HOT 3
- Commit the .ebnf output into the repo HOT 1
- User defined function in custom namespace parsed as property lookup if function name is a keyword HOT 3
- Validating ``WHERE (value in property)``
- TCK only published with features
- RedisGraph: EOL
- Question on Semantics of WITH clause HOT 1
- Grammar HOT 2
- Short-circuit evaluation in openCypher HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from opencypher.