Data Modification
Pattern Matching GSQL supports Insert, Update, and Delete operations. The syntax is identical to that in classic GSQL (v1), though the full range of data modification operations are not yet supported.
In general, data modification can be at two levels in GSQL:
-
Top level. The statement does not need to be embedded within any other statement.
-
Within a SELECT query statement. The FROM-WHERE clauses define a match table, and the data modification is performed based on the vertex and edge information in the match table. The GSQL specifications calls these within-SELECT statements DML-sub statements.
|
INSERT
Pattern matching Insert is supported at both the top-level and within-SELECT levels, using the same syntax as in classic GSQL. You can insert vertices and edges.
-
For a top-level statement, use INSERT INTO,
-
Inside an ACCUM or POST-ACCUM clause, use the DML-sub INSERT statement.
Example 1. Create a Person vertex, whose name is Tiger Woods. Next, find Viktor’s favorite 2012 posts' authors, whose last name is prefixed with S. Finally, insert Knows edges connecting Tiger Woods with Viktor’s favorite authors.
USE GRAPH ldbc_snb
// find Viktor's 2012 favorite posts' authors, whose last_name begins with S.
INTERPRET QUERY() SYNTAX V2 {
R = SELECT t
FROM Person:s -(Likes>)- :msg -(Has_Creator>)- Person:t
WHERE s.first_name == "Viktor" AND s.last_name == "Akhiezer"
AND t.last_name LIKE "S%" AND year(msg.creation_date) == 2012;
PRINT R[R.id, R.first_name, R.last_name];
}
// results
{
"error": false,
"message": "",
"version": {
"schema": 0,
"edition": "enterprise",
"api": "v2"
},
"results": [{"R": [
{
"v_id": "8796093025410",
"attributes": {
"R.first_name": "Priyanka",
"R.id": 8796093025410,
"R.last_name": "Singh"
},
"v_type": "Person"
},
{
"v_id": "2199023260091",
"attributes": {
"R.first_name": "Janne",
"R.id": 2199023260091,
"R.last_name": "Seppala"
},
"v_type": "Person"
},
{
"v_id": "15393162796846",
"attributes": {
"R.first_name": "Mario",
"R.id": 15393162796846,
"R.last_name": "Santos"
},
"v_type": "Person"
}
]}]
}
// create a Person node, whose name is tiger,
// and connect this Person with above Victor's favorite authors
CREATE QUERY insert_edge_and_vertex () SYNTAX v2{
// add a celebrity person node using INSERT INTO statement.
INSERT INTO Person VALUES (100000000,"Tiger", "Woods", "m", _, _,_,_,_,_);
R = SELECT t
FROM Person:s -(Likes>)- :msg -(Has_Creator>)- Person:t
WHERE s.first_name == "Viktor" AND s.last_name == "Akhiezer"
AND t.last_name LIKE "S%" AND year(msg.creation_date) == 2012
PER (s, t)
ACCUM
// add edges connecting "tiger" and t with a 6/1/2020 time stamp
INSERT INTO Knows VALUES(100000000, t, to_datetime("2020-06-01"));
PRINT R [R.id, R.first_name, R.last_name];
}
You can verify the result by running a simple built-in REST endpoint.
Check the inserted vertex.
// check the inserted vertex
curl -X GET "http://localhost:9000/graph/ldbc_snb/vertices/Person/100000000" | jq .
// result
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 1
},
"error": false,
"message": "",
"results": [
{
"v_id": "100000000",
"v_type": "Person",
"attributes": {
"id": 100000000,
"first_name": "Tiger",
"last_name": "Woods",
"gender": "m",
"birthday": "1970-01-01 00:00:00",
"creation_date": "1970-01-01 00:00:00",
"locationIP": "",
"browserUsed": "",
"speaks": [],
"email": []
}
}
]
}
Check the inserted edges.
// check the inserted edges using tiger's id (100,000,000)
curl -X GET "http://localhost:9000/graph/ldbc_snb/edges/Person/100000000/Knows" | jq .
// result
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 0
},
"error": false,
"message": "",
"results": [
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "8796093025410",
"to_type": "Person",
"attributes": {
"creation_date": "2020-06-01 00:00:00"
}
},
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "2199023260091",
"to_type": "Person",
"attributes": {
"creation_date": "2020-06-01 00:00:00"
}
},
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "15393162796846",
"to_type": "Person",
"attributes": {
"creation_date": "2020-06-01 00:00:00"
}
}
]
}
// note you can use the vertex lookup API to verify the three connected authors. E.g
curl -X GET "http://localhost:9000/graph/ldbc_snb/vertices/Person/8796093025410" | jq .
UPDATE
|
Example 2. For all Knows edges that connect Viktor Akhiezer and his friends whose last_name begins with "S", update the edge creation_date to "2020-10-01". Also, for the Person vertex (Tiger Woods) update the vertex’s creation_date and language he speaks.
CREATE QUERY update_knows_ts () SYNTAX v2 {
// update the vertex tiger's attributes
// creation_date and languages spoken in POST-ACCUM
R = SELECT p
FROM Person:p
WHERE p.first_name == "Tiger" AND p.last_name == "Woods"
POST-ACCUM
// update simple base type attribute
p.creation_date = to_datetime("2020-6-1"),
// update collection-type attribute
p.speaks = ("english", "golf");
// DML-sub level, update Knows edge attribute "creation_date" in ACCUM
R = SELECT t
FROM Person:s-(Knows:e) -:t
WHERE s.first_name == "Tiger" and s.last_name == "Woods"
//update the Knows edge time stamp
ACCUM e.creation_date = to_datetime("2020-10-01");
}
To verify the update, we can use REST calls.
Check Tiger Woods' creation_date and language he speaks.
curl -X GET "http://localhost:9000/graph/ldbc_snb/vertices/Person/100000000" | jq .
// result
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 0
},
"error": false,
"message": "",
"results": [
{
"v_id": "100000000",
"v_type": "Person",
"attributes": {
"id": 100000000,
"first_name": "Tiger",
"last_name": "Woods",
"gender": "m",
"birthday": "1970-01-01 00:00:00",
"creation_date": "2020-06-01 00:00:00",
"location_ip": "",
"browser_used": "",
"speaks": [
"english",
"golf"
],
"email": []
}
}
]
}
Check Knows edges whose source is tiger woods.
curl -X GET "http://localhost:9000/graph/ldbc_snb/edges/Person/100000000/Knows" | jq .
// result
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 0
},
"error": false,
"message": "",
"results": [
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "8796093025410",
"to_type": "Person",
"attributes": {
"creation_date": "2020-10-01 00:00:00"
}
},
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "2199023260091",
"to_type": "Person",
"attributes": {
"creation_date": "2020-10-01 00:00:00"
}
},
{
"e_type": "Knows",
"directed": false,
"from_id": "100000000",
"from_type": "Person",
"to_id": "15393162796846",
"to_type": "Person",
"attributes": {
"creation_date": "2020-10-01 00:00:00"
}
}
]
}
DELETE
You can use delete () function to delete edges and vertices in ACCUM and POST-ACCUM clauses.
|
Example 3. Delete vertex Tiger Woods and its Knows edges.
CREATE QUERY delete_edge_and_vertex () SYNTAX v2 {
R = SELECT t
FROM Person:s -(Knows:e)- Person:t
WHERE s.first_name == "Tiger" AND s.last_name == "Woods"
ACCUM
//delete edges
DELETE(e)
POST-ACCUM DELETE(s); //delete src vertex
PRINT R [R.id, R.first_name, R.last_name];
}
To verify the result, you can use built-in REST calls.
curl -X GET "http://localhost:9000/graph/ldbc_snb/vertices/Person/100000000" | jq .
#vertexresults
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 0
},
"error": true,
"message": "The input vertex id '100000000' is not a valid vertex id for vertex type = Person.",
"code": "601"
}
curl -X GET "http://localhost:9000/graph/ldbc_snb/edges/Person/100000000/Knows" | jq .
#edge results
{
"version": {
"edition": "enterprise",
"api": "v2",
"schema": 0
},
"error": true,
"message": "The input source_vertex_id '100000000' is not a valid vertex id for vertex type = Person.",
"code": "601"
}