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

Linux Shell

// check the inserted vertex
curl -X GET "http://localhost:14240/restpp/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.

Linux file

// check the inserted edges using tiger's id (100,000,000)
curl -X GET "http://localhost:14240/restpp/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:14240/restpp/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 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.

Linux Shell

curl -X GET "http://localhost:14240/restpp/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.

Linux Shell

curl -X GET "http://localhost:14240/restpp/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.

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.

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:14240/restpp/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:14240/restpp/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"
}