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 support.

In general, data modification can be at two levels in GSQL:

Top level. The statement does not need to 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, Update, and Delete currently work in compiled mode only (e.g., you must run INSTALL QUERY before RUN QUERY.) Data Modification in interpreted mode is not yet available.
SELECT queries with data modification may only have one POST-ACCUM clause.

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 lastName prefix with S.
INTERPRET QUERY() SYNTAX V2 {

  R  =  SELECT t
        FROM Person:s -(LIKES>)- :msg -(HAS_CREATOR>)- Person:t
        WHERE s.firstName == "Viktor" AND s.lastName == "Akhiezer"
         AND t.lastName LIKE "S%" AND year(msg.creationDate) == 2012;

  PRINT R[R.id, R.firstName, R.lastName];
}

#result
{
  "error": false,
  "message": "",
  "version": {
    "schema": 0,
    "edition": "enterprise",
    "api": "v2"
  },
  "results": [{"R": [
    {
      "v_id": "8796093025410",
      "attributes": {
        "R.id": 8796093025410,
        "R.firstName": "Priyanka",
        "R.lastName": "Singh"
      },
      "v_type": "Person"
    },
    {
      "v_id": "2199023260091",
      "attributes": {
        "R.id": 2199023260091,
        "R.firstName": "Janne",
        "R.lastName": "Seppala"
      },
      "v_type": "Person"
    },
    {
      "v_id": "15393162796846",
      "attributes": {
        "R.id": 15393162796846,
        "R.firstName": "Mario",
        "R.lastName": "Santos"
      },
      "v_type": "Person"
    }
  ]}]
}

# create a Person node, whose name is tiger,
# and connect this Person with above Victor's favorite authors
CREATE QUERY InsertEdgeAndVertex () 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.firstName == "Viktor" AND s.lastName == "Akhiezer"
         AND t.lastName LIKE "S%" AND year(msg.creationDate) == 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.firstName, R.lastName];
}

INSTALL QUERY InsertEdgeAndVertex
RUN QUERY InsertEdgeAndVertex()

You can verify the result by running a simple built-in REST endpoint.

Check the inserted vertex.

Linux Shell

#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,
        "firstName": "Tiger",
        "lastName": "Woods",
        "gender": "m",
        "birthday": "1970-01-01 00:00:00",
        "creationDate": "1970-01-01 00:00:00",
        "locationIP": "",
        "browserUsed": "",
        "speaks": [],
        "email": []
      }
    }
  ]
}

Check the inserted edges.

Linux file

#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": {
        "creationDate": "2020-06-01 00:00:00"
      }
    },
    {
      "e_type": "KNOWS",
      "directed": false,
      "from_id": "100000000",
      "from_type": "Person",
      "to_id": "2199023260091",
      "to_type": "Person",
      "attributes": {
        "creationDate": "2020-06-01 00:00:00"
      }
    },
    {
      "e_type": "KNOWS",
      "directed": false,
      "from_id": "100000000",
      "from_type": "Person",
      "to_id": "15393162796846",
      "to_type": "Person",
      "attributes": {
        "creationDate": "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

Top-level UPDATE statements are not yet supported in syntax v2.

Vertex attributes can only be updated in POST-ACCUM clause, and edge attributes can only be updated in the ACCUM clause.
To perform within-SELECT updates, the FROM pattern can only be a *single hop, fixed length* pattern.

Example 2. For all KNOWS edges that connect Viktor Akhiezer and his friends whose lastName begins with "S", update the edge creationDate to "2020-10-01". Also, for the Person vertex (Tiger Woods) update the vertex’s creationDate and language he speaks.

USE GRAPH ldbc_snb

DROP QUERY UpdateKnowsTS

CREATE QUERY UpdateKnowsTS () SYNTAX v2 {

  # update the vertex tiger's attributes
  # creationDate and languages spoken in POST-ACCUM
  R = SELECT p
      FROM Person:p
      WHERE p.firstName == "Tiger" AND p.lastName == "Woods"
      POST-ACCUM
            # update simple base type attribute
            p.creationDate = to_datetime("2020-6-1"),
            # update  collection-type attribute
            p.speaks = ("english", "golf");

  #DML-sub level, update KNOWS edge attribute "creationDate" in ACCUM
  R  =  SELECT t
        FROM Person:s-(KNOWS:e) -:t
        WHERE s.firstName == "Tiger" and s.lastName == "Woods"
        #update the KNOWS edge time stamp
        ACCUM e.creationDate = to_datetime("2020-10-01");
}

INSTALL QUERY UpdateKnowsTS
RUN QUERY UpdateKnowsTS()

To verify the update, we can use REST calls.

Check Tiger Woods' creationDate and language he speaks.

Linux Shell

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,
        "firstName": "Tiger",
        "lastName": "Woods",
        "gender": "m",
        "birthday": "1970-01-01 00:00:00",
        "creationDate": "2020-06-01 00:00:00",
        "locationIP": "",
        "browserUsed": "",
        "speaks": [
          "english",
          "golf"
        ],
        "email": []
      }
    }
  ]
}

Check KNOWS edges whose source is tiger woods.

Linux Shell

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": {
        "creationDate": "2020-10-01 00:00:00"
      }
    },
    {
      "e_type": "KNOWS",
      "directed": false,
      "from_id": "100000000",
      "from_type": "Person",
      "to_id": "2199023260091",
      "to_type": "Person",
      "attributes": {
        "creationDate": "2020-10-01 00:00:00"
      }
    },
    {
      "e_type": "KNOWS",
      "directed": false,
      "from_id": "100000000",
      "from_type": "Person",
      "to_id": "15393162796846",
      "to_type": "Person",
      "attributes": {
        "creationDate": "2020-10-01 00:00:00"
      }
    }
  ]
}

DELETE

You can use delete () function to delete edges and vertices in ACCUM and POST-ACCUM clauses.

Top-levels DELETE statements are not yet supported in SYNTAX v2.
Edges can only be deleted in the ACCUM clause.
For best performance, vertices should be deleted in the POST-ACCUM clause.
To perform within-SELECT deletes, the FROM pattern can only be a *single hop, fixed length* pattern.

Example 3. Delete vertex Tiger Woods and its KNOWS edges.

USE GRAPH ldbc_snb

DROP QUERY  DeleteEdgeAndVertex

CREATE QUERY DeleteEdgeAndVertex () SYNTAX v2{

  R  =  SELECT t
        FROM Person:s -(KNOWS:e)- Person:t
        WHERE s.firstName == "Tiger" AND s.lastName == "Woods"
        ACCUM
           //delete edges
           DELETE(e)
        POST-ACCUM DELETE(s); //delete src vertex


  PRINT  R [R.id, R.firstName, R.lastName];
}

INSTALL QUERY DeleteEdgeAndVertex
RUN QUERY DeleteEdgeAndVertex()

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"
}