davisford · January 2, 2020 15:14
diff --git a/_cypher-tips-and-tricks.adoc b/_cypher-tips-and-tricks.adoc
diff --git a/apoc.date.parse.format.cypher b/apoc.date.parse.format.cypher
 call apoc.load.json("https://api.github.com/search/repositories?q=apoc") yield value
 UNWIND value.items as r
 return r.full_name, r.created_at, apoc.date.format(apoc.date.parse(r.created_at,'s',"yyyy-MM-dd'T'HH:mm:ss'Z'"),"s")
 order by r.favorites desc
diff --git a/apoc.periodic.iterate.sub-batching.cypher b/apoc.periodic.iterate.sub-batching.cypher
 CALL apoc.periodic.iterate(
 "LOAD CSV WITH HEADERS FROM url AS line 
 WITH  apoc.coll.partition(collect(line),10000) AS batchesOfLines 
 UNWIND batchesOfLines as batch 
 RETURN batch",
 "UNWIND {batch} AS user 
 MERGE (u:User {Email: user.Email}) 
 SET u += apoc.map.clean(user,['Email'],null)",
 {batchSize: 1, parallel: true})
diff --git a/delete-node-with-any-property-in-values-from-csv.adoc b/delete-node-with-any-property-in-values-from-csv.adoc
diff --git a/efficient-graph-updates-with-cypher.adoc b/efficient-graph-updates-with-cypher.adoc
diff --git a/find-null-values-load-csv.cypher b/find-null-values-load-csv.cypher
 LOAD CSV WITH HEADERS FROM "file:///data2.csv" AS row
 WITH ROW WHERE ANY (k in keys(row) WHERE row[k] IS NULL)
 RETURN row LIMIT 100;
diff --git a/graph-json-with-cypher.cypher b/graph-json-with-cypher.cypher
    OPTIONAL MATCH path = (x)<-[*..3]-() WHERE ID(x) = 65

    UNWIND nodes(path) as node
    UNWIND rels(path) as rel

    WITH collect(distinct node) as nodes,collect(distinct rel) as rels
    // todo release apoc.coll.flatten
    // WITH apoc.coll.flatten(collect(nodes(path))) as nodes, apoc.coll.flatten(collect(relationships(path))) as rels
    WITH apoc.coll.toSet([n in nodes WHERE n is not null 
                | { id: id(n),label: labels(n),type:"",metadata: properties(n)  } ]) as nodes,
         apoc.coll.toSet([r in rels WHERE r is not null 
                | { id: id(r),source: id(startNode(r)),relation: type(r),target: id(endNode(r)), directed: "true"  } ]) as rels

    RETURN { graph: { type:"",label: "",directed: "true",nodes: nodes,edges: rels,
             metadata:{ countNodes: size(nodes),countEdges: size(rels) } } } as graph;

diff --git a/index-of.cypher b/index-of.cypher
 // length of the first part of a split is equivalent to index-of
 RETURN length(split("European Union","pean")[0]); 

 START u=node:node_auto_index("fullName:*jay*") 
 MATCH (u:User) 
 WITH distinct u
 RETURN {firstName : u.firstName , lastName : u.lastName, fullName : u.fullName, profilePicture : u.profilePicture, id : u.id} as user
 // length of the first part of a split is equivalent to index-of
 ORDER BY length(split(toLower(u.fullName,"jay")[0]);
diff --git a/longest-path.cypher b/longest-path.cypher
 // all paths
 MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
 RETURN p


 // longest path with sorting
 MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
 RETURN p
 order by size(p) desc
 return p limit 1

 // longest path by checking end-node with size() aka get-degree
 MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb) 
 WHERE SIZE((shb)<-[:SHARES_HELD_BY]-())=0 
 RETURN p


 // longest path by checking end-node with exists() which is cheaper for long chains
 MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb) 
 WHERE not exists( (shb)<-[:SHARES_HELD_BY]-() )
 RETURN p
diff --git a/manage-relationship-status-updates-keep-10.adoc b/manage-relationship-status-updates-keep-10.adoc
diff --git a/map-projection-pattern-comprehension.adoc b/map-projection-pattern-comprehension.adoc
diff --git a/match-node-expressions.cypher b/match-node-expressions.cypher
 // Matching dynamic objects
 MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
 WITH collect([p, m]) as pairs
 UNWIND pairs as pair

 // WONT WORK
 MATCH (pair[1])<-[:DIRECTED]-(p:Person)
 // WORKS, alias expression with variable
 WITH pair[0] as p0, pair[1] as p1
 MATCH (p1)<-[:DIRECTED]-(p:Person)
 RETURN p.name
diff --git a/meta-graph-with-virtual-entities.cypher b/meta-graph-with-virtual-entities.cypher
 MATCH (a)-[r]->(b)
 WITH head(labels(a)) AS l, head(labels(b)) AS l2, type(r) AS rel_type, count(*) as count
 WITH collect({from:l,to:l2,type:rel_type, count:count}) as rels, apoc.coll.toSet(collect(l2)+collect(l)) as nodes
 WITH apoc.map.fromPairs([name in nodes | [name, apoc.create.vNode([name],{name:name})]]) as nodes, rels
 UNWIND rels as r
 CALL apoc.create.vRelationship(nodes[r.from],r.type,{count:r.count},nodes[r.to]) yield rel
 RETURN rel,nodes[r.from],nodes[r.to];
diff --git a/pattern-comprehension.cypher b/pattern-comprehension.cypher
 MATCH (m:Movie {title:"The Matrix"})
 RETURN
 m {.title, .released, 
   directors: [ (m)<-[:DIRECTED]-(a) | a {.name, .born } ],
   actors: [ (m)<-[:ACTED_IN]-(a) | a {.name, .born, 
       movies:[(a)-[:ACTED_IN]->(m2) | m2 { .title, .released }]
  }]} as document
diff --git a/people-that-know-me-grouped-by-reverse.cypher b/people-that-know-me-grouped-by-reverse.cypher
 // group people that know me by reverse relationship ("followback")
 MATCH (me:Person)<-[:KNOWS]-(o)
 WHERE me.name = "me"
 RETURN exists( (me)-[:KNOWS]->(o) ) as friend, collect(distinct o) as people;
diff --git a/property-lookup-performance.adoc b/property-lookup-performance.adoc
diff --git a/pure-virtual-graph-apoc.cypher b/pure-virtual-graph-apoc.cypher
 WITH ["Andres","Eve","Rik","Mark","Sophia","Praveena","Michael","Stefan","Max","Zhen"] AS names
 UNWIND names as name
 call apoc.create.vNode(["Person"],{name:name}) yield node
 WITH names, size(names) as len, apoc.map.groupBy(collect(node),"name") as nodes
 UNWIND range(1,42) as idx
 CALL apoc.create.vRelationship(nodes[names[toInt(rand()*len)]],"KNOWS",{},nodes[names[toInt(rand()*len)]]) yield rel
 RETURN nodes,rel;
diff --git a/random-sampling.cypher b/random-sampling.cypher
 // answer to http://stackoverflow.com/questions/42691687/neo4j-slow-selection-operation-with-huge-data

 MATCH (n) WITH count(*) as total
 WITH [_ IN range(1,10000) | toInt(rand()*total)] as ids
 MATCH (emp) WHERE id(emp) IN ids AND emp:Employee
 RETURN emp LIMIT 10;
diff --git a/set_map_projection.cypher b/set_map_projection.cypher
 // set map projection as propertie, kudos to Adam Cowley

 MERGE (n:User {screen_name:user.screen_name})
 SET  n += user { .name, .location}
	call apoc.load.json("https://api.github.com/search/repositories?q=apoc") yield value
	UNWIND value.items as r
	return r.full_name, r.created_at, apoc.date.format(apoc.date.parse(r.created_at,'s',"yyyy-MM-dd'T'HH:mm:ss'Z'"),"s")
	order by r.favorites desc
	CALL apoc.periodic.iterate(
	"LOAD CSV WITH HEADERS FROM url AS line
	WITH apoc.coll.partition(collect(line),10000) AS batchesOfLines
	UNWIND batchesOfLines as batch
	RETURN batch",
	"UNWIND {batch} AS user
	MERGE (u:User {Email: user.Email})
	SET u += apoc.map.clean(user,['Email'],null)",
	{batchSize: 1, parallel: true})
	LOAD CSV WITH HEADERS FROM "file:///data2.csv" AS row
	WITH ROW WHERE ANY (k in keys(row) WHERE row[k] IS NULL)
	RETURN row LIMIT 100;
	OPTIONAL MATCH path = (x)<-[*..3]-() WHERE ID(x) = 65

	UNWIND nodes(path) as node
	UNWIND rels(path) as rel

	WITH collect(distinct node) as nodes,collect(distinct rel) as rels
	// todo release apoc.coll.flatten
	// WITH apoc.coll.flatten(collect(nodes(path))) as nodes, apoc.coll.flatten(collect(relationships(path))) as rels
	WITH apoc.coll.toSet([n in nodes WHERE n is not null
	\| { id: id(n),label: labels(n),type:"",metadata: properties(n) } ]) as nodes,
	apoc.coll.toSet([r in rels WHERE r is not null
	\| { id: id(r),source: id(startNode(r)),relation: type(r),target: id(endNode(r)), directed: "true" } ]) as rels

	RETURN { graph: { type:"",label: "",directed: "true",nodes: nodes,edges: rels,
	metadata:{ countNodes: size(nodes),countEdges: size(rels) } } } as graph;
	// length of the first part of a split is equivalent to index-of
	RETURN length(split("European Union","pean")[0]);

	START u=node:node_auto_index("fullName:jay")
	MATCH (u:User)
	WITH distinct u
	RETURN {firstName : u.firstName , lastName : u.lastName, fullName : u.fullName, profilePicture : u.profilePicture, id : u.id} as user
	// length of the first part of a split is equivalent to index-of
	ORDER BY length(split(toLower(u.fullName,"jay")[0]);
	// all paths
	MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
	RETURN p


	// longest path with sorting
	MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
	RETURN p
	order by size(p) desc
	return p limit 1

	// longest path by checking end-node with size() aka get-degree
	MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
	WHERE SIZE((shb)<-[:SHARES_HELD_BY]-())=0
	RETURN p


	// longest path by checking end-node with exists() which is cheaper for long chains
	MATCH p=(c:Organisation {duns_nbr:'216236900'})-[r:SHARES_HELD_BY*1..50]->(shb)
	WHERE not exists( (shb)<-[:SHARES_HELD_BY]-() )
	RETURN p
syntax	description	example
`.property`	property lookup	`p{.name} → {name : "John"}`
`.*`	all properties	`p{.*} → {name:"John", age:42}`
`variable`	variable name as key, variable value as value	`p{count} → {count: 1}`
`key : value`	literal entry	`p{awesome:true} → {awesome:true}`
	// Matching dynamic objects
	MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
	WITH collect([p, m]) as pairs
	UNWIND pairs as pair

	// WONT WORK
	MATCH (pair[1])<-[:DIRECTED]-(p:Person)
	// WORKS, alias expression with variable
	WITH pair[0] as p0, pair[1] as p1
	MATCH (p1)<-[:DIRECTED]-(p:Person)
	RETURN p.name
	MATCH (a)-[r]->(b)
	WITH head(labels(a)) AS l, head(labels(b)) AS l2, type(r) AS rel_type, count(*) as count
	WITH collect({from:l,to:l2,type:rel_type, count:count}) as rels, apoc.coll.toSet(collect(l2)+collect(l)) as nodes
	WITH apoc.map.fromPairs([name in nodes \| [name, apoc.create.vNode([name],{name:name})]]) as nodes, rels
	UNWIND rels as r
	CALL apoc.create.vRelationship(nodes[r.from],r.type,{count:r.count},nodes[r.to]) yield rel
	RETURN rel,nodes[r.from],nodes[r.to];
	MATCH (m:Movie {title:"The Matrix"})
	RETURN
	m {.title, .released,
	directors: [ (m)<-[:DIRECTED]-(a) \| a {.name, .born } ],
	actors: [ (m)<-[:ACTED_IN]-(a) \| a {.name, .born,
	movies:[(a)-[:ACTED_IN]->(m2) \| m2 { .title, .released }]
	}]} as document