Cypher Snippets

Code
Neo4j

Cypher snippets collected over the years. Some are trivial, some are probably not supported anymore, some depend on APOC or GDS.

Also, with the adoption of or transition to GQL, things will change.

The snippets are mostly for Neo4j but can be useful in other systems supporting Cypher.

Drop a database

Go to the system db and use

Drop Database thing
Create Database thing

Delete duplicate relationships

match ()-[r]->() 
match (s)-[r]->(e) 
with s,e,type(r) as typ, tail(collect(r)) as coll 
foreach(x in coll | delete x)

Delete everything

Truncating data in SQL systems is fast and only challenging when foreign keys are blocking data. With Neo4j truncating data is painful and slow. Shame really.

MATCH (n)
DETACH DELETE n

In v5+

:auto
MATCH (n) 
CALL { WITH n
DETACH DELETE n
} IN TRANSACTIONS OF 10000 ROWS;

With APOC:

CALL apoc.periodic.truncate({dropSchema: true})

Select with empty or different properties

MATCH (n:Composition) WHERE NOT (HAS (n.name)) RETURN n
Create a node
CREATE (p:Person { name:"John Field" }) RETURN p;
match (n) return n limit 100
match (p:Person{firstname:"LEna",lastname:"Pearson"}), (q:Person{firstname:"Swa"}) 
create (q)-[:Loves]->(p)
match (p:Person) return p limit 100
match (n{id:"/c/en/bird"})-->(other) return other limit 10
match (n{id:"/c/en/bird"})-[t]->(other) return distinct type(t) limit 10

Count

Match (n)  return count(n);
Match ()-[r]-() return count(r);

Unique relationship

Merge(e:Country {name: "Belgium"})
MERGE (p:Person {name: "Swa"})
ON CREATE SET p.created = "2018-12-30"
CREATE UNIQUE (p) - [:BornIn{when:"1968-09-27"}] -> (e)

Drop all constraints

CALL apoc.schema.assert({}, {})

Louvain method

CALL algo.louvain.stream("Person", null)
YIELD nodeId, community
RETURN algo.getNodeById(nodeId).pId, community
ORDER BY community

clear

MATCH (n)
OPTIONAL MATCH (n)-[r]-()
WITH n,r LIMIT 50000
DELETE n,r
RETURN count(n) as deletedNodesCount

or

MATCH (n)
DETACH DELETE n

Export to GraphML

You first need to set the following setting

apoc.export.file.enabled=true

which can be done from within the manager.

CALL apoc.export.graphml.all('graph.graphml', {useTypes:true, storeNodeIds:false})

you can find the file in the import dir of the database.

Import GraphML

CALL apoc.import.graphml('graph.graphml', {batchSize: 10000, storeNodeIds: false})

Import CSV

The csv file has to be in the import directory of the database. Going outside the app is a mess.

LOAD CSV FROM 'file:///export.csv' AS row
RETURN count(row)

The export forma can best be seen from an export of the browser (upper right button set).

Relationships between a collection of nodes

Match p=(d:Dataset{id: 'a0a9da66-84e8-4e40-b48b-e11b9f5fba9d'})-[:Contains]->(n)
With collect(n) as nodes
Unwind nodes as p
MATCH ph = (p)-[{isDataLink:true}]->(m)
WHERE m in nodes
RETURN relationships(ph)

Derived graph

Match (d:Dataset{id: '1e70cf1d-fb4f-41af-902e-6da767b13f4c'})-->(n)
With d+collect(n) as ns
Match p=(u)-[r]->(v) Where u in ns and v in ns

With nodes(p) as A, relationships(p) as B

call apoc.refactor.cloneSubgraph(A, B)
yield input, output
Set output.cloned = true
return output

This one is even better

Match (d:Dataset{id: 'faace384-ef3c-467e-a62b-2cdaf79878b3'})
CALL apoc.path.subgraphAll(d, {relationshipFilter:'Contains>'})
YIELD nodes, relationships
CALL apoc.refactor.cloneSubgraph(nodes, relationships)
YIELD input, output
Set output.derivedFrom = output.id
Set output.id = randomUUID()
With collect(output) as ns
Match (dd:Dataset)-[r:Contains]->(n) Where dd in ns and n in ns
Set r.derivedFrom = r.id
Set r.datasetId = dd.id
Set dd.name = 'Derived'
With ns
Match (u)-[r:Parent]->(v) where u in ns and v in ns
Set r.sourceId = u.id
Set r.targetId = v.id
Set u.parentId = v.id
With ns
Match (u)-[r:GenericLink]->(v) where u in ns and v in ns
Set r.sourceId = u.id
Set r.targetId = v.id
return ns

Auto increment

The non-existent removal is necessary for a lock of some sort…


MERGE (s:Sequence {name:'mysequenceName', value:0})
WITH s 
Match (t:ProcessTask) 
REMOVE t.lock  
Set t.name = "Task " + s.value
Set s.value = s.value+1

Random number

Match (t:ProcessTask) 
Set t.Dur = toInteger(rand()*11)

Rename relationship label

MATCH (n:ProcessTask)-[rel]->(m:ProcessEndNode) WITH rel
CALL apoc.refactor.setType(rel, 'ProcessLink') YIELD input, output RETURN *

Import export CSV

You need the following in the conf

dbms.security.procedures.unrestricted=apoc.*
apoc.export.file.enabled=true
apoc.import.file.enabled=true

To export

call apoc.export.csv.all('all.csv',{})

To import

CALL apoc.import.csv([{fileName: 'file:/var/lib/neo4j/import/all.csv'}], [], {});

Get full distinct paths


Match (p:ProcessTask) 
where 'OOG-S1B'  in p.solutionReferences 
    Match c=(p)-[:ProcessLink*0..50]->(leaf)
WHERE NOT (leaf)-->()
return [node in nodes(c) | properties(node)] as nodes, length(c)

Import export cypher

CALL apoc.export.cypher.all("all.cypher", {
format: "cypher-shell",
useOptimizations: {type: "UNWIND_BATCH", unwindBatchSize: 20}
})

Can run it

CALL apoc.cypher.runFile("all.cypher")

Load dump

bash "/Users/swa/Library/Application Support/com.Neo4j.Relate/Data/dbmss/dbms-1fa0c99e-185e-4870-bf7d-99f4bffb0b74/bin/neo4j-admin" load --force --from="/a/b/file.dump"

Computed properties

return properties(n{.*, L3:l3.name})

Subquery on a path

Match (solution:Sol{name:'Solution 1'})
Match (solution)-[solutionLink:SolutionLink]->(startTask:Task)
Match p=(startTask)-[:ProcessLink*0..50]->(leaf)
    WHERE Not (leaf)-->() 
With nodes(p) as ns, solution, solutionLink   
Call{
    With ns
    Unwind ns as x
    Optional Match (l3{L:'3'})-[:HierarchicalLink*1..7]->(x)
    With  properties(x{.*, L3:l3.L}) as ex
    return collect(ex) as ns2
} 
Return ns2, solution.name

//              - 3 - 4 
//  sol - 1 - 2 
//              - 5 - 6
create (sol:Sol{name:'Solution 1', id: randomUUID()})

create (t1:Task{name:'Task 1', id: randomUUID()})
create (t2:Task{name:'Task 2', id: randomUUID()})
create (t3:Task{name:'Task 3', id: randomUUID()})
create (t4:Task{name:'Task 4', id: randomUUID()})
create (t5:Task{name:'Task 5', id: randomUUID()})
create (t6:Task{name:'Task 6', id: randomUUID()})

create (t1)-[:ProcessLink]->(t2)
create (t2)-[:ProcessLink]->(t3)
create (t3)-[:ProcessLink]->(t4)
create (t2)-[:ProcessLink]->(t5)
create (t5)-[:ProcessLink]->(t6)

create (l3:Box{L:'3'})
create (l3)-[:HierarchicalLink]->(t1)
create (l3)-[:HierarchicalLink]->(t6)

create (sol)-[:SolutionLink{id: randomUUID(), solutionDuration: 23}]->(t1);


// This returns the path and fetches some new properties of the path nodes along the way
Match (solution:Sol{name:'Solution 1'})
Match (solution)-[solutionLink:SolutionLink]->(startTask:Task)
Match p=(startTask)-[:ProcessLink*0..50]->(leaf)
    WHERE Not (leaf)-->() 
With nodes(p) as ns, solution, solutionLink   
Call{
    With ns
    Unwind ns as x
    Optional Match (l3{L:'3'})-[:HierarchicalLink*1..7]->(x)
    With  properties(x{.*, L3:l3.L}) as ex
    return collect(ex) as ns2
} 
Return ns2, solution.name

Export part of database

CALL apoc.export.cypher.query(
  "MATCH (u:User)
   RETURN *",
  "users.cypher",
  { format: "cypher-shell", separateFiles: true })
YIELD file, batches, source, format, nodes, relationships, time, rows, batchSize
RETURN file, batches, source, format, nodes, relationships, time, rows, batchSize;

then copy the script to be executed elsewhere

    scp Swa@abc.com:/home/Swa/export.txt /users/swa/desktop/temp
WITH "match p=(u:Identity)<-[:HAS_IDENTITY]-(v:Device)-[:SIMILAR*1..3]-(w:Device)
where not exists((w)-[:HAS_IDENTITY]->(u))
with u ,w , reduce(t = 1, r IN relationships(p) | t * coalesce(r.strength,1)) as prob
return u.value as Identity, w.id as Device, round(max(prob),1) as Probability
union
match (u:Identity)<-[:HAS_IDENTITY]-(w:Device)
return u.value as Identity, w.id as Device, 1.0 as Probability" AS query
CALL apoc.export.csv.query(query, "predictions.csv", {})
YIELD file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data
RETURN file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data;

Show index

This works from v4.4 on and does not require priviledges:

call db.indexes()

The newer method is:

show indexes

Create index

To create on all labels:

call db.labels() yield label
with label, ['name','id'] as indexProp
with collect(label) as labels, collect(indexProp) as indexProps
with apoc.map.fromLists(labels, indexProps) as indexMap
call apoc.schema.assert(indexMap,{}) yield label, key, unique, action
return label, key, unique, action

Change label

Match (x:A) 
remove x:A
set x:B

Return a prop even if it does not exist

match (n) return n{.*,.name}

Remove all indexex

CALL apoc.schema.assert({},{})

Create fulltext index

create fulltext index full for (n:Officer) on each [n.name]

to query it:

CALL db.index.fulltext.queryNodes("full", "ass") YIELD node, score
RETURN node.name, score

Import CSV with APOC

CALL apoc.load.csv("locations.csv")
YIELD lineNo, map, list
MERGE (n:Location)
Set n=map

Epoch to datetime

Convert unix epoch or the date.getTime in JS.

datetime({epochMillis: 1000*toInteger(n.epoch)})

Change User Password

ALTER USER neo4j SET PASSWORD '123456'
:server change-password

Label Stats

CALL db.labels()
YIELD label
    CALL apoc.cypher.run("MATCH (:" + label + ") RETURN count(*) AS count", {})
    YIELD value
RETURN label, value.count AS count;

Schema Properties

call db.schema.relTypeProperties() 
yield relType, propertyName 
With Collect(propertyName) as names, relType
    return relType as type, names
union
call db.schema.nodeTypeProperties() 
yield nodeType, propertyName 
With Collect(propertyName) as names, nodeType
    return nodeType as type, names

Random combinations

MATCH (g1:ConstructionSite)
WITH COLLECT(g1) as g1
MATCH (g2:Contract)
WITH g1, COLLECT(g2) as g2
UNWIND apoc.coll.randomItems(g1, 10) as group1
WITH group1, apoc.coll.randomItem(g2) as group2
Create  (group1)-[:HAS_CONTRACT{id:randomUuid()}]->(group2)

Take some random items

Match (p:Contract) 
With collect(p) as items limit 1000
return apoc.coll.randomItems(items, 10)