Neo4j CQL Commands - POSOCO/grid_graph GitHub Wiki
Execute CQL commands in neo4j desktop
- Change the configuration of neo4j db to allow cql execution. To do this go the settings section of the manage db window and uncomment the line
dbms.shell.enabled=true
- Keep the files to import in import folder of the 'C:\Users\username\AppData\Roaming\Neo4j Desktop\Application\neo4jDatabases\database-xyz\installation-3.3.1\import'
- Open command window in bin folder 'C:\Users\username\AppData\Roaming\Neo4j Desktop\Application\neo4jDatabases\database-xyz\installation-3.3.1\bin' and execute the command
.\neo4j-shell.bat -file ..\import\load_765_kv_lines.cql
- Dump database using
./neo4j-admin.bat dump --database=graph.db --to=/neo4jbackups/graph.db/2018-01-12.dump
command which dumps the database named graph.db to a file location namedC/neo4jbackups/graph.db/2018-01-12.dump
. The command should be run in the bin folder ('C:\Users\username\AppData\Roaming\Neo4j Desktop\Application\neo4jDatabases\database-xyz\installation-3.3.1\bin'). Refer https://neo4j.com/docs/operations-manual/current/tools/dump-load/
Create and return a node/relationship
Using CREATE Command
CREATE (Node:Label{properties. . . . }) RETURN Node
CREATE (Dhawan:person:player{name: "Shikar Dhawan", YOB: 1985, POB: "Delhi"}) RETURN Dhawan
Using MERGE Command to create/find a node
MERGE (node:label {properties . . . . . . . . . . .})
ON CREATE SET property.isCreated ="true"
ON MATCH SET property.isFound ="true"
MERGE (Jadeja:player {name: "Ravindra Jadeja", YOB: 1988, POB: "NavagamGhed"})
ON CREATE SET Jadeja.isCreated = "true"
ON MATCH SET Jadeja.isFound = "true"
RETURN Jadeja
Using MERGE Command to create/find a relationship
MATCH (a:Country), (b:Tournament)
WHERE a.name = "India" AND b.name = "ICC Champions Trophy 2013"
MERGE (a)-[r:WINNERS_OF]->(b)
RETURN a, b
Querying nodes/relationships using the MATCH clause
Match by a label using MATCH clause
MATCH (node:label)
RETURN node
MATCH (n:player)
RETURN n
Match by relationship using MATCH clause
MATCH (node:label)<-[: Relationship]-(n)
RETURN n
MATCH (Ind:Country {name: "India", result: "Winners"})<-[: TOP_SCORER_OF]-(n)
RETURN n.name
Optional Match clause
https://neo4j.com/docs/developer-manual/current/cypher/clauses/optional-match/
Nulls will be used for missing parts. It is similar to SQL outer join
MATCH (a:Movie { title: 'Wall Street' })
OPTIONAL MATCH (a)-->(x)
RETURN x, x.name
The above cql will return null,null since there is no node outgoing from Wall Street
Optional typed and named relationship
MATCH (a:Movie { title: 'Wall Street' })
OPTIONAL MATCH (a)-[r:ACTS_IN]->()
RETURN a.title, r
The above cql will return a,null since there is no node outgoing from Wall Street with a relationship of name ACTS_IN
Query data with conditional statements using WHERE clause
MATCH (player:Cricketer)
WHERE player.country = "India" AND player.runs >=175
RETURN player
We can also describe relationships in the WHERE clause as shown below
MATCH (n)
WHERE (n)-[: TOP_SCORER_OF]->( {name: "India", result: "Winners"})
RETURN n
Neo4j documentation on WHERE clause with good examples of predicates
http://neo4j.com/docs/developer-manual/3.3/cypher/clauses/where/
Count the data using COUNT clause
Count rows using count clause
Match(n{name: "India", result: "Winners"})--(x)
RETURN n, count(*)
Count relationship groups using count clause
Match(n{name: "India", result: "Winners"})-[r]-(x)
RETURN type (r), count(*)
Chaining commands using the WITH clause
The WITH syntax is similar to RETURN. It separates query parts explicitly, allowing you to declare which variables to carry over to the next part
MATCH (user)-[:FRIEND]-(friend)
WITH user, count(friend) AS friends
ORDER BY friends DESC
SKIP 1
LIMIT 3
RETURN user
Set/Remove multiple properties/labels using SET clause
Setting multiple properties using SET clause
MATCH (node:label {properties})
SET node.property1 = value, node.property2 = value
RETURN node
MATCH (Jadeja:player {name: "Ravindra Jadeja", YOB: 1988})
SET Jadeja.POB: "NavagamGhed", Jadeja.HS = "90"
RETURN Jadeja
Setting multiple labels using SET clause
MATCH (n {properties . . . . . . . })
SET n :label1:label2
RETURN n
MATCH (Ishant {name: "Ishant Sharma", YOB: 1988, POB: "Delhi"})
SET Ishant: player:person
RETURN Ishant
Remove multiple properties using SET clause
MATCH (node:label {properties})
SET node.property = NULL
RETURN node
MATCH (Jadeja:player {name: "Ravindra Jadeja", YOB: 1988, POB: "NavagamGhed"})
SET Jadeja.POB = NULL
RETURN Jadeja
Delete Multiple nodes using DELETE Command
Delete option removes nodes and associated relationships
MATCH (node:label {properties . . . . . . . . . . })
DETACH DELETE node
MATCH (Ishant:player {name: "Ishant Sharma", YOB: 1988, POB: "Delhi"})
DETACH DELETE Ishant
Remove multiple properties/labels of a node using the REMOVE Command
Remove option removes labels/properties
Remove property of a node using REMOVE clause
MATCH (node:label{properties . . . . . . . })
REMOVE node.property
RETURN node
MATCH (Dhoni:player {name: "MahendraSingh Dhoni", YOB: 1981, POB: "Ranchi"})
REMOVE Dhoni.POB
RETURN Dhoni
Remove multiple labels of a node using REMOVE clause
MATCH (node:label1:label2 {properties . . . . . . . . })
REMOVE node:label1:label2
RETURN node
MATCH (Ishant:player:person {name: "Ishant Sharma", YOB: 1988, POB: "Delhi"})
REMOVE Ishant:player:person
RETURN Ishant
Itereate through a list using FOREACH clause
Iterating through a list of paths using FOREACH clause
MATCH p = (start node)-[*]->(end node)
WHERE start.node = "node_name" AND end.node = "node_name"
FOREACH (n IN nodes(p)| SET n.marked = TRUE)
MATCH p = (Dhawan)-[*]->(CT2013)
WHERE Dhawan.name = "Shikar Dhawan" AND CT2013.name = "Champions Trophy 2013"
FOREACH (n IN nodes(p)| SET n.marked = TRUE)
Convert lists into rows using the UNWIND clause
UNWIND [1, 2, 3] AS x
RETURN x
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows
A good example of UNWIND clause
Parameters
{
"events" : [ {
"year" : 2014,
"id" : 1
}, {
"year" : 2014,
"id" : 2
} ]
}
UNWIND $events AS event
MERGE (y:Year { year: event.year })
MERGE (y)<-[:IN]-(e:Event { id: event.id })
RETURN e.id AS x
ORDER BY x
Result
+---+
| x |
+---+
| 1 |
| 2 |
+---+
2 rows
Nodes created: 3
Relationships created: 2
Properties set: 3
Labels added: 3
Iterate through nodes with merge intersection using WITH command
MATCH (owner1:Owner)<--(ss1:Substation)<--(bus1:Bus)<--(l:Line)-->(bus2:Bus)-->(ss2:Substation)-->(owner2:Owner), (l_owner:Owner)<--(l)
WHERE ss1.name<ss2.name
with l, ss1.name as ss1_name,ss2.name as ss2_name, collect(distinct owner1.name) as owner1_names, collect(distinct owner2.name) as owner2_names, collect(distinct l_owner.name) as l_owner_names
return ss1_name,owner1_names,ss2_name,owner2_names,l.id,l_owner_names order by ss1_name, ss2_name
Using UNWIND and WITH command to iterate through array variable inside a query
WITH SPLIT(line.`Line Owner`, '/') AS line_owners_names, lineEl
UNWIND range(0,size(line_owners_names)-1) AS i
WITH line_owners_names[i] AS line_owner_name, lineEl
MERGE (line_owner:Owner { name: line_owner_name })
MERGE (lineEl)-[:OWNED_BY]->(line_owner)
Getting db information
CALL db.constraints
to get the database constraints informationCALL db.schema()
to get the database schema information