Neo4j Cypher query
Neo4j Cypher Query
filter element
Delete specific element from array
MATCH (n)
WHERE HAS(n.some_array)
SET n.array = FILTER(x IN n.some_array WHERE x <> "oranges");
split unrelated queries
MATCH (a {cond:'1'}), (b {cond:'x'}) CREATE a-[:rel]->b
WITH 1 as dummy
MATCH (a {cond:'2'}), (b {cond:'y'}) CREATE a-[:rel]->b
WITH 1 as dummy
MATCH (a {cond:'3'}), (b {cond:'z'}) CREATE a-[:rel]->b
aggregation
remember that anything which isn’t an aggregate function(count/collect/distinct/sum..)
is automaticlly used as part of the grouping key which means we could include more than
one field in our grouping key.
MATCH (n { name: 'A' })-[r]->()
RETURN type(r), count(*)//group key as type(r)
START player = node:players('name:*')
MATCH player-[:sent_off_in]-game-[:in_month]-month
RETURN COUNT(player.name) AS numberOfReds, month.name//group key as month.name
ORDER BY numberOfReds DESC
START player = node:players('name:*')
MATCH player-[:sent_off_in]-game-[:in_month]-month,
game-[:in_match]-stats-[:stats]-player,
stats-[:played_for]-team
RETURN month.name, team.name, COUNT(player.name) AS numberOfReds//group key as month.name and team.name
ORDER BY numberOfReds DESC
MATCH (u:User {name: 'my user'})
RETURN u, size((u)-[:POSTED]>())//count posts group by user
start n=node:node_auto_index(name='comp') match n<-[:Members_In]-x with n.name as companyName, collect(x) as employees//group by company name return length(filter(x in employees : x.Sex='Male')) as NumOfMale, length(filter(x in employees : x.Sex='Female')) as NumOfFemale, length(employees) as Total
collecting elments
MATCH
(brand:Brand)-[:CREATED_A]->(campaign:Campaign)<-->(node)
WITH
brand,
{
campaign : campaign,
nodes : COLLECT(node)
} AS campaigns
WITH
{
brand : brand,
campaigns : COLLECT(campaigns)
} AS brands
RETURN brands
match (n:Label)
//group by n.prop implicitly
with n.prop as prop, collect(n) as nodelist, count(*) as count
where count > 1
return prop, nodelist, count;
MATCH (p:Person)-[:EVENT]->(e)
RETURN p, COLLECT({eventName: e.name, eventTimestamp: e.timestamp});
match (c:Company {id: 'MY.co'})<-[:type_of]-(s:Set)<-[:job_for]-(j:Job)
with s, j.Status as Status,count(*) as StatusCount
return s.Description, collect({Status:Status,StatusCount:StatusCount]);
MATCH (movie:Movie {title:'A League of Their Own'})
OPTIONAL MATCH (movie)<-[r]-(person:Person)
return movie.title as title,collect({name:person.name, type:(head(split(lower(type(r)), '_'))), roles:r.roles}) as cast
START team = node:teams('name:"Manchester United"')
MATCH team-[h:home_team|away_team]-game-[:on_day]-day
WITH day.name as d, game, team, h
MATCH team-[:home_team|away_team]-game-[:home_team|away_team]-opp
WITH d, COLLECT([type(h),opp.name]) AS games
RETURN d,
EXTRACT(c in FILTER(x in games: HEAD(x) = "home_team") : HEAD(TAIL(c))) AS home,
EXTRACT(c in FILTER(x in games: HEAD(x) = "away_team") : HEAD(TAIL(c))) AS away
null handling
case&&null check
load csv with headers from "" as line
with line, case line.foo when '' then null else line.foo end as foo
create (:User {name:line.name, foo:foo})
MATCH (n:Asset)
WHERE n.manageIP IS NOT NULL
WITH n.manageIP as ips,n as asset
REMOVE asset.manageIP
return asset,ips
foreach check
load csv with headers from "file:/tmp/foo.csv" as row
MERGE (p:Person {a: row.a})
FOREACH(ignoreMe IN CASE WHEN trim(row.b) <> "" THEN [1] ELSE [] END | SET p.b = row.b)
FOREACH(ignoreMe IN CASE WHEN trim(row.c) <> "" THEN [1] ELSE [] END | SET p.c = row.c)
RETURN p
FOREACH ( i in (CASE WHEN {asset_location} IS NOT NULL and {asset_location}.status = 'mounted' THEN [1] ELSE [] END) |
MERGE (cabinet:Cabinet {uuid:{asset_location}.cabinet})
MERGE (n)-[:LOCATED{status:"mounted",u:{asset_location}.u,date_mounted:{asset_location}.date_mounted}]->(cabinet)
)
complex json structure handling
Overall Response Structure
{ "items": [{
"question_id": 24620768,
"link": "http://stackoverflow.com/questions/24620768/neo4j-cypher-query-get-last-n-elements",
"title": "Neo4j cypher query: get last N elements",
"answer_count": 1,
"score": 1,
.....
"creation_date": 1404771217,
"body_markdown": "I have a graph....How can I do that?",
"tags": ["neo4j", "cypher"],
"owner": {
"reputation": 815,
"user_id": 1212067,
....
"link": "http://stackoverflow.com/users/1212067/"
},
"answers": [{
"owner": {
"reputation": 488,
"user_id": 737080,
"display_name": "Chris Leishman",
....
},
"answer_id": 24620959,
"share_link": "http://stackoverflow.com/a/24620959",
....
"body_markdown": "The simplest would be to use an ... some discussion on this here:...",
"title": "Neo4j cypher query: get last N elements"
}]
}
WITH {json} as data
UNWIND data.items as q
MERGE (question:Question {id:q.question_id}) ON CREATE
SET question.title = q.title, question.share_link = q.share_link, question.favorite_count = q.favorite_count
MERGE (owner:User {id:q.owner.user_id}) ON CREATE SET owner.display_name = q.owner.display_name
MERGE (owner)-[:ASKED]->(question)
FOREACH (tagName IN q.tags | MERGE (tag:Tag {name:tagName}) MERGE (question)-[:TAGGED]->(tag))
FOREACH (a IN q.answers |
MERGE (question)<-[:ANSWERS]-(answer:Answer {id:a.answer_id})
MERGE (answerer:User {id:a.owner.user_id}) ON CREATE SET answerer.display_name = a.owner.display_name
MERGE (answer)<-[:PROVIDED]-(answerer)
)
unwind vs foreach
MATCH (u:User {id:"2"})
unwind [{id:"21",name:"b",year:"2010"},
{id:"41",name:"d",year:"2011"},
{id:"51",name:"e",year:"2013"}] as user
merge (y:User {id: user.id, name: user.name,year:user.year})
MERGE (u)-[:FRIEND]->(y)
WITH [{name: "Event 1", timetree: {day: 1, month: 1, year: 2014}},
{name: "Event 2", timetree: {day: 2, month: 1, year: 2014}}] AS events
UNWIND events AS event
CREATE (e:Event {name: event.name})
WITH e, event.timetree AS timetree
MATCH (year:Year {year: timetree.year }),
(year)-[:HAS_MONTH]->(month {month: timetree.month }),
(month)-[:HAS_DAY]->(day {day: timetree.day })
CREATE (e)-[:HAPPENED_ON]->(day)
match (a {name:"Daniel"}), (b {name:"Jerry"})
with a,b
match s = shortestPath(a-[]-b)
unwind nodes(s) as n
with collect(n) as m
return head(m)